CODEWITHSUNDEEP
phpmysqlselect
user3163801
user3163801

Reputation:

SELECT Data from Two Separate Tables

I have two separate tables. I need to SELECT the avatar from the user table, WHERE the username equals from on the comments table. I am trying to create a comment system that displays the user's avatar next to their message.

Comments - ID|Username|From|Timestamp|Message
User - ID|Username|Avatar

$fetchto=mysql_fetch_object(mysql_query("SELECT * FROM user WHERE username='$variable'"));

I think I could display the URL to the avatar using $fetchto->avatar if I had a variable that would pull the avatar of the member making the comment from the user table.

Upvotes: 0

Views: 106

Answers (2)

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

First off your database isn't properly normalized. The comments should refer to the User by UserId, not by Username. Once you've fixed that:

select * from Comments c
join User u on u.ID = c.UserId

Until then:

select * from Comments c
join User u on u.UserName = c.UserName

Also, please stop using the mysql_ family of functions - they're deprecated.

Upvotes: 1

Derek Nutile
Derek Nutile

Reputation: 211

Your query needs to have a simple join, something like this:

SELECT c.*, u.avatar
FROM comments AS c
JOIN user AS u ON c.username = a.username

Upvotes: 0

Related Questions