Problem joining 2 tables

Question

I want to join 2 tables together but i cant get it to work.

These are the tables:
threads: id, title
posts: thread_id, message

$sql = mysql_query("SELECT threads.id, threads.title
                    FROM threads
                    JOIN posts ON posts.thread_id = threads.id
                    WHERE threads.id = ".intval($_GET['id']));


while ($post = mysql_fetch_assoc($sql))
{
    echo $post['title'];
    echo $post['message'];

}

It gets the title but not the message. I have one assigned to the thread.

Answer 1

Are there posts with a thread_id that matches $id? Because, if not, that would generate an empty result set.

Answer 2

Can you make it work in MySQL, without the PHP? I rely on phpMyAdmin to troubleshoot queries. Once I get it in MySQL more directly, phpMyAdmin has a "export SQL for PHP" feature.

Answer 3

You're not selecting posts.message. Change your SQL to this and it may work.

SELECT threads.id, threads.title, posts.message
FROM threads
JOIN posts ON posts.thread_id = threads.id
WHERE threads.id = $id

Answer 4

If you want to fetch the posts you might want to do it the other way

SELECT threads.id, threads.title, posts.message
FROM posts
JOIN threads ON threads.id = posts.thread_id
WHERE posts.thread_id = $id