cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query to search SKUs and display product info

SOLVED
   Did you know you can see the translated content as per your choice?

Translation is in progress. Please check again after few minutes.

SQL Query to search SKUs and display product info

Hi,

 

I am converting my current website to a magento run website.

Currently I had a small web page with only a search bar, where I can search by sku and it will show me the name, short description and price.

 

my current sql query was: 

SELECT * FROM `products` WHERE (`code` LIKE '$query')

But when I tried to modify the SQL Query to work with magento DB, I could not figure out how to find the data needed.

 

Does any one know how to build the SQL query command?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: SQL Query to search SKUs and display product info

Hi in order to get products attribute filtered using SKU you can use query as follows :

 

Below query is loading sku with value Unknown and its description whose attribute id is 72 in the DB.

 

SELECT `e`.*, IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description` FROM `mg1924_catalog_product_entity` AS `e`
 INNER JOIN `mg1924_catalog_product_entity_text` AS `at_description_default` ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND (`at_description_default`.`attribute_id` = '72') AND `at_description_default`.`store_id` = 0
 LEFT JOIN `mg1924_catalog_product_entity_text` AS `at_description` ON (`at_description`.`entity_id` = `e`.`entity_id`) AND (`at_description`.`attribute_id` = '72') AND (`at_description`.`store_id` = 1) WHERE (`e`.`sku` = 'Unknown')

Please let me know if it helps, you can add as many attribute as description in above query too.

View solution in original post

9 REPLIES 9

Re: SQL Query to search SKUs and display product info

Hi @robertbits,

 

If you need to perform that query it should be something like:

 

SELECT * FROM `catalog_product_entity` WHERE ....

Into that table you can filter by the SKU column.

Re: SQL Query to search SKUs and display product info

thanks for your reply, I will give it a try. 

Re: SQL Query to search SKUs and display product info

Hi in order to get products attribute filtered using SKU you can use query as follows :

 

Below query is loading sku with value Unknown and its description whose attribute id is 72 in the DB.

 

SELECT `e`.*, IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description` FROM `mg1924_catalog_product_entity` AS `e`
 INNER JOIN `mg1924_catalog_product_entity_text` AS `at_description_default` ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND (`at_description_default`.`attribute_id` = '72') AND `at_description_default`.`store_id` = 0
 LEFT JOIN `mg1924_catalog_product_entity_text` AS `at_description` ON (`at_description`.`entity_id` = `e`.`entity_id`) AND (`at_description`.`attribute_id` = '72') AND (`at_description`.`store_id` = 1) WHERE (`e`.`sku` = 'Unknown')

Please let me know if it helps, you can add as many attribute as description in above query too.

Re: SQL Query to search SKUs and display product info

thanks #Nethues_Sunil for replying, but I am not that advanced when it comes to MySQL commands, the query that you gave me didn't work, so I tried to figure out what it is doing.

 

can you please explain to me the first part of the query:

SELECT `e`.*, IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description`

thanks

Re: SQL Query to search SKUs and display product info

Hi Robert,

 

The first part of the query means for ex :

 

SELECT IF(a.ID=0, 'Some Result If True', 'Some Result If False'), OTHER_COLUMNSFROM ...
WHERE ...

So if 

at_description.value_id >0 // means the value exists

return  

at_description.value

else return

at_description_default.value

 I hope its understandable now, moreover what error are you getting while running the query ?

 

Regards

Sunil

Re: SQL Query to search SKUs and display product info

thanks #Nethues_Sunil for your help, it worked fine... I had to change the tables name and was missing one.

 

regards

Re: SQL Query to search SKUs and display product info

Glad i can help Smiley Happy

 

Regards

Sunil

 

If you've found one of my answers useful, please give "Kudos"

Re: SQL Query to search SKUs and display product info

Hallo Nethues_Sunil

 

I try to use your code and I get a Parse error: syntax error, paste it into the spare details page.phtml page in magento 2.3, could you help me.

 

Thanks 

Re: SQL Query to search SKUs and display product info

@roberto_geronta can you please post your sql query to check whats wrong?