[SOLVED] Messaging System-displaying user names

[sciencebear]

I am currently building a messaging system for my website. I already have a user system. In the user MySQL table, there is both a user id and a user name. However, the messages table is separate, and only stores the senders' and recipients' user ids, but in the inbox, I want to show the user names.

 

Right now, my code looks like this...

 

$result = mysql_query("SELECT * FROM pmessages WHERE recipid='$userid'") or die(mysql_error());

 

if (mysql_num_rows($result) == 0) {

echo "No messages!";

}

 

while($row = mysql_fetch_array($result)) {

echo $row['senderid'];

}

 

How can I show the user name in the different table where the user id is the same as the sender id?

[Maq]

You need to do a join, read more here.

[irkevin]

can u show how ur table is structured?

[sciencebear]

I don't see how I could use a join.

 

My db is like this:

 

Table:pmessages

-recipid

-senderid

-subject

-message

 

Table:user

-userid

-username

 

 

And of course the user table has other fields like password and email, but I'm only using userid and username in this query.

 

So instead of echoing senderid, I want it to echo the username where the userid is the same as the senderid.

[Maq]

I'm not sure what you should join on, maybe userid and recipid?  But anyway, like I said, you have to use a join if you're using two different tables.

 

Try:

 

SELECT * FROM users u LEFT JOIN pmessages p ON u.userid = p.recipid WHERE p.recipid = '$userid';

[sciencebear]

I'm not sure what you should join on, maybe userid and recipid?  But anyway, like I said, you have to use a join if you're using two different tables.

 

Try:

 

SELECT * FROM users u LEFT JOIN pmessages p ON u.userid = p.recipid WHERE p.recipid = '$userid';

 

But that would take the recipient's row from the user table... so I should do that except with senderid? Does that sound right?

[Maq]

You don't have a recipid in the users table...  Not from what your table structure tells me.

 

If you just want username use this:

 

$result = mysql_query("SELECT u.userid FROM users u LEFT JOIN pmessages p ON u.userid = p.recipid WHERE p.recipid = '$userid'") or die(mysql_error());

if (mysql_num_rows($result) > 0) {
   echo $row['username']
} else {
   echo "No Messages!";
}