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...

PHP Code:

$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

PHP Code:

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

PHP Code:

$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";