Gallery and photos in one query?

[rshelby]

Is there a way to get a list of galleries and all of it's photos in 1 query?

Right now, I can only see doing that with 2 queries:

$gallery_photos = array();

//get a list of galleries
$galleries = mysql_query("SELECT * FROM galleries_table");

while ($row = mysql_fetch_assoc($galleries)
{
 $gallery_id = $row['gallery_id'];

 $gallery_photo_result = mysql_query("SELECT gallery_photo_connect_table.photo_id,
photo_table.photo_file_name
FROM gallery_photo_connect_table
LEFT JOIN photo_table ON photo_table.photo_id = gallery_photo_connect_table.photo_id
WHERE gallery_photo_connect_table = $gallery_id");

while ($photo_row = mysql_fetch_assoc($gallery_photo_result));
 {
  $file_name = $photo_row['photo_file_name'];

   $gallery_photos[] = array('file_name' => $file_name, 'gallery_id' =>  $gallery_id );
 }

}

That code is totally unchecked...

Anyways, it would be great to have 1 query that each row would have gallery id and all of it's photo ids and file names..

[johanafm]

Do it the exact same way as you joined the other two tables together.

SELECT ...
FROM galleries_table
LEFT JOIN gallery_photo_connect_table ON ...
LEFT JOIN photo_table ON ...
WHERE ...

[rshelby]

But that would result in too many lines. I think what I'm trying to do is for each line retrieve:

the fields from gallery_table (title of the gallery etc), and every photo id and name that is associated to that gallery... Possibly the photos would all be concated and comma spaced?

I think the query you sent would result in 1 line per photo, not 1 line per gallery?

[rshelby]

One more reply.

Even if the end result was 1 query that through each gallery and all of it's photos into an array would be perfect... I just can visualize it...

[johanafm]

You are correct in that it retrieves one row per photo. But since each row of photo data is retrieved together with data from its corresponding gallery, you allready have both gallery and photo data in each row.

For example, dealing with this in PHP by iterating over the result set just once

# ORDER BY very important for this to work
$result = $db->query(
'SELECT gallery.id AS gallery_id, photo related stuff, ...
FROM ...
JOINS ...

ORDER BY gallery_id
');

$last_gallery = false;
while ($row = $result->fetch())
{
	# if the current photo's gallery ($row['gallery_id']) is not the same as the
	# last photo's gallery ($last_gallery), output a new header (or new table row or
	# whatever way you use to group things
	if ($last_gallery != $row['gallery_id'])
	{
		# additionally, if $last_gallery !== false, this isn't the first iteration
		# and we have to close div, table row or some such from before
		if ($last_gallery !== false)
			echo '</div>';

	echo '<h3>'.$row['gallery_name'].'</h3>';

	$last_gallery = $row['gallery_id'];
}

echo '<img src="'.$row['photo_url'].'" alt="" width="'.$row['w'].'" height="'.$row['h'].'" />';
}

You can choose to either use aggregate functions and group by clause to create one row per gallery, or create your gallery array containing photo arrays in PHP.

Doing it in PHP but iterating over the result set twice, thus being less efficient than the above.

# ORDER BY gallery_id is not needed for this approach
$result = $db->query(
'SELECT gallery.id AS gallery_id, photo related stuff, ...
FROM ...
JOINS ...
');

$galleries = array();
while ($row = $result->fetch())
{
	# $galleries will be an array of gallery arrays, indexed by gallery id
	$galleries[$row['id']][] = $row;
}

foreach ($galleries as $gallery_id => $photo)
{
	# in reality, each $photo obviously contain all gallery data as well,
	# such as gallery id ($photo['gallery_id'] is the same as $gallery_id here)

echo 'gallery name etc';
foreach ($photo as $p)
{
	echo 'img element etc';
}
}

And doing it directly in your SQL query

SELECT gallery.id AS gallery_id, gallery.name as gallery_name, GROUP_CONCAT(photo_url SEPARATOR ', ') AS photos
FROM ...
JOINS

GROUP BY gallery_id, gallery_name

But, I'm curious as to why you want to avoid having one row per photo? How else will you display each separate photo in a gallery? Or are you going to explode your comma separated string of photos into an array? If so, then you are doing double work, first having the DB concatenate these values into one string, and then having your PHP scritp undo this work by exploding it back into an array.