What am I doing wrong?!

[themissingelf]

I'm sure that I am doing something fundamentally wrong here; I just cannot get my head around it...

Basically, I have a categories table, a products table, and a category lookup table (catlookup).

I want to randomly generate a "featured" category; pulling the category name and description from the categories table and a random image for that category from one of the products in the product table (the link between the two being the catlookup table).

The problem I have is that some categories do not contain any products, therefore, I do not want to "feature" them. I always want the script to find a category that has at least one product. The following script keeps including categories that don't have products, along with those that do:

//pull a selection of categories and associated images...
 //establish which categories have and entry in the catlookup table and are therefore active and contain products.
 $activecats = @mysql_query("SELECT * FROM catlookup ORDER BY RAND() LIMIT 0,1");
 if(!$activecats) {
 echo(mysql_error());
 }
  while($active = mysql_fetch_array($activecats)) {
  $cid = $active["cid"];
  $pid = $active["pid"];
   //get all the categories from the categories table...
   $category = @mysql_query("SELECT * FROM categories WHERE id=$cid ORDER BY RAND()");
	if(!$category) {
	echo(mysql_error());
	}
	while($cats = mysql_fetch_array($category)) {
	 $catid = $cats["id"];
	 $category = $cats["category"];
	 $description = $cats["cat_desc"];
   
	  
	  //get a random image for the category from one of the products in the category
	  $image_for_cat = mysql_query("SELECT id,thumb,visible,complete FROM products WHERE id=$pid AND thumb != 'thnoimage.gif' AND visible='Y' AND complete='y' ORDER BY RAND() LIMIT 0,1");
	  if(!$image_for_cat) {
	  echo(mysql_error());
	  }
	  while($image = mysql_fetch_array($image_for_cat)) {
	  $thumb = $image["thumb"]; 
	  echo("<h4>CAT: ".$category. "</h4><p>Description: ".$description."</p><img src='/images/products/thumbs/".$thumb."' alt='Available now from our current stock' />");
	  }
	 }	 
   }

Perhaps I'm just going about it the wrong way?

Cheers in advance...

[Everah]

Do the products in your PRODUCT table have the potential to be in more than one category? If not, you can join data from the two tables without the lookup, pulling all categories and all products using a combination FOR loop.

Can you post your field names of the three tables? There may be an easy work around for this.

[r937]

i don't do php but it looks like you are always getting the same product --

SELECT id,thumb,visible,complete FROM products WHERE id=$pid

i don't see why you would use RAND() on this, because presumably each id in the products table is unique

[kleineme]

Hi,

I don't quite see why you are getting categories without any products, because the first thing you do is this:

SELECT * FROM catlookup ORDER BY RAND() LIMIT 0,1

which selects exactly one randomly chosen combination of cid and pid, so there has to be at least this product in the selected category.

[guelphdad]

i don't do php but it looks like you are always getting the same product --

SELECT id,thumb,visible,complete FROM products WHERE id=$pid

Rudy the $pid is passed from the original query so it would change randomly there.

[themissingelf]

Cracked it...

 //establish product ID's and get images for items that are NOT missing an image, set to invisible, or incomplete
$prod_check = @mysql_query("SELECT id,thumb,visible,complete FROM products WHERE visible='Y' AND complete='y' AND thumb != 'thnoimage.gif' ORDER BY RAND() LIMIT 0,1");
 if(!$prod_check) {
 echo(mysql_error());
 }
  $prods = mysql_fetch_array($prod_check);
  $prod_id = $prods["id"];
  $thumb = $prods["thumb"];
   
   //establish which categories have and entry in the catlookup table and are therefore active and contain products.
   $activecats = @mysql_query("SELECT * FROM catlookup WHERE pid=$prod_id ORDER BY RAND() LIMIT 0,1");
   if(!$activecats) {
   echo(mysql_error());
   }
	$active = mysql_fetch_array($activecats);
	$cid = $active["cid"];
	$pid = $active["pid"];
  
	 //get all the categories from the categories table...
	 $category = @mysql_query("SELECT * FROM categories WHERE id=$cid ORDER BY RAND()");
	  if(!$category) {
	  echo(mysql_error());
	  }
	  $cats = mysql_fetch_array($category);
	   $catid = $cats["id"];
	   $category = $cats["category"];
	   $description = $cats["cat_desc"];

Suddenly realised that some products don't have images, some are set to not be visible on the site, and some are nt fully set up - all of which mean that they may exist in a category but not be available to show. Changed the query to start by finding products that fit the bill and then the associated category.

Works a treat now.

Cheers

[r937]

okay, i get it now

you get one random product that is visible and complete and has a thumbnail

then, based on this one product, you get one category id at random,
from amongst all the category ids that this product is associated with
in the lookup table

then, having the category id, you retrieve the category name and
description from the category table

would you consider doing these three steps all in one query?

select p.id
     , p.thumb
     , p.visible
     , p.complete 
     , c.id as cid
     , c.category
     , c.cat_desc
  from products  p
inner
  join catlookup lu
    on p.id = lu.pid
inner
  join categories c
    on lu.cid = c.id
 where p.visible = 'y' 
   and p.complete = 'y' 
   and p.thumb != 'thnoimage.gif' 
order 
    by rand() 
limit 0,1

[elgumbo]

rudy,

reading your posts has made me look at my own SQL in a new light. The first site I did had on average 4 queries per page (!). On my new site I'm down to about 2 but always aiming for 1 wherever possible

Thanks

[r937]

thanks for the kind words, el gumbo

[themissingelf]

rudy,

Had a go but could not get it to work. Probably because I've not quite got me head round it and most likely making a mistake in extracting the records from the query.

I'll have to give it more time.

Cheers

Matt