How to group entries in table according to one column

[eluser]landitus[/eluser]
Hi! I have the following table:

id | album | photo
--------------------
1 | 1 | photo1.jpg
2 | 1 | photo2.jpg
3 | 2 | photo3.jpg

I would like to know how can I retrieve the table data and then loop throught the records so that for each album I can list the corresponding photos.

[eluser]cmgmyr[/eluser]

Code:

SELECT * FROM `albums` WHERE `album` = 1

http://ellislab.com/codeigniter/user-gui...eries.html
http://ellislab.com/codeigniter/user-gui...sults.html

[eluser]landitus[/eluser]
Thanks cmgmyr, but what if I don't know how many albums, and just need to loop through the whole table and get all albums with their corresponding photos?

[eluser]cmgmyr[/eluser]
Then you can do something like this:

Code:

SELECT DISTINCT `album` FROM `albums` ORDER BY `album` ASC

I normally set up an "albums" page then a "photos" page. Once they click on an album then they will get directed to the photos page, which shows all of the photos for that album.