How to show GROUP BY data in a loop

MySQL Table: Images

id | Rating |
 1 | 3.0    |
 2 | 3.2    |
 3 | 4.7    |
 4 | 2.4    |
 5 | 2.4    |
 6 | 4.3    |
 7 | 2.4    |
 8 | 3.2    |

I’m basically trying to query the above table to get the data from those 2 fields (id and Rating) so that I can produce an ‘Image of the Month’ page.
I can query the database as so:

 $rows = @mysql_rows('SELECT ID FROM Image WHERE Live = 1 AND '.$whereDateLive.' ORDER BY Rating Desc LIMIT 15');

which gives me all the ID’s of the images. I then use a foreach loop to build my list of Images in descending order from the highest rated, with something like –

foreach($rows as $row)
    $img = new Image($row['ID'],true);
$content .= '<img src="/images/'.$img.'.jpg">';

This gives me a very basic list with no sorting (e.g 1st place, 2nd place etc). What I’m trying to do is GROUP the id’s by Rating, because as you can see from the table, Images ID 2 & 8 have the same rating (3.2) so would be Joint 3rd place. I can do this in theory with the following:

 $rows = @mysql_rows('SELECT ID FROM Image WHERE Live = 1 AND '.$whereDateLive.' GROUP BY Rating ORDER BY Rating Desc LIMIT 15');

Which gives me the data grouped how I need, However, the foreach loop then only shows 1 image of the group (the first in the group), so is obviously not the right thing to use. What I want to know is there a PHP function that will display the data by how it is grouped? or is something wrong in my code. Thank you.

Desired results, what I’m working towards, would be (as requested below) –

Image of the Month Page Title
First Place
[imgid 3]
Second Place
[imgid 6]
Third Place
[imgid 2] [imgid 8]
Best of the rest
(showing any image with a rating > 2.4)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s