PDA

View Full Version : Forum - mySQL JOIN



zachsformacs95
22 Jun 2012, 02:18 AM
I am trying to set up a forum but cannot quite understand how to use SQL JOINs properly. I have 4 tables involved - categories, threads, posts, and users. In any given category, I want to display the threads in that category sorted by the most recent post - I also want to receive the date and creator's username and ID of the most recent post and the first post (hence the date and creator's username and ID of the thread). My tables are set up like this:

categories
category_id - primary index
etc.

threads
thread_id - primary index
thread_category - index that corresponds with category_id
thread_first_post - index that corresponds with post_id
thread_last_post - index that also corresponds with post_id
etc.

posts
post_id - primary index
post_by - index that corresponds with user_id
post_date - datetime
etc.

users
user_id - primary index
user_username - string
etc.

Notice that there are two indexes on the threads table that correspond to post_id. I am unsure if this is legal, but I don't know what else I would do.

So once I have the category_id, I can select all the threads associated with that category. This works fine. However, I want to select the post_date, user_username and user_id that correspond with thread_first_post and thread_last_post.

The logic I am looking for is as follows:
go to the post with the post_id of thread_first_post
select the post_date
go to the user with the user_id of post_by
select the user_id and user_username
repeat with thread_last_post

As far as I can tell, the relationships are set up to where this should be possible, however I cannot figure out how to implement it.

Currently I have the following code:


// GET THREADS:
$threads = mysql_query('SELECT threads.thread_id,
threads.thread_title,
threads.thread_num_views,'

// I want the following three variables that correspond with both thread_first_post and thread_last_post (so 6 total variables).

'users.user_id,
users.user_username,
posts.post_date

FROM threads'

// Somehow I need to JOIN the following three sets of columns in order to accomplish this. I am assuming that it cannot be done in one statement.

'INNER JOIN posts ON threads.thread_first_post = posts.post_id
INNER JOIN posts ON threads.thread_last_post = posts.post_id
LEFT JOIN users ON posts.post_by = users.user_id

WHERE threads.thread_category = "' . $category_id . '"
ORDER BY threads.thread_last_post DESC');


I know that this is wrong (and ignore my splitting up the single quotes in the query - that is just so the comments would show in color), but I think it shows what I am trying to do.