[Solved] How to view first image in list per user as its highlight Profile

Question

you are looping on all results of your query, so of course you will get all images. Your query is to generic.

Now I assume your table is something like this:
– id, int, auto-increment, primary key
– uid, varchar, foreing key from a user table, not null
– file_name, varchar, not-null
– upload_time, datetime, not-null

Modify it to get only the 1 image you want:

SELECT file_name
FROM member_images
WHERE uid = <SOME UID>
ORDER BY upload_time DESC
LIMIT 1

DESC will order the results by latest first. So LIMIT 1 will get only the first image, which is the latest.

Now if you want to get the latest image for every user in one go, use:

SELECT *
FROM member_images AS m
INNER JOIN (SELECT uid, max(upload_time) as latest
            FROM member_images
            GROUP BY uid) AS r
            ON m.upload_time = r.latest and m.uid = r.uid
ORDER BY upload_time DESC

BTW I did not know how to do this either, so I searched the web, and I found
many pages on StackOverflow on how to do it. Hence the downvotes. Folks here
expect you to do your own research.

Nic

Accepted Answer