- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Re: SQL Query to search SKUs and display product info
thanks for your reply, I will give it a try.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Re: SQL Query to search SKUs and display product info
Glad i can help
Regards
Sunil
If you've found one of my answers useful, please give "Kudos"
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Re: SQL Query to search SKUs and display product info
@roberto_geronta can you please post your sql query to check whats wrong?