Join question I think??

[Jalrock]

the two tables are "reputation" & "user".

I get the info from "reputation" but want to translate the userid in to a username via the user table.

the script looks like this...

$sql = "(SELECT * FROM reputation ORDER BY $order)";
$result = mysql_query($sql);

while($myrow = mysql_fetch_array($result)) {
$postid = $myrow["postid"];
$userid = $myrow["userid"];
$reputation = $myrow["reputation"];
$whoadded = $myrow["whoadded"];
$reason = $myrow["reason"];
$dateline = $myrow["dateline"];



echo "<tr><td><a href=\"http://www.twins.combinedwealth.com/forum/showthread.php?p=$postid\">$postid</a></td><td><a href=\"http://www.twins.combinedwealth.com/forum/member.php?u=$userid\">$username</a><td>$reputation</td>\n<td><a href=\"http://www.twins.combinedwealth.com/forum/member.php?u=$whoadded\">$whoadded</a></td>\n<td width=\"350\">$reason .</td><td>$dateline</td>\n</tr>";
}


any help appreciated.

[Dean C]

This is not a nice way of doing it - firstly if this is for vB make use of the built in class.

mysql_query(); = $DB_site->query();
mysql_fetch_array(); = $DB_site->fetch_array();

And so on...

Secondly use templates as it's a lot cleaner method

Thirdly for your join :)


SELECT reputation.*,user.* FROM reputation LEFT JOIN user ON user.userid=reputation.userid ORDER BY $order


-------

You may want to try and limit it too as if you have 100+ users as soon as they all have their reputations you're going to be pulling 100+ users at a time.

[Andreas]

Try

$sql = "SELECT reputation.*,user.username FROM " . TABLE_PREFIX . "reputation AS reputation LEFT JOIN " . TABLE_PREFIX . "user AS user ON (user.userid=reputation.userid) ORDER BY $order";