[RESOLVED] Grouping together but still accessing all rows? - HTML Forums

robkar97 · 09-10-2009, 01:44 AM

I've got a database of photos in an online db and would like to offer the users the possibility to group photos together that were shot within the same 5 seconds (meaning they most likely show the same subject).

However, these stacked, hidden subsequent shots that would initially be hidden should be "expandable" by clicking an icon of the "master photo" that acts as a cover for the stack.

But when I use the "GROUP BY" feature, all these "stacked" photos aren't fetched at all from the db at all, and I dont' think I can easily do it afterwards using PHP by fetching all photos since I would also like the user to be able to sort by ANY column in the db (not just date!), for example ISO or aperture, while having this "grouping feature" active...

Will I need to do multiple queries (say one addtional for each stacked photo) - or is there a smarter way?

Rob

cmetz1977 · 09-11-2009, 06:40 PM

1 ) Run your query, don't group, just order:

$query = mysql_query( "SELECT `photoid`, `phototime` FROM `album` WHERE `userid` = '$userid' ORDER BY `phototime` ASC", $connection_mysql );

2) As you iterate through your results, compare the times and do a subroutine if subsequent photos fall within your 5 second buffer

PHP Code:


			
if( $query && mysql_num_rows( $query ) )

{

  print( "<div class='photogroup'>" );



  while( $photo = mysql_fetch_assoc( $query ) )

  {

    if( !isset( $lastphotogrouptimestart ) || ( $photo[ 'phototime' ] - $lastphotogrouptimestart ) > 5 seconds )

    //I'll let you figure out how to do the math there since I don't know how your time fields are configured

    {

      if( isset( $lastphotogrouptimestart ) ) //print a separator if this isn't the first group of photos

        print( "</div><div class='photogroup'>\n" );

      print( "<img src='{$photo[ 'photoid' ]}' class='mainthumbnail'/>\n" );

    }

    else print( "<img src='{$photo[ 'photoid' ]}' class='subsequentthumbnail'/>\n" );





      $lastphotogrouptimestart = $photo[ 'phototime' ];

  }



  print( "</div>" );

}

else print( "<p>This user doesn't have any photos</p>" );

Clearly, this won't compile in PHP so just use my suggestions as a guideline. I'm sure you'll have a ton of your own output adjustments to make as well. Godspeed!