cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query to search SKUs and display product info

SOLVED

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?