Member Avatar for CFROG

I'm trying to set up an app for users to put their individual photos into different albums, eg. friends, family, whatever. I would like one pic from that album to be displayed for each album along with the album name. I thought SELECT DISTINCT would do the trick for me but I'm not getting the desired results. It's still displaying all pictures with the appropriate album name even if they are duplicates. I'm sure I'm overlooking something simple as usual but my head is in a fog and I'm just not seeing it.

Here is where I'm at.

$sql="SELECT DISTINCT album, pic FROM user_profile_pics WHERE user_id='$uid' AND pic !='' ORDER BY def DESC";
$res=mysql_query($sql);

$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 

while($row = mysql_fetch_array($res)) { 

echo ($i!= 0 && $i%$num_of_cols == 0)?'</tr><tr>':''; 
echo "<td valign=\"bottom\" align=\"center\"><a href=\"../xpics/profiles/".$row['pic']."\"><img src=\"../xpics/profiles/small/".$row['pic']."\" border=\"none\"></a>";
echo '<br/>'.$row['album'].'';
echo"</td>"; 
$i++; 
} 
echo '</tr></table>';

Am I going about this the wrong way? It seemed like a no-brainer but isn't turning out that way.

  • 2 Contributors
  • 4 Replies
  • 87 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by CFROG
Member Avatar for almostbob

limit =1 ? if you only want one pic
or if pictures in albums are number, select only number 1 in each album
hard to code without knowing the data structure

Edited by almostbob because: n/a
Member Avatar for CFROG

LIMIT 1 gives me just that, 1 result whereas I need more than 1 if there is more than 1 album name. It selects distinct album just fine if that's all I tell it to choose, telling it to choose 'pic' as well seems to be throwing it off. I was trying to avoid the extra coding but I'm working on now that when the album is created the very first INSERT of that album will have an identifier such as '1' and subsequent INSERTS '0', this way I can tell is to choose just the albums with the identifier of '1'. It really seems like I shouldn't have to do that though, I just don't know why the 'pic' is throwing off the original query.

Member Avatar for CFROG

All of the pictures are named with a random number for example this would be the table:

PIC | ALBUM
----- | --------
1026895.jpg | TEST
6458788.jpg | TEST
6546547.jpg | ANOTHER_TEST

Since the pictures are distinct as well, it's including them in the result. All I want is DISTINCT album 'TEST' and 'ANOTHER_TEST' with the jpg that goes with them.

Edited by CFROG because: n/a
Member Avatar for CFROG

Well this did the trick for me

$sql="SELECT album, pic FROM user_profile_pics WHERE user_id='$uid' AND pic !='' AND def_al='1'";

I could've used DISTINCT if I could have figured out how to separate distinct albums from distinct pics. I'm sure it can be done, I just wasn't going about it the right way. I would like to know however, what would be the proper way?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.