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

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

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

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.

Yes this works perfectly. Thanks very much.

Yay! You are welcome :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.