SQL Select user
I have the following query that selects a friend request script that shows the profile pic, the hometown, the userid, and the full name of a user that has requested to be friends with a certain user.
SELECT a.hometown, a.first_name, a.uid, a.last_name, b.friend_one, b.friend_two, b.friend_request_id, p.thumbnail FROM users a, friend_requester b, profile_pics p WHERE a.uid = b.friend_one AND b.friend_two = $uid AND p.uid_fk = $uid ORDER BY b.created DESC LIMIT 5
My problem is that instead of returning 1 user for each query found in the friend_requester table, I return 3 instances for example of the same user with different profile pictures. So if a user has uploaded 3 profile pictures which will be stored as
id=1, profile_pic=profile_pic1.jpg... id=1 profile_pic=profile_pic2.jpg... id=1 profile_pic=profile_pic3.jpg...
I get 3 different boxes of the same user asking to be friends with the logged-in user.
As Hituptony mentioned, I would want to select the latest inserted picture which would be from the 'profile_pics' table in the column 'created'.
GROUP BY Uid_FK, get the max created and INNER join back to the picture table (done in my subquery called "MaxPic")
SELECT a.hometown, a.first_name, a.uid, a.last_name, b.friend_one, b.friend_two, b.friend_request_id, p.thumbnail FROM users a INNER JOIN friend_requester b ON b.friend_one = a.uid INNER JOIN profile_pics p ON p.uid_fk = b.frind_two INNER JOIN ( SELECT uid_FK, MAX(Created) AS Created FROM Profile_Pics GROUP BY uid_FK ) MaxPic ON MaxPic.UID_FK = p.UID_FK AND MaxPic.Created = p.Created WHERE p.uid_fk = $uid
So which picture do you want? The picture should have a timestamp related to it, maybe you can pull the date of the picture and get the one closest to NOW() or curdate() in order to display the most recent picture. ^^ I dont think you can group by order by, but you should SELECT b.created, so you can see the order from which it is retrieving the pictures.
If you can add a new derived table to your join, you can do this:
SELECT a.hometown, a.first_name, a.uid, a.last_name, b.friend_one, b.friend_two, b.friend_request_id, p.thumbnail FROM users a, friend_requester b, (Select MAX(Created) as Created, uid_fk from profile_pics) p_date, profile_pics p WHERE a.uid = b.friend_one AND b.friend_two = $uid AND p.uid_fk = $uid AND p_date.Created=p.Created And p_date.uid_fk=p.uid_fk ORDER BY b.created DESC LIMIT 5
Alternately, you may want to have 'flag' column in your pic table that contains a 1 if the picture is the most recent, and a 0 otherwise, - this type of problem seems like it would come up a lot, and as the database grows, and there are mor old pictures, you will get performance problems if you need to check every entry each time. With a flag, you can index for performance much more easily. Also, this query would simply need an added: where flag=1. Each time you insert a new picture, you make the flag 1, and the flag of all that users other pics you set to 0.