MySQL Query to get attributes for a product

[mcgdave]

Hi there

 

I need to write a MySQL query to get a list of all attribute groups and attributes for a given product. Here's some pseudocode (pseudoSQL):

 

SELECT

attribute_group_name,

attribute_name,

ps_product_attribute.price

FROM

ps_product_attribute

JOIN ....

...WHERE ps_product_attribute.id_product = 23

 

It's so (in another application) I can generate dropdowns for users to choose product attributes. I need to show the impact on the product price of each attribute.

 

But I'm confused, because I need to link ps_product_attribute to either ps_product_attrbute_combination or ps_attribute_impact, in order then to link to table ps_attribute, and when I do that, I get duplicate values that I can't get rid of with a SELECT DISTINCT.

 

Can anyone tell me what tables I need to use to simplify my query?

 

Thanks for your help!

 

Dave

[KPH Computers]

Try this:

 

SELECT pa.*, ag.id_attribute_group, ag.is_color_group, agl.name AS group_name, al.name AS attribute_name, a.id_attribute, pa.unit_price_impact
FROM ps_product_attribute pa
LEFT JOIN ps_product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
LEFT JOIN ps_attribute a ON a.id_attribute = pac.id_attribute
LEFT JOIN ps_attribute_group ag ON ag.id_attribute_group = a.id_attribute_group
LEFT JOIN ps_attribute_lang al ON (a.id_attribute = al.id_attribute AND al.id_lang = 1)
LEFT JOIN ps_attribute_group_lang agl ON (ag.id_attribute_group = agl.id_attribute_group AND agl.id_lang = 1)
WHERE pa.id_product = 1
ORDER BY pa.id_product_attribute

 

Make sure to change the agl.id_lang and pa.id_product to match your language and product id.

Also change the table prefix to match whatever your database uses.

 

HTH

 

Kev

Edited May 30, 2012 by KPH Computers (see edit history)

[shacker]

In the class product.php, you have the function to get attributes

[mcgdave]

Thanks for the pointers guys - I've got Kev's SQL working, and I'm looking at product.php now because I've got a feeling that's going to become very useful very soon.

 

Have a good week!

[naeems]

Instead of SQL query you can get the same result from using getAttributeCombinations() function in "classes/Product.php"

 

For example:

 

$myproduct = new Product(23); //// 23 is your product id

$attributes = $myproduct->getAttributeCombinations($cookie->id_lang);

 

OR if you want to get attributes by product attribute id:

 

$attribute = $myproduct->getAttributeCombinationsById(45, $cookie->id_lang); /////// 45 is id_product_attribute

 

Also you can get attribute groups using below function:

 

$attribute_groups = $myproduct->getAttributesGroups($cookie->id_lang);

 

 

Naeem

[muhsinap]

Hi there

 

I need to write a MySQL query to get a list of all attribute groups and attributes for a given product. Here's some pseudocode (pseudoSQL):

 

SELECT

attribute_group_name,

attribute_name,

ps_product_attribute.price

FROM

ps_product_attribute

JOIN ....

...WHERE ps_product_attribute.id_product = 23

 

It's so (in another application) I can generate dropdowns for users to choose product attributes. I need to show the impact on the product price of each attribute.

 

But I'm confused, because I need to link ps_product_attribute to either ps_product_attrbute_combination or ps_attribute_impact, in order then to link to table ps_attribute, and when I do that, I get duplicate values that I can't get rid of with a SELECT DISTINCT.

 

Can anyone tell me what tables I need to use to simplify my query?

 

Thanks for your help!

 

Dave

Thank you so much

And I need image also, how to get image of a product ? I hop you will answer soon. Thank you

 

Thank you so much

And I need image also, how to get image of a product ? I hop you will answer soon. Thank you

[kelvz]

$id_image = Product::getCover(17); //17 is your Product ID

// get Image by id

if (sizeof($id_image) > 0) {

$image = new Image($id_image['id_image']);

// get image full URL

$large_image_url = _PS_BASE_URL_._THEME_PROD_DIR_.$image->getExistingImgPath()."-large".".jpg";

$medium_image_url = _PS_BASE_URL_._THEME_PROD_DIR_.$image->getExistingImgPath()."-medium".".jpg";

$small_image_url = _PS_BASE_URL_._THEME_PROD_DIR_.$image->getExistingImgPath()."-small".".jpg";

}

[Anton_bcn]

