How to get distinct rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

How to get distinct rows

Post 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?
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post 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]
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

One of the conditions in your query is that `instock` must be "yes". Take that condition out.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply