Replace user ID with Name

[ajloun]

Hello

I have Simple Script , Messages between Members .

i have two tabels on has users name and id , email.. another tabel for messages has message id , subject, from, to, text

Now the code Below Shows the messages between the users . the code Shows Message ID , From, To , Subject, Text .

But where "from" and "to" replaced with user ID , and i want it to be replaced with the User Name .. from the other Tabel ..

like if user id =100 get user name from table users where ID=100

this the code i use

$result = mysql_query("SELECT * FROM ".$DB->prefix("msgs")." LIMIT 10");
$rows = mysql_num_rows($result);
if($rows > 0){

echo "<table width='700' border='1'>
<tr>
<td>";
echo "<table border='1' width='100%'>";
echo "<tr>";
echo "<td width='15%'><b>ID</b></td> ";
echo " <td width='15%'><b>From</b></td><td width='15%'><b>To</b> ";
echo " <td width='15%'><b><p>Subject</p></b>";
echo "</td><td width='45%'><b>Message</b></td>" ;
echo "</tr>";

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

echo "<tr><td width='15%'>".$row['id']."</td>";
echo "<td width='15%'>".$row['from']."</td> ";
echo "<td width='15%'>".$row['to']."</td> ";
echo "<td width='15%'>".$row['subject']."</td> ";
echo "<td width='45%'>".$row['msg']."</td> ";
}
echo "</table>";
echo "</td> ";
echo " </tr> ";
echo " </table>";
}

[kareems]

2 options:

1. do an INNER JOIN on your messages table and your users table, on FROM and TO (you'd have to do multiple JOIN's - kinda messy), so you have the user info alongside each message.

2. within the while loop:

SELECT name FROM users WHERE id=$row["from"] limit 1
SELECT name FROM users WHERE id=$row["to"] limit 1

[ajloun]

thx

i didt get that . can you do it in php example ?

[Fou-Lu]

Inner joins are not at all messy. I have no idea the names of you're tables or fields, so I'll just assume its user and the fields userID and username:

SELECT m.id, m.subject, m.msg, r.username AS to, s.username AS from
FROM msgs m
INNER JOIN r.username ON r.userID = m.to
INNER JOIN s.username ON s.userID = m.from


This way you shouldn't have to replace any current code since the to and from should be replaced by the corresponding usernames.


BTW, this is a SQL issue, so I'll move this to the MySQL forum.

[ajloun]

Fou . Thank you

Couldt Realy Understand your code , as i never even heard of Inner joins ..

My tables name 1- users 2- msgs

Tabel users has , username , userid

Tabel msgs has , Userid,subject,txt

So userid is Shared in both ,
Now with regards to my Code above , how to edit it with your Code ?

i did not know wht to do with ur code..

[Fou-Lu]

You cannot, msgs indicates that it has no way to track who a message is from assuming that its userid is related to the receiver not the sender.

[ajloun]

This wht is in my Mind ..

Tabel 1 - users

UserID--------Username
5 -----------admin
10-----------john

Tabel 2 - Msgs

From_ID----_ID -----Subject ------TxT
5 --------- 8 ----- Hello --- Hi whts Up ?

we Need to Replace from , to other words 5 and 8 with the actual names .

Now

From_id=UserID
To_id =UserID

All i need is when From_id equal 5 or 10 or whtever is the id of the sender , to get the equals Name of that id from the Username Filed in tabel users

So if the sender id is 10 then lets go to tabel users and check UserID 10 and get the name equal that id.

[Fou-Lu]

Than use this query:

SELECT m.Subject, m.TxT, r.Username AS to, s.Username AS from
FROM Msgs m
INNER JOIN Users r ON r.userID = m.To_ID
INNER JOIN Users s ON s.userID = m.From_ID


What the heck was I thinking with the r.Username in the join? Lol.
Just replace you're current query, but you appear to be using a $DB->prefix, so wrap you're table names with that first.

[ajloun]

Fou-Lu

I'm sorry i do not think u understand me Good , due to my bad english .

The thing is i'm not tryin to do it by just insert the query to the sql .. No I wanted it do thru Php .

I know how to insert query to sql and do it with easier than your code .
SELECT * FROM TABLENAME1,TABLENAME2 WHERE TABLENAME1.id = TABLENAME2.uid

EH, Forger it , i dont think i could excplain it better than previously.

[Fou-Lu]

Just replace you're query:

$result = mysql_query("
SELECT m.Subject, m.TxT, r.Username AS to, s.Username AS `from`
FROM " . $DB->prefix('Msgs') . " m
INNER JOIN " . $DB->prefix('Users') . " r ON r.userID = m.To_ID
INNER JOIN " . $DB->prefix('Users') . " s ON s.userID = m.From_ID
LIMIT 10");


Also needed to backtick the `from` since its reserved. Man, I am getting rusty with sql.