SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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' />");
    	  }
    	 }	 
       }
    Perhaps I'm just going about it the wrong way?

    Cheers in advance...

  2. #2
    SitePoint Enthusiast Everah's Avatar
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    which selects exactly one randomly chosen combination of cid and pid, so there has to be at least this product in the selected category.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 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
    Rudy the $pid is passed from the original query so it would change randomly there.

  6. #6
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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"];
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist elgumbo's Avatar
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words, el gumbo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •