How to get product manufacturer name from manufacturer value id by mysql?

Question

I know how to get the product manufacturer id, but I need the product manufacturer id & name using MySQL.

75 is the attribute id for getting product manufacturer id.

Anyone can help?

I want it in SQL format.

Answer 1

First of all I want to get this out of the way.
If you have the product as an object you can get the manufacturer id like this:

$product->getManufacturer();

and for the name:

$product->getAttributeText('manufacturer');

Now for the sql part...I don't know why you would need it but here goes.
Let's say the product id is 10. Try this query.

SELECT
    i.value as manufacturer_id,
    v.value as manufacturer_name
FROM 
    catalog_product_entity_int i
    LEFT JOIN 
        eav_attribute_option o 
        ON i.value = o.option_id
    LEFT JOIN
        eav_attribute_option_value v
        ON o.option_id = v.option_id AND
        v.store_id = 0
WHERE 
    i.entity_id = 10 AND
    i.attribute_id = 75 AND 
    i.store_id = 0;

if you want the values for an other store view just replace the 0 in v.store_id = 0 and i.store_id = 0 with the value of your store id. 0 means default values.