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?
Solved! Go to Solution.
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.
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.
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`
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 ...
at_description.value_id >0 // means the value exists
I hope its understandable now, moreover what error are you getting while running the query ?
thanks #Nethues_Sunil for your help, it worked fine... I had to change the tables name and was missing one.
Glad i can help
If you've found one of my answers useful, please give "Kudos"