v139 Random Products Image instead of Category Icon

Hi, i wish to add an option for using a random Products Image instead of Category Icon Image,

In includes/modules/category_row.php

after:

if (!defined('IS_ADMIN_FLAG')) {
die('Illegal Access');
}

i've added something like:

$rand_category_query = $db->Execute("select p.products_image as img from ". TABLE_PRODUCTS . " p ".
"LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ".
"p.products_id = ptc.products_id ".
"WHERE LENGTH(p.products_image) > 0 ".
"AND ptc.categories_id = " .$categories->fields['categories_id'].
" ORDER BY rand( ) LIMIT 1");

$random_cat = $rand_category_query->fields['img'];

But

$random_cat instead of zen_image(DIR_WS_IMAGES . $categories->fields['categories_image'] is not working, whts wrong? Thanks :blink:

No one? noboby interested?

Up, it's really important, what's wrong?

Revisited:

$random_products_query = "select p.products_id, p.products_image, pd.products_name
from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_DESCRIPTION . "
pd on p.products_id = pd.products_id
where p.products_id = pd.products_id
and p.products_status = 1
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_image > '' ORDER BY rand( ) LIMIT 1";

$categories_img = $db->Execute($random_products_query);

and

/$categories->fields['categories_image']/$categories_img->fields['products_image']

Now partially working, image is random from some products but is the same for all categories!
Any ideas?

Webmart:

Now partially working, image is random from some products but is the same for all categories!
Any ideas?

I'm no expert, and I don't really know the answer, but I can see that your query is not linking the products you are selecting back to a particular category. You need to match the products selected to a particular category ID into your mysql query.... somehow. :) Good luck!

I don't have a fully-functioning answer, but kamion is right, you need to limit the products to the relevant category. The query would have to include reference to TABLE_PRODUCTS_TO_CATEGORIES and the product being associated with the correct category id, as your original code showed. Not sure if there is anything wrong with your syntax there, but that is the query you need to perfect. I'm also not sure why you need to reference the description to get the image, which is in the base "products" table.

gjh42:

you need to limit the products to the relevant category. The query would have to include reference to TABLE_PRODUCTS_TO_CATEGORIES

Thanks to both but the question is how?

What's wrong on?

$random_products_query = "select p.products_image, p.products_id, ptc.products_id, ptc.categories_id
from ". TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ".
"p.products_id = ptc.products_id
WHERE LENGTH(p.products_image) > ''
AND ptc.categories_id = " .$categories->fields['categories_id'].
" ORDER BY rand( ) LIMIT 1";

$categories_img = $db->Execute($random_products_query);

and

/$categories->fields['categories_image']/$categories_img->fields['products_image']

Expanding on your second attempt above that partially works, maybe something like:

$random_products_query = "select p.products_id, p.products_image, pd.products_name, ptc.categories_id
FROM " . TABLE_PRODUCTS . " p,
     " . TABLE_PRODUCTS_DESCRIPTION . " pd,
     " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc 
WHERE p.products_id = pd.products_id
AND p.products_status = 1
AND pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
AND ptc.categories_id = '" .$categories->fields['categories_id']. "'
AND p.products_image > '' ORDER BY rand( ) LIMIT 1";

$categories_img = $db->Execute($random_products_query);

Just taking a stab here. Totally untested. :lol:

nop.... :lookaroun

Webmart:

nop.... :lookaroun

$patharray = array();
$new_img = "";

 $sql = "SELECT c.categories_id FROM " . TABLE_CATEGORIES . " c WHERE c.parent_id=" .$categories->fields['categories_id']. " and c.categories_status = 1";
    $result = $db->Execute($sql);

if ($result->EOF){
$patharray[] = $categories->fields['categories_id'];
}else{
while (!$result->EOF) {
//check if more categories underneath
$path_lastsub = $result->fields['categories_id']; 
$patharray[] = intval($path_lastsub);      
$result->MoveNext();
                }
}
$i=0;

while($i <= count($patharray)-1) {
$random_products_query = "select p.products_image from ". TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ".
"p.products_id = ptc.products_id
WHERE p.products_image != '' and p.products_status = 1
AND ptc.categories_id = " .$patharray[$i]. " ORDER BY rand( ) LIMIT 1";
$categories_img = $db->Execute($random_products_query); 
$new_img = $categories_img->fields['products_image'];
if ($new_img != ""){
break; 
}
$i++;
}

///if massive drill down catgeories then migt have to hunting a little further for images....keep adding to this pattern if needed

if ($new_img == ""){
$i = 0;
while($i <= count($patharray)-1) {

$sql = "SELECT c.categories_id FROM " . TABLE_CATEGORIES . " c WHERE c.parent_id=" .$patharray[$i]. " and c.categories_status = 1";
    $results = $db->Execute($sql);

if ($results->EOF){
$patharrays[] = $categories->fields['categories_id'];
}else{
while (!$results->EOF) {
//check if more categories underneath
$path_lastsubs = $results->fields['categories_id']; 
$patharrays[] = intval($path_lastsubs);      
$results->MoveNext();
                }
}

$j=0;

while($j <= count($patharrays)-1) {
$random_products_querys = "select p.products_image from ". TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ".
"p.products_id = ptc.products_id
WHERE p.products_image != '' and p.products_status = 1
AND ptc.categories_id = " .$patharrays[$i]. " ORDER BY rand( ) LIMIT 1";
$categories_img = $db->Execute($random_products_querys); 
$new_img = $categories_img->fields['products_image'];
if ($new_img != ""){
break; 
}
$j++;
}
$i++;
}

}

$categories->fields['categories_image'] = $new_img;