Page 1 of 1

How to get distinct rows

Posted: Thu Oct 12, 2006 1:17 pm
by jabbaonthedais
I have a table just for pictures of items in a shopping cart. It's structured like this:

id | productid | pictureurl | views | clicks

Some products have several pictures, and other only have 1. I need to pull one random picture for each item. I don't think the Distinct function will work, as you can only pull one column (to get it to work like I need). Plus, I'm not sure if some variation of that would pull random pictures, or just the same one every time.

Anyone have any thoughts?

Posted: Thu Oct 12, 2006 6:08 pm
by jabbaonthedais
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Ok, I have this, but its not doing what I need:

Code: Select all

<?
// grab 1 banner for each item
   $result = mysql_query("SELECT DISTINCT productid FROM pictures");
   while($r=mysql_fetch_array($result)) 
   {
    $mydel=$r["productid"];
    $row = mysql_query("SELECT id,picid,picurl,producturl FROM pictures WHERE productid = $mydel and instock = 'yes' ORDER BY RAND()");
     $arow = mysql_fetch_row($row);
     if($arow) {
     echo $arow[0];
     }
     echo " -  test<br>";
   }
?>
That pulls one picture for each item, but if an item is not in stock, it shows a blank space. I figured that if statement would work there, but it apparently doesn't.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Oct 13, 2006 10:15 am
by pickle
One of the conditions in your query is that `instock` must be "yes". Take that condition out.