$id_image = Product::getCover(17); //17 is your Product ID // get Image by id if (sizeof($id_image) > 0) { $image = new Image($id_image['id_image']); // get image full URL $large_image_url = _PS_BASE_URL_._THEME_PROD_DIR_.$image->getExistingImgPath()."-large".".jpg"; $medium_image_url = _PS_BASE_URL_._THEME_PROD_DIR_.$image->getExistingImgPath()."-medium".".jpg"; $small_image_url = _PS_BASE_URL_._THEME_PROD_DIR_.$image->getExistingImgPath()."-small".".jpg"; }

 

Is it possible to getCover but for combinations of product?

 

Or somehow to get images only associated with combinations?

 

Lol this forum is dead?... only professinal here to help by money...

 

I found solution to get default image for combinations is in product_attribute_image value of id_image

 

so we can ask it from db like this

 

to get default image for this combination we need something like:

 

SELECT `id_image` FROM `product_attribute_image` WHERE `id_product_attribute` = 16

 

it give us all images for combination

 

Hope someone found it useful

Edited May 26, 2013 by anseme (see edit history)

[p-d-s]

Hi, this is an old post - but I am trying to modify our export script.

Everything works, without the combinations.

 

At the moment this is getting exported:

 

• ID 7
• ID 7
• ID 7
• ID 7

 

But I need

 

• ID 7-Banana
• ID 7-Apple
• ID 7-Strawberry
• ...

 

Because the import tells me, there are multiple products with same id.

Has anyone solved this? 

I know how to display the array, but I don't know how to loop it inside the loop. 

 

 

Thanks

[amsawad]

in getAttributeCombinations()

 

I found

GROUP BY pa.`id_product_attribute`, ag.`id_attribute_group`
ORDER BY pa.`id_product_attribute`';

 

I try

 

GROUP BY pa.`id_product_attribute` DESC, ag.`id_attribute_group` DESC

 

 

But not working

 

Thanks

public function getAttributeCombinations($id_lang)
	{
		if (!Combination::isFeatureActive())
			return array();

		$sql = 'SELECT pa.*, product_attribute_shop.*, ag.`id_attribute_group`, ag.`is_color_group`, agl.`name` AS group_name, al.`name` AS attribute_name,
					a.`id_attribute`, pa.`unit_price_impact`
				FROM `'._DB_PREFIX_.'product_attribute` pa
				'.Shop::addSqlAssociation('product_attribute', 'pa').'
				LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON pac.`id_product_attribute` = pa.`id_product_attribute`
				LEFT JOIN `'._DB_PREFIX_.'attribute` a ON a.`id_attribute` = pac.`id_attribute`
				LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON ag.`id_attribute_group` = a.`id_attribute_group`
				LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute` AND al.`id_lang` = '.(int)$id_lang.')
				LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group` AND agl.`id_lang` = '.(int)$id_lang.')
				WHERE pa.`id_product` = '.(int)$this->id.'
				GROUP BY pa.`id_product_attribute`, ag.`id_attribute_group`
				ORDER BY pa.`id_product_attribute`';

[shacker]

i use thsi to get names and works for me

 

$sorgu3     = 'SELECT sa.`id_product_attribute`,sa.`quantity` AS paq,ag.`id_attribute_group`, ag.`is_color_group`, agl.`name` AS group_name, agl.`public_name` AS public_group_name, a.`id_attribute`, al.`name` AS attribute_name,
        a.`color` AS attribute_color, pa.`id_product_attribute`, pa.`quantity`, pa.`price`, pa.`ecotax`, pa.`weight`, pa.`default_on`, pa.`reference`,pa.`price` AS pprice 
        FROM `'._DB_PREFIX_.'product_attribute` pa 
        LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON pac.`id_product_attribute` = pa.`id_product_attribute` 
        LEFT JOIN `'._DB_PREFIX_.'attribute` a ON a.`id_attribute` = pac.`id_attribute` 
         LEFT JOIN `'._DB_PREFIX_.'stock_available` sa ON pac.`id_product_attribute` = sa.`id_product_attribute` 
        LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON ag.`id_attribute_group` = a.`id_attribute_group` 
        LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON a.`id_attribute` = al.`id_attribute` 
        LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON ag.`id_attribute_group` = agl.`id_attribute_group` 
        WHERE pa.`id_product` = '.$veri['id_product'].' AND pac.`id_product_attribute` = '.$veri4['id_product_attribute'].' 
        AND al.`id_lang` = '.$langs.' 
        AND agl.`id_lang` = '.$langs.' 
        ORDER BY agl.`name` 
        ';

[newbie87]

