CODEWITHSUNDEEP
sqlmagento
gadss
gadss

Reputation: 22489

Magento SQL Query to show product manufacturer

I am trying to query the Product Manufacturer in Magento 1.7.0.2 . I browse again and again all the table to where I can get the manufacturer table and connect it with product SKU.

I try this query to hope that I can get the manufacturers of the product:

SELECT 
eav_attribute_option_value.value FROM 
eav_attribute,
eav_attribute_option,
eav_attribute_option_value 
WHERE 
eav_attribute.attribute_code = 'manufacturer' AND
eav_attribute_option.attribute_id = eav_attribute.attribute_id AND
eav_attribute_option_value.option_id = eav_attribute_option.option_id

but it is not equal to the product manufacturer when I compare the result to my magento admin product manufacturer.

My question is that what should I do to query so that I can get the list of manufacturers of the product so that I can sql join in with catalog_product_enity's SKU.

Does anyone has an idea about my case? I am new with magento so please be gentle with me. Any help will be appreciated, Thanks in advance

Upvotes: 2

Views: 5382

Answers (2)

jackie
jackie

Reputation: 634

Little late, but this is what I came up with. Works like a charm.

SELECT cpe.sku, cpev.value, cpf1.manufacturer_value 
FROM catalog_product_entity cpe 
JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id
JOIN catalog_product_flat_1 cpf1 ON cpf1.entity_id = cpe.entity_id
WHERE cpev.attribute_id = 191

Upvotes: 1

Marius
Marius

Reputation: 15206

I hope I understood correctly.
If you want to get the manufacturer for a product, you don't need any query.
This should work. Let's assume that you already have the product in var $_product;

$_product->getAttributeText('manufacturer');//this gets you the label
$_product->getManufacturer(); //gets the manufacturer id

[EDIT]
To get this for all the products do this:

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('manufacturer');
$collection->addAttributeToFilter('status', 1);//optional for only enabled products
$collection->addAttributeToFilter('visibility', 4);//optional for products only visible in catalog and search
foreach ($collection as $product) {
   $sku = $product->getSku();
   $manufacturerId = $product->getManufacturer();
   $manufacturerLabel = $product->getAttributeText('manufacturer');
   //do something with the values above - like write them in a csv or excel
}

Upvotes: 2

Related Questions