0

I am new for SQL, I need to get details from db like,

  1. id, sku, name, status
  2. sku,name,category
  3. sku, category

Mentioned above my example requirement. How can i code for get details from database?

2 Answers 2

0

Direct sql query for Magento

/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readConnection = $resource->getConnection('core_read');

$query = 'SELECT * FROM ' . $resource->getTableName('your-table-name');
//take a look at your config.xml that added your-table-name table for the correct name   
/**
 * Execute the query and store the results in $results
 */
$results = $readConnection->fetchAll($query);

// display result

foreach($results as $row){
   echo $row['fieldName']
}

the categories names are in the table catalog_category_entity_varchar

select cat.*, cv.value as `category_name` from `catalog_category_entity` as cat
    join `catalog_category_entity_varchar` as cv on cat.entity_id = cv.`entity_id`
    join `eav_attribute` as att on att.`attribute_id` = cv.`attribute_id`
    join `eav_entity_type` as aty on att.`entity_type_id` = aty.`entity_type_id`
    where aty.`entity_model` = 'catalog/category' and att.`attribute_code` = 'name' and cv.`store_id` = 0

for default store i Used value 0 (store_id = 0). if you have multiple store check in core_store table

1
  • how can i get sku and image path using sql
    – Gem
    Commented Oct 11, 2016 at 6:28
0

SELECT e.sku, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS name, IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS description

FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_varchar AS at_name_default ON (at_name_default.entity_id = e.entity_id) AND (at_name_default.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'name' AND et.entity_type_code = 'catalog_product')) AND at_name_default.store_id = 0 LEFT JOIN catalog_product_entity_varchar AS at_name ON (at_name.entity_id = e.entity_id) AND (at_name.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'name' AND et.entity_type_code = 'catalog_product')) AND (at_name.store_id = 1) INNER JOIN catalog_product_entity_text AS at_description_default ON (at_description_default.entity_id = e.entity_id) AND (at_description_default.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'description' AND et.entity_type_code = 'catalog_product')) AND at_description_default.store_id = 0 LEFT JOIN catalog_product_entity_text AS at_description ON (at_description.entity_id = e.entity_id) AND (at_description.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'description' AND et.entity_type_code = 'catalog_product')) AND (at_description.store_id = 1)

1
  • how can i display category_name
    – Gem
    Commented Oct 7, 2016 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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