1

Magento 1.7.0.2

I got the attribute ids I want to get from the database, they are 426, 74, 439, 92, 81, 425. In addition to those I want the product name, sku and the categories the product is in. The products visibility should be catalog,search only.

This is what I got so far:

SELECT DISTINCT
cpev.`value` as Name,
cpe.sku as SKU,
#cpe.*,
#cpev.*,
ea.attribute_code
FROM
catalog_product_entity_varchar cpev
INNER JOIN catalog_product_entity cpe ON cpev.entity_id = cpe.entity_id
INNER JOIN eav_attribute ea on cpev.attribute_id = ea.attribute_id where ea.attribute_id in (426, 74, 439, 92, 81, 425);

Not exactly what I need, but maybe a start. How could I get the data I need from the DB?

Thanks!

3

Please try with the following query.

SELECT
cpe.sku as SKU,
if(cpev.attribute_id = 71,cpev.`value`,null) as Name,
cv.value as category_name,
max(if(cpev.attribute_id = 426,cpev.`value`,null)) as '426',
max(if(cpev.attribute_id = 74,cpev.`value`,null)) as '74',
max(if(cpev.attribute_id = 439,cpev.`value`,null)) as '439',
max(if(cpev.attribute_id = 92,cpev.`value`,null)) as '92',
max(if(cpev.attribute_id = 81,cpev.`value`,null)) as '81',
max(if(cpev.attribute_id = 425,cpev.`value`,null)) as '425'
FROM `catalog_product_entity` cpe
INNER JOIN `catalog_product_entity_varchar`cpev ON cpev.entity_id = cpe.entity_id
LEFT JOIN  `catalog_category_product` AS at_category ON at_category.product_id = cpe.entity_id
INNER JOIN `catalog_category_entity_varchar` as cv on at_category.category_id = cv.entity_id
GROUP BY cpe.sku
| improve this answer | |
  • Looking good for a start, but its missing the columns for the mentioned attributes (426, 74, 439, 92, 81, 425) – Arnie Nov 14 '19 at 13:43
  • so you want all other attributes of product as a separate columns in query output. right? like...need to display attribute values after category_name column? like wise? – Meet Shekhat Nov 14 '19 at 13:47
  • Yes, exactly, I need a separate column for all the attributes mentioned. The name of the column should be the attribute code. – Arnie Nov 14 '19 at 13:48
  • No idea how to solve it? – Arnie Nov 14 '19 at 21:10
  • I have edited the query above please check with it and let me know it is working or not. – Meet Shekhat Nov 15 '19 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.