Jump to content

MySQL Query to get attributes for a product


mcgdave

Recommended Posts

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

Link to comment
Share on other sites

  • 4 weeks later...

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 by KPH Computers (see edit history)
  • Like 2
Link to comment
Share on other sites

  • 6 months later...

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

Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

  • 3 weeks later...

$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";

}

Link to comment
Share on other sites

  • 5 weeks later...
$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 by anseme (see edit history)
Link to comment
Share on other sites

  • 1 year later...

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

Link to comment
Share on other sites

  • 2 weeks later...

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`';
Link to comment
Share on other sites

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` 
        ';
Link to comment
Share on other sites

  • 1 month later...

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 by newbie87 (see edit history)
Link to comment
Share on other sites

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 by newbie87 (see edit history)
Link to comment
Share on other sites

  • 1 year later...
  • 4 weeks later...

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

Link to comment
Share on other sites

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: dot.gif

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: dot.gif

#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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 by Jahyno97232 (see edit history)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...