show category image from sub-category problem

Question

Hi,

Please let me explain my question first. I have a webshop which has a lot of products, but also a lot of categories. The product pictures are imported from openicecat.biz, it's a content provider too. When there is no description or image available for a certain product, a noimage.jpg is displayed

Someone made a code which picks a image from the product list and uses it as a image for that category. (when available) The trouble comes when a category has a sub-category with images and a sub-category without images. The noimage.jpg is displayed instead it shows a image from a sub-category with images.

For example

catelog -> components -> card readers (no image) -> internal card reader (image)
                                                 -> external card reader (no image)

The way the code snippet was designed, only a image will show when there are both pictures in internal and external card reader, not when one sub category does not have images.

What I'd like to have is that when for example the sub-category internal cardreader has products with images, and the sub-category external card reader has no images, a image of a external card reader is displayed as category image for card readers.

Example

catelog -> components -> card readers (image of internal card reader, instead of no image)                                    
                                       -> internal card reader (image)
                                       -> external card reader (no image)

I hope you'll understand what I mean.

This is the code snippet:

// Start auto fetch category image from product
if($categories['categories_image'] == "") {
$categories_img_query = tep_db_query("select products_image from " . TABLE_PRODUCTS . " p, products_to_categories pc WHERE p.products_id = pc.products_id AND pc.categories_id = '{$categories['categories_id']}' AND p.products_image IS NOT NULL order by p.products_id ASC");

if(tep_db_num_rows($categories_img_query) > 0) {
 $categories_img = tep_db_fetch_array($categories_img_query);
 $categories['categories_image'] = $categories_img['products_image'];
}
else {
 $categories_img_parent_query = tep_db_query("select categories_id from categories WHERE parent_id = '{$categories['categories_id']}'");

 while($categories_img_parent = tep_db_fetch_array($categories_img_parent_query)) {
   $categories_img_query = tep_db_query("select products_image from " . TABLE_PRODUCTS . " p, products_to_categories pc WHERE p.products_id = pc.products_id AND pc.categories_id = '{$categories_img_parent['categories_id']}' AND p.products_image IS NOT NULL order by p.products_id ASC");
   if(tep_db_num_rows($categories_img_query) > 0) {
     $categories_img = tep_db_fetch_array($categories_img_query);
     $categories['categories_image'] = $categories_img['products_image'];
   }
 }
}
}
// End auto fetch category image from product

Can anyone help me complete this snippet?

btw1, it's for oscommerce.

btw2, omg, I allmost spend 40 minutes explaining and typing this problem, is this worth a medal too (medal of Eternal Patience) ;-)

Answer 1

First of all, some criticism. Your WHERE clause should be in your JOIN. That is:

WHERE p.products_id = pc.products_id

should actually be

p JOIN pc USING (products_id)

This is faster clearer.

About your specific problem:

I don't understand how you can have blank images at all (you always specify products_image IS NOT NULL) so the implication is that noimage.jpg is actually stored as the value for that image. In that case the problem is simply that you are adding whatever image is found, even if it is noimage.jpg. Instead of adding the first image from tep_db_fetch_array(), loop through it and see if a non-noimage was found. For example:

while ($row = tep_db_fetch_assoc($categories_img_query)) {
  if ($row['products_image'] <> 'noimage.jpg'
     or !isset($categories['categories_image'])
  ) {
     $categories['categories_image'] = $row['products_image'];
  }
}

This also assumes you don't care about adding a SPECIFIC image for the category, just any non-noimage.jpg if one is available.