Returning different users when using an inner join and orderby

[fawkman]

HI Guys

I have an issue, I would like to know if anyone can suggest how I can achieve what I want.

I have two tables, a user table and a media table. The media table has a FK, User_id , referenced to User.User_id.

Table structure User:

user_id
username
password
name
about_me
date_joined

Table structure for Media:

media_id
file_name
file_path
user_id

What I want to do is display the latest three users on the home page, as a sort of profile. SO I created the following.

$data = mysqli_query ($dbc,'SELECT media.file_name, users.username, users.creative_specialism
FROM media
INNER JOIN users
on(media.user_id = users.user_id)
ORDER BY date_joined
LIMIT 1');
if (mysqli_num_rows($data) > 0) {
while ($row = mysqli_fetch_array($data))
{
	
	echo '<div id="image1">';
	//echo '<img src="uploadedImages/'.$file_name[0].'.jpg" alt=""/>';
	echo'<p>'.	            $row['']									.'</p>';
	 echo'<p>'.				$row['username']					.'</p>';
	 echo'<p>'.				$row['creative_specialism']					.'</p>';
	echo '</div>';
}
}
else {
	echo'<p>there are no featured artists</p>';
}

I did have it set to Limit 3, but this code only outputs data for one user. Is there a clause I can use that will help me to display three different usernames?? Or will I have to do this Select Statement differently and run it three times.

Thanks very much for your time. I appreciate any help.

kind regards

[nav33n]

Hi, You don't have to run the select query thrice. In my opinion, this should work.

SELECT media.file_name, users.username, users.creative_specialism
FROM media, users
WHERE media.user_id = users.user_id 
ORDER BY date_joined DESC
LIMIT 3

[fawkman]

HI nav33n, thanks for your response. Unfortunately this code doesn't achieve what I want. It outputs, three records for the user who is at the bottom of the user table, i.e date_joined DESC.

I did look at running a distinc statement, as this is mean to return only different values. But I only want the USERNAME to be different, and I need to populate other columns, so I don't think it's possibe to run a SELECT with DISTINCT in this case. I could be wrong, or ther could be another way of doing it? ANy thoughts or opinions welcome please.

Thank you

[nav33n]

It outputs, three records for the user who is at the bottom of the user table, i.e date_joined DESC.

Actually, date_joined DESC should sort the records on date_joined column with the latest date on top. Can you try this query ?

SELECT media.file_name, users.username, users.creative_specialism
FROM media, users
WHERE media.user_id = users.user_id 
GROUP BY media.user_id 
ORDER BY users.date_joined DESC
LIMIT 3

GROUP BY will group all the records having common user_id in media table, more like, what DISTINCT would do.

[fawkman]

Yes this works perfectly. Thanks very much.

[nav33n]

Yay! You are welcome :)