selecting id and count(id) from table in mysql

Question

Hi guys,

I am trying to make a photo album system in php and mysql. I have this code for list all photo albums:

SELECT  albums.*, count(pictures.id) AS countpic 
FROM albums
LEFT JOIN pictures ON (albums.id=pictures.album_id)
GROUP BY albums.id 
ORDER BY albums.date DESC");

I want to list title of photo albums with photo thumbnails (thumb_id si written in table pictures) and also with number of all pictures assigned to each albums.

Dont you know how to select thumb_id and also number of all pictures in album by using one select?

Thanks

Answer 1

Does adding group_concat(pictures.thumb_id) to the select list do what you need?

Answer 2

This would work if for each album there is a single image with is_thumb=1.

SELECT
  albums.*, 
  count(pictures.id) AS countpic,
  group_concat(pictures.thumb_id) ids,
  group_concat(case when picture.is_thumb then pictures.thumb_id else '' end separator '') thumb
FROM albums
LEFT JOIN pictures ON (albums.id=pictures.album_id)
GROUP BY albums.id 
ORDER BY albums.date DESC");