Hello,

for some unknown reason, the combinations/attributes function is not active in our backend. Information is there but, it is not showing with any products. Our data was uploaded via a 3rd party script. Error message says Feature has been disabled. Links to Performance page but, Combinations and Customer Groups are grayed out and not accessible.

I went through the database and deleted all of the info entered under each attribute table thinking to start fresh. cleared cache. However that did not help.

Any ideas as to what could be causing this and how to correct it?

thanks in advance for a reply.

Edited March 28, 2015 by newbie87 (see edit history)

[shacker]

in advanced parameters, performance, is the combinations option enabled?

[newbie87]

Hello and thanks for your reply. The attributes page has a message and link to the Performance page. Combinations mode is disabled - greyed out and not accessible at all. The only enabled function in Optional features section is Features. Combinations is grayed out. Customer Groups is also completely grayed out in the cart although we have customer groups set up in the cart. Copy of that section on this page:

      

 BEGIN ************      Optional features

 

Some features can be disabled in order to improve performance. ** added where a selected radio button actually appears.

 

Combinations

Yes     **No

You cannot set this parameter to No when combinations are already used by some of your products

Features

** Yes   No

Customer Groups

Yes    ** No

 

***************************** END

Edited March 30, 2015 by newbie87 (see edit history)

[shacker]

try to use the combination generator, and try to generate some of them, and check again if can enable. if you use multistore, you need to select the store first (not all stores)

[AntoineBa]

Hello I encountered a problem with this query in the results that phpmyadmin returns in a column I have several times "Hair" and "Inches" is it possible to separate hair into another column and inch in another ?

 

 

[softhightech]

try this : SELECT ps_attribute_group_lang.public_name, ps_attribute_lang.name FROM `ps_attribute` join ps_attribute_group_lang on(ps_attribute_group_lang.id_attribute_group = ps_attribute.id_attribute_group) join ps_attribute_langon (ps_attribute_lang.id_attribute = ps_attribute.id_attribute) join ps_product_attribute_shop on (ps_product_attribute_shop.id_product_attribute = ps_attribute.id_attribute) where ps_attribute.id_attribute = 189

[AntoineBa]

try this : SELECT ps_attribute_group_lang.public_name, ps_attribute_lang.name FROM `ps_attribute` join ps_attribute_group_lang on(ps_attribute_group_lang.id_attribute_group = ps_attribute.id_attribute_group) join ps_attribute_langon (ps_attribute_lang.id_attribute = ps_attribute.id_attribute) join ps_product_attribute_shop on (ps_product_attribute_shop.id_product_attribute = ps_attribute.id_attribute) where ps_attribute.id_attribute = 189

Hello thanks for your reply

there is a syntax problem

 

 

Error

SQL query:

SELECT ps_attribute_group_lang.public_name, ps_attribute_lang.name FROM `ps_attribute` join ps_attribute_group_lang on(ps_attribute_group_lang.id_attribute_group = ps_attribute.id_attribute_group) join ps_attribute_langon (ps_attribute_lang.id_attribute = ps_attribute.id_attribute) join ps_product_attribute_shop on (ps_product_attribute_shop.id_product_attribute = ps_attribute.id_attribute) where ps_attribute.id_attribute = 189 LIMIT 0, 30

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ps_attribute_lang.id_attribute = ps_attribute.id_attribute) join ps_product_att' at line 1

[softhightech]

it's ok now change the id_attribute with ur id good luck.

 

SELECT ps_attribute_group_lang.public_name, ps_attribute_lang.name FROM `ps_attribute` 

join ps_attribute_group_lang on(ps_attribute_group_lang.id_attribute_group = ps_attribute.id_attribute_group) 

join ps_attribute_lang on(ps_attribute_lang.id_attribute = ps_attribute.id_attribute) 

join ps_product_attribute_shop on (ps_product_attribute_shop.id_product_attribute = ps_attribute.id_attribute) 

where ps_attribute.id_attribute = 189

[AntoineBa]

Hello,

Sorry about the delay.



On my screenshot http://www.hostingpics.net/viewer.php?id=704639Capturede769cran20170320a768164743.png



You see that:



Pouces | 10 Pouces - 26 cm

Cheveux | Brésilien

Couleur | Noir naturel



It is one above the other what I would like to display in other column create on the fly



Inches | 10 inches - 26 cm | Hair Care | Photos | Color | Natural black



Do you understand what I would like to do?





Waiting for your answer.

Edited March 20, 2017 by Jahyno97232 (see edit history)