Magento 1.9 SQL Query – Get Products Price, SKU, Product ID, Description, and Image URL

magento-1.9sql

I have found separate queries that get me some things on their own, but the problem is that the sku order they each have is different, at a given row it changes so I cannot paste the results into a single document. The export tool for Products in magento gives horrendous mixed up rows, it is completely useless. So, since I dont know how the php syntax for magento and its folder architecture is, I cant do it that way, I only am familiar with SQL but not so much with magento tables EAV.

For example the query for price gets me these results

product_id   price
1             45
2             56
3             98
etc

but the query for description gets them so

product_id   description
3             this is description for item 3
1             this is description for item 1
2             as you see, we cant match up all results

For price I have found in SO

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, 

`price_index`.`final_price`,  `price_index`.`min_price`, `price_index`.`max_price`, 
`price_index`.`tier_price` FROM `cp8_catalog_product_entity` AS `e` INNER JOIN 
`catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND
 price_index.website_id = '1' AND price_index.customer_group_id = 0

for Description 

SELECT e.entity_id, e.sku, eav.value AS 'description'
FROM cp8_catalog_product_entity e
JOIN cp8_catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN cp8_eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE ea.attribute_code = 'description'

So, I am strugling to get one complete result that contains:

product_id, name, description, price, url to the image, meta keywords, etc

Best Answer

Problem seems to be that description, meta_keywords art not part of flat table ...

Please try this query:

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0

If this attributes are part of flat tables this should work:

MAGENTO_ROOT/myFile.php

<?php
require_once('./app/Mage.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);

umask(0);
Mage::app();

$attributes = array(
    'description',
    'meta_keyword'
);

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect($attributes)
    ->addUrlRewrite()
    ->addMinimalPrice()
    ->addFinalPrice();

echo $collection->getSelect()->__toString();

Note: you can set this in Manage Attributes -> Use in Product Listing.

If not, you I had to diable flat tables first ...

MAGENTO_ROOT/myFile.php

<?php
require_once('./app/Mage.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);

umask(0);
Mage::app();

$helper = Mage::helper('catalog/product_flat');
$status = $helper->getProcess()->getStatus();
$helper->getProcess()->setStatus(Mage_Index_Model_Process::STATUS_RUNNING);


$attributes = array(
    'description',
    'meta_keyword'
);

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect($attributes)
    ->addUrlRewrite()
    ->addMinimalPrice()
    ->addFinalPrice();

echo $collection->getSelect()->__toString();

$helper->getProcess()->setStatus($status);

To output SQL-query, you can use this:

echo $collection->getSelect()->__toString();
Related Topic