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]

[scoutt]

where is the username coming from that is watching the topic?

[scoutt]

well I would call the watcher another variable. that would make things so much easier. The variable username will only and can only be one, so what ever you do, it will have to be different than the username.

[rjlohan]

I'm not sure what you mean... the watcher is identified by a record ID in the topic_watch table (tw.user_id). This ID identifies a user record in the users table (u.user_id)...

Also, the poster is identified by a record ID (p.poster_id) in the posts table, which also links to a user record in the users table...

I can't really call anything something different...

[scoutt]

well when the info is sent and the variable username is defined as the username of the poster then you won't be able to define the username as the watcher would ya? either way you will have to get one or the other, but not both

[rjlohan]

That's why I made the second (top) query. To get the username of the poster separately from the other query.

But, I was having trouble with JOINs.

I would expect something like:

$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 INNER JOIN (" . POSTS_TABLE . " p INNER JOIN " . USERS_TABLE . " u ON  
        u.user_id = p.poster_id)
        ON p.post_id = t.topic_last_post_id 
        WHERE t.topic_id = $topic_id";

But, no luck...

[scoutt]

why use join? also when you query for a variable like username you only get one. so lets say username = john, and john is the poster, how can you query for the watcher if username = john?

try this.

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

[rjlohan]

Originally posted by scoutt
why use join? also when you query for a variable like username you only get one. so lets say username = john, and john is the poster, how can you query for the watcher if username = john?

try this.

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

That's exactly what I do have...
It doesn't work.

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

JOINs are the correct way of querying related tables. That's why I want to use JOINs...

I don't follow your question about John there, either....
If John posts, then I want to get 'John' from the users table, store it in a variable. Then, the second query is completely unrelated, and just gets the username, email, etc of all users watching a topic. The username gotten by the 2nd query is not meant to be the poster.

Really, all I want to do, is make the 1st query do what I've mentioned.

[scoutt]

oh sorry about that. didn't even realize it.

so what errors are you getting on the join query?

and once you get the first query done and working the second query is goinig to get the same username the first query did. but worry about that later.

[rjlohan]

No errors, it just doesn't retrieve the username correctly. I can't remember what happened when I tried using JOINs, but I think there may have been some sort of error there. I don't know exactly, it just throws some sort of generic error...

[scoutt]

echo the query out and see if all the variables are what they are suppose to be. then try this

if ( !($result = $db->sql_query($sql)) )
{
echo mysql_error();
message_die(GENERAL_ERROR, 'Last topic poster returned invalid data.', '', __LINE__, __FILE__, $sql);
}

[rjlohan]

I think I've discovered my problem. It's getting the last but one poster in a thread. May just have to look at reorganising the function calls.

Cheers.