Stuck on a query here...

[rjlohan]

I'm a bit stuck on the following. The bold section is the original script. The other, is something I included... the problem is, that I need to get u.username twice in one query. I need to get the user who last posted to the topic, which is sort of what I'm trying at the top query, and also the username of a user watching the topic, in the second query.

I can't work out how to use JOINs in PHP - I don't know if it's any different, or what. It's a MySQL db.

Wanna help solve my conundrum?

//Get last poster's username
$sql = "SELECT u.user_id, u.username, t.topic_id, t.topic_last_post_id, p.post_id, p.poster_id
    FROM " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p, " . USERS_TABLE . " u  
        WHERE u.user_id = p.poster_id 
        AND p.post_id = t.topic_last_post_id 
        AND t.topic_id = $topic_id";
if ( !($result = $db->sql_query($sql)) )
{
    message_die(GENERAL_ERROR, 'Last topic poster returned invalid data.', '', __LINE__, __FILE__, $sql);
}
if ( $row = $db->sql_fetchrow($result) ) {
    $topic_poster = $row['username'];
} else {
    $topic_poster = 'Someone';
}
                
                
 
[b]$sql = "SELECT u.user_id, u.username, u.user_email, u.user_lang, t.topic_title  
    FROM " . TOPICS_WATCH_TABLE . " tw, " . TOPICS_TABLE . " t, " . USERS_TABLE . " u 
    WHERE tw.topic_id = $topic_id 
    AND tw.user_id NOT IN (" . $userdata['user_id'] . ", " . ANONYMOUS . $user_id_sql . " ) 
    AND tw.notify_status = " . TOPIC_WATCH_UN_NOTIFIED . " 
    AND t.topic_id = tw.topic_id 
    AND u.user_id = tw.user_id";
if ( !($result = $db->sql_query($sql)) )
{
    message_die(GENERAL_ERROR, 'Could not obtain list of topic watchers', '', __LINE__, __FILE__, $sql);
}[/b]