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
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
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.