SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: What am I doing wrong?!
-
May 5, 2004, 16:31 #1
What am I doing wrong?!
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:
Code://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' />"); } } }
Cheers in advance...
-
May 5, 2004, 17:19 #2
- Join Date
- Mar 2004
- Location
- East Bay Area, CA, USA
- Posts
- 28
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 5, 2004, 18:50 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 6, 2004, 03:49 #4
Hi,
I don't quite see why you are getting categories without any products, because the first thing you do is this:
Code:SELECT * FROM catlookup ORDER BY RAND() LIMIT 0,1
Never ascribe to malice,
that which can be explained by incompetence.
Your code should not look unmaintainable, just be that way.
-
May 6, 2004, 07:32 #5
-
May 6, 2004, 09:20 #6
Cracked it...
Code://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"];
Works a treat now.
Cheers
-
May 6, 2004, 12:28 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
Code: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
-
May 7, 2004, 03:09 #8
- Join Date
- Nov 2002
- Location
- North West, UK
- Posts
- 545
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 7, 2004, 03:35 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
thanks for the kind words, el gumbo
-
May 14, 2004, 12:20 #10
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
Bookmarks