PHP joins with two tables
I am just learning php as I go and I am completely lost here. I've never used a connection before, and I think I need it here, but I don't know. I didn't expect anyone to do it for me, but if you could just point me in the right direction, that would be awesome, I tried reading on connections, but there are 20 different methods and I just got lost.
Basically, I manually coded the forum and it works great but not efficient.
I have board_posts (for posts) and board_forums (for forums, categories as well as sections).
The part I am tinkering with 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 information for the last post in the table for board_forums, so say there is an "Off Topic" section, there I have a field for "forum_lastpost_username / userid "/ posttitle / posttime" which I update when the user posts, etc. But this is bad, I am trying to dynamically extract the whole thing and get rid of these fields.
Right now my request looks like this:
`SELECT * FROM board_forums WHERE forum_parent='$forum_id''
And then I have a material where I take information for this forum (name, description, etc.), and all the data for the last post is:
$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 he set up in board_posts is that if it's a thread, post_parentpost is NULL, if it's a response then this field has a thread id (first post of the topic). So I need to grab the last post_date post, see which user posted this, THEN, see if parentpost is NULL (if it is null, the last post is a new thread, so I can get all the title and user information there, but if it is not, I need to get the information (title, id) of the first post in this thread (which can be found by looking at what post_parentpost is, looking at that id and getting the title from it.
Is that the point? If yes, please help me :(
Any help is greatly appreciated !!!!
Updating board___forums whenever a post or response is inserted - in terms of performance - is not a bad idea. To display the index page, you only need to select data from one board_forums table - this is definitely much faster than selecting the second table to get the "latest information", even when using smart join.
source to share
You'd better update your statistics for every action, new post, post deletion, etc.
Other instances are unlikely to require updating the statistics (deleting the thread will update the forum to show another topic in the topic list).
Think about all the actions the user will take, in most cases you don't need to update the statistics, so getting on the fly is very inefficient and you are right to think so.
source to share
It looks like you've already done the right thing.
If you were to join, you would 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 this is that it gets you every post, which is not useful (and very slow). What you would like 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'
other than SQL it doesn't work. You can't order or restrict the connection (or do many other useful things like this), so you have to fetch each line and then scan them in code (which sucks).
In short, don't worry. Use connections for the actual case where you want to download all forums and all posts in one go.
source to share
A simple solution will lead to numerous requests, some optional as you have already discovered.
The classic approach to this is to cache the results and only fetch it from time to time. Cash doesn't have to live long; even two or three seconds on a busy site will make a difference.
De-normalizing the data into a table you are already reading will help. This approach allows you to figure out optional queries and can be a bit of a cheap win because it's just another update with the insert already in progress. But it does transfer some data integrity to the application.
As an aside, you might run into the recursive query problem with your threads. Relational databases do not store hierarchical data, which is good if you use a "simple" algorithm. The best way is what is sometimes called "set trees". Unfortunately this is a bit tricky for Google, so there are some links here .
source to share