Category images from product images

[korayp]

Hi all,

I run a site with more than 40000 products with over 1000 categories.

The following code allows me to choose random product images from the corresponding category as category images.

File to modify includes/functions/functions_lookups:

/*
 * Return category's image
  */
function zen_get_categories_image($what_am_i){
	global $db;

	$the_products_category=$db->Execute("SELECT categories_image FROM ".TABLE_CATEGORIES." WHERE categories_id=$what_am_i");
	$image=$the_products_category->fields['categories_image'];

	$image_path=DIR_FS_CATALOG.DIR_WS_IMAGES.$image;

	$image_exists=(boolean)@getimagesize($image_path);

	$last_update=date("Y-m-d H:i:s", mktime( date( 'H' ),date( 'i' ),date( 's' ), date( 'm' ),date( 'd' )-10 ,date( 'Y' ) ) );

	if ($image=="" || $image==PRODUCTS_IMAGE_NO_IMAGE || $image_exists===false){
		$noimage=1;
	} else {
		return $image;
		exit;
	}

	//Patch by KP
	//Category pictures from products

	if($noimage==1){
		$sub_cats=qc_get_child_categories($what_am_i);

		if(count($sub_cats)==0){
			$image_qry="SELECT products_image FROM ".TABLE_PRODUCTS." WHERE products_id IN (SELECT products_id FROM ".TABLE_PRODUCTS_TO_CATEGORIES." WHERE categories_id=".$what_am_i.") AND products_image NOT LIKE '".PRODUCTS_IMAGE_NO_IMAGE."' AND products_image IS NOT NULL ORDER BY RAND() LIMIT 1";

			$image_res=$db->Execute($image_qry);
			$image=$image_res->fields['products_image'];
			//print $image;
			$db->Execute("UPDATE ".TABLE_CATEGORIES." SET ".TABLE_CATEGORIES.".categories_image='$image' WHERE categories_id=$what_am_i AND (last_modified IS NULL OR last_modified<='$last_update')");
			return $image;
		} else {
			$n=0;
			while($n<count($sub_cats)){
				$image_qry="SELECT products_image FROM ".TABLE_PRODUCTS." WHERE products_id IN (SELECT products_id FROM ".TABLE_PRODUCTS_TO_CATEGORIES." WHERE categories_id=".$sub_cats[$n].") AND products_image NOT LIKE '".PRODUCTS_IMAGE_NO_IMAGE."' AND products_image IS NOT NULL ORDER BY RAND() LIMIT 1";

				//$image_qry="SELECT products_image FROM ".TABLE_PRODUCTS." WHERE products_image NOT LIKE '".PRODUCTS_IMAGE_NO_IMAGE."' AND products_image IS NOT NULL AND products_id IN (SELECT products_id FROM ".TABLE_PRODUCTS_TO_CATEGORIES." WHERE categories_id IN (SELECT categories_id FROM ".TABLE_CATEGORIES." WHERE parent_id=".$sub_cats[$n].") ) ORDER BY RAND() LIMIT 1";

				$image_res=$db->Execute($image_qry);
				$image=$image_res->fields['products_image'];;
				if ($image){
					$db->Execute("UPDATE ".TABLE_CATEGORIES." SET ".TABLE_CATEGORIES.".categories_image='$image' WHERE categories_id=$what_am_i AND (last_modified IS NULL OR last_modified<='$last_update')");
					return $image;
					exit;
				} else {
					$sub_cats2=qc_get_child_categories($sub_cats[$n]);
					$m=0;
					while($m<count($sub_cats2)){
						$image_qry2="SELECT products_image FROM ".TABLE_PRODUCTS." WHERE products_id IN (SELECT products_id FROM ".TABLE_PRODUCTS_TO_CATEGORIES." WHERE categories_id=".$sub_cats2[$m].") AND products_image NOT LIKE '".PRODUCTS_IMAGE_NO_IMAGE."' AND products_image IS NOT NULL ORDER BY RAND() LIMIT 1";
	
						$image_res2=$db->Execute($image_qry2);
						$image=$image_res2->fields['products_image'];;
		
						if ($image){
							$db->Execute("UPDATE ".TABLE_CATEGORIES." SET ".TABLE_CATEGORIES.".categories_image='$image' WHERE categories_id=$what_am_i AND (last_modified IS NULL OR last_modified<='$last_update')");
							return $image;
							exit;
						}
						$m++;
					}
				}
				$n=$n+1;
			}
		}
	}
}

The code looks for manullay entered category images.

If not found queries the products table for valid images. Empty categories and nested categories are queried for valid images.

when a relevant image is found categories table is updated with the category image.

The image is static for 10 days (upto my liking). You can change this "cache time modifying "date( 'd' )-10" part.

Enjoy !

[Muzz]

Nice. Whats your website to see in action?

[korayp]

Nice. Whats your website to see in action?

The previous version without the 10 day static record concept is ay http://www.nhesapli.com

[Louis]

Hi,

Very good idea. However...

I took your code as is and updated the category image section in the functions file. This caused the categories with images to not appear on the screen at all. The sub-cats without images still appeared but still without an image. Done on the silverfish template. Any ideas?