CODEWITHSUNDEEP
phpsqlmysqlselectcount
Bajlo
Bajlo

Reputation: 1417

selecting id and count(id) from table in mysql

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

Upvotes: 1

Views: 1394

Answers (2)

ceteras
ceteras

Reputation: 3378

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");

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453278

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

Upvotes: 3

Related Questions