PHP join help with two tables

Question

Hi. I am just learning php as I go along, and I'm completely lost here. I've never really used join before, and I think I need to here, but I don't know. I'm not expecting anyone to do it for me but if you could just point me in the right direction it would be amazing, I've tried reading up on joins but there are like 20 different methods and I'm just lost.

Basically, I hand coded a forum, and it works fine but is not efficient.

I have board_posts (for posts) and board_forums (for forums, the categories as well as the sections).

The part I'm redoing is how I get the information for the last post for the index page. The way I set it up is that to avoid using joins, I have it store the info for latest post in the table for board_forums, so say there is a section called "Off Topic" there I would have a field for "forum_lastpost_username/userid/posttitle/posttime" which I woudl update when a user posts etc. But this is bad, I'm trying to grab it all dynamically and get rid of those fields.

Right now my query is just like:

`SELECT * FROM board_forums WHERE forum_parent='$forum_id''

And then I have the stuff where I grab the info for that forum (name, description, etc) and all the data for the last post is there:

 $last_thread_title = $forumrow["forum_lastpost_title"];
 $last_thread_time = $forumrow["forum_lastpost_time"];
 $lastpost_username = $forumrow["forum_lastpost_username"];
 $lastpost_threadid = $forumrow["forum_lastpost_threadid"];

But I need to get rid of that, and get it from board_posts. The way it's set up in board_posts is that if it's a thread, post_parentpost is NULL, if it's a reply, then that field has the id of the thread (first post of the topic). So, I need to grab the latest post_date, see which user posted that, THEN see if parentpost is NULL (if it's null then the last post is a new thread, so I can get all the info of the title and user there, but if it's not, then I need to get the info (title, id) of the first post in that thread (which can be found by seeing what post_parentpost is, looking up that ID and getting the title from it.

Does that make any sense? If so please help me out :(

Any help is greatly appreciated!!!!

Answer 1

Updating board___forums whenever a post or a reply is inserted is - regarding performance - not the worst idea. For displaying the index page you only have to select data from one table board_forums - this is definitely much faster than selecting a second table to get the "last posts' information", even when using a clever join.

Answer 2

You are better off just updating the stats on each action, New Post, Delete Post etc.

The other instances would not likely require any stats update (deletion of a thread would trigger a forum update, to show one less topic in the topic count).

Think about all the actions the user would do, in most cases, you dont need to update any stats, therefore, getting the counts on the fly is very inefficient and you are right to think so.

Answer 3

The simple solution will result in numerous queries, some optional, as you're already discovered.

The classic approach to this is to cache the results, and only retrieve it once in a while. The cache doesn't have to live long; even two or three seconds on a busy site will make a significant difference.

De-normalizing the data into a table you're already reading anyway will help. This approach saves you figuring out optional queries and can be a bit of a cheap win because it's just one more update when an insert is already happening. But it shifts some data integrity to the application.

As an aside, you might be running into the recursive-query problem with your threads. Relational databases do not store heirarchical data all that well if you use a "simple" algorithim. A better way is something sometimes called 'set trees'. It's a bit hard to Google, unfortunately, so here are some links.

Answer 4

It looks like you've already done the right thing.

If you were to join, you'd do it like this:

SELECT * FROM board_forums
JOIN board_posts ON board_posts.forum_id = board_forums.id
WHERE forum_parent = '$forum_id'

The problem with that, is that it gets you every post, which is not useful (and very slow). What you would want to do is something like this

SELECT * FROM board_forums
JOIN board_posts ON board_posts.forum_id = board_forums.id ORDER BY board_posts.id desc LIMIT 1
WHERE forum_parent = '$forum_id'

except SQL doesn't work like that. You can't order or limit on a join (or do many other useful things like that), so you have to fetch every row and then scan them in code (which sucks).

In short, don't worry. Use joins for the actual case where you do want to load all forums and all posts in one hit.