[SOLVED] Fetch id_image from product attribute id

[remyessers]

Hi,

 

Im using a module to display the available colors from a product on the product-list. It uses this code to select the colors and display a <span> tag on my product on the list:

 

$attributesGroups = Db::getInstance()->ExecuteS('
SELECT 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.*
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_.'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` = '.$idP.'
AND al.`id_lang` = '.$id_lang.'
	AND ag.`id_attribute_group` in ('.$lstM2P.')
AND agl.`id_lang` = '.$id_lang.'
ORDER BY agl.`public_name`, al.`name`');
if (Db::getInstance()->numRows()){
 $vir = 0;
	$previous2 = '';
	$previous = '';
	foreach ($attributesGroups AS $k => $rowattr){
$attribuut = $rowattr['id_product_attribute'];


		if($rowattr['public_group_name'] != $previous2){
		$vir = 0;
	 }

	 if ($rowattr['attribute_name'] != $previous)
	 {
	   if($rowattr['is_color_group'] && $rowattr['attribute_color']){
		  if(Attribute::checkAttributeQty($rowattr['id_product_attribute'],1)>=0){
			  $attributs[$rowattr['public_group_name']][] = ' <span class="squareAttrSpe" id="1" style="background:'.$rowattr['attribute_color'].'" >   </span>';
				}

 

As you can see, it already fetches the id_product_attribute ($rowattr['id_product_attribute']). I want to extend the query to join the table ps_product_attribute_image on id_product_attribute, but I don't know how to build that query. Does anybody with a bit of Mysql knowledge know the correct line?

 

Best regards,

 

Remy

Edited April 25, 2013 by remyessers (see edit history)

[Terragg]

Since you're just looking up things in the product_attribute_image by the id_product_attribute column, a simple left join should get you what you need. Look for the pai aliases in the updated code chunk below.

 

'SELECT 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,  pai.id_image AS attribute_color_image, pa.*
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_.'product_attribute_image` AS pai ON pai.`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`
LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON ag.`id_attribute_group` = agl.`id_attribute_group`
WHERE pa.`id_product` = '

 

That should make the image_id (if it's available) accessible through $rowattr['attribute_color_image'] in your

foreach loop. There's nothing special about the name attribute_color_image, so changing it won't break anything.

 

HTH,

[remyessers]

Thanks! It works great. Ive got one more problem, perhaps you can help me with that too..

The attribute-images are sometimes 2 different formats. One is base-directory/id_product-id_image/product-name.jpg:

base-directory/'.$rowattr['id_product'].'-'.$rowattr['id_image'].'-relatedproducts/'.$rowattr['link_rewrite'].'.jpg';

The second one is the same format but without id_product, so like: base-directory/id_image/product-name.jpg:

base-directory/'.$rowattr['id_image'].'-relatedproducts/'.$rowattr['link_rewrite'].'.jpg';

 

I want the image to appear inside the span tag:

<span class="squareAttrSpe" id="'.$rowattr['id_image'].''.$rowattr['link_rewrite'].'" style="background:'.$rowattr['attribute_color'].'" ><img src="imglocation"/></span>

 

Is there a check to see if image exist? If not, use the other format?

Edited April 24, 2013 by remyessers (see edit history)

[Terragg]

Take a look at the Image::getExistingImgPath(). You can use that as sort of a base for making your function. It builds a file path and then runs a file_exists()on it, much like what you're trying to do.

 

HTH,

[Anton_bcn]

IMHO it is better analyze how it works in default prestashop theme and then adapte to you own

 

If you don't know mysql you can use some pretashop functions

 

like:

 

getAttributes() or isColorAttribute() and so one, u need just see functions of classes to see what they have. Shure you can find all what you need.

 

Just open and analyze classes like Attribute.php and AttributeGroup.php in classes folder and all what you need