Building a statement problem: two tables, 'WHERE' clause issue.

[jeskel]

Hi,

Sorry, it was kinda hard to find an appropriate title for this thread :)

It might seem basic but I can't figure out how to write this statement.

Here is the deal: users will post comments on various texts. When a user post a comment, I record the following info in the 'messages' table:

pk (auto increment), textID (the ID of the text being commented), title, message, posterID (the ID of the member posting his/her comment) and finally messaDate.

No problem here.

When I want to display the messages related to a specific text, I get the textID in question using $textID = $_GET['textID']. Then I will need to build my request in order to display the comments recorded about the textID in question. (WHERE messages.textID = $textID). Here, I am also fine.

Here comes the pain for me.

Since I recorded the ID of the poster (not his name) and that I want to display the name of the persons who posted the comments, I will have to get their username using the pk value they got when registering, stored in another table. I hope I am clear.

Here is some code that will probably help you understand my problem:



$textID = $_GET['text'];

$sql = @mysql_query("SELECT messages.textID, messages.title, messages.message, messages.posterID, messages.messageDate, users.pk, users.username FROM messages WHERE messages.textID = '$textID' AND messages.posterID = users.pk ORDER BY messages.messageDate DESC");
}


Then I display all the data retrieved using a loop:

while ( $display = mysql_fetch_array($sql) ) {
echo('<p>' . $display['messageTitle'] . ' ' . $display['message] . ' ' . $display[username] . '</p>');
}

That's the idea but it doesn't work, as you may guess :)

Thanks in advance.

[raf]

should we also guess what doesn't work ? Do you get an error or so? did you try the query out in phpmyadmin or so? did you check for reserved words ?

[jeskel]

argh... shame on that thread :o

I could figure out by myself what I had to do :)

I didn't post the error message because there was none, I was at the point of trying to build the statement and I thought it was harder than expected. I was close to making it work with the sql line posted above.

Sorry about that. :)