SQL Query to search SKUs and display product info

[robertbits]

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?

[Damian Culotta]

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.

[robertbits]

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

[Nethues_Sunil · Accepted solution]

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.

[robertbits]

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

[Nethues_Sunil]

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

[robertbits]

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

regards

[Nethues_Sunil]

Glad i can help

Regards

Sunil

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

[roberto_geronta]

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 

[robertbits]

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