Forum Loop?

[Andrew777]

Hi Guys,

I should know how to do this but for some reason my code isn't working or I just am missing something...

 

I am setting up a simple forum and posts are stored in one table with the following fields:

 

auto_id -------- primary, auto_increment

start -------- stores a 0 or 1, to show that the post is the first in the thread. (1 for the starting post, 0 for other posts in the thread)

blog_id ---------- the threads id number, posts that are repllies to the starting post have the same blog_id number.

bid ---------- forum title

title ----------- post title

post_by --------- user's profile name

mem_id ---------user's member id

date_time --------- the date and time

querydate --------- unix timestamp

visible ------------ to disable visibility of a specific post

 

My Problem is that I can list posts in certain forum but when someone creates another post in an older thread, I want that thread to show up at the top of the list of threads, but my code isn't doing it....

 

Example:

A)

Who bought the new iPhone 4S?

    Started by: John - Date: February 1, 2011

What did you think of the Green Lantern Movie?

    Started by: Mary - Date: January 1, 2011

 

**then if someone adds a post to "Going to see Transformers tonight." that thread should show up above the "Who bought new iPhone 4S?" thread, just basically like any forum page...

 

B)

What did you think of the Green Lantern Movie? (New Post Added on: March 1, 2011 by Scott)

    Started by: Mary - Date: January 1, 2011

Who bought the new iPhone 4S?

    Started by: John - Date: February 1, 2011

 

Here is my while loop code so far:

$sqlf="SELECT * FROM blogs WHERE bid='$brd' AND visible='yes' GROUP BY blog_id ORDER BY querydate DESC, auto_id DESC ";
$rsf=mysql_query($sqlf);
..... Some HTML and CSS
while($rowf=mysql_fetch_array($rsf)) {
..... Some more HTML/CSS and displaying the variables for the rows of threads.
}

 

Thanks for any help...

 

 

[creata.physics]

You are also ordering by auto_id, remove that completely.  The way you have it is that all posts will be ordered by their id, that's fine until somebody posts a reply to a thread, the order of it will not update ( go to top of list ). 

[kney]

You could add a field updateTime to ur table and fill it with the date/time when the last post is posted.

Then you order by that instead of the original date

 

So first time you post a thread it will be

 

date_time = January 1, 2011

update_time = January 1, 2011

 

And when a new post is added

 

date_time = January 1, 2011

update_time = March 3, 2011

 

[Andrew777]

"creata.physics", I removed the auto_id out of the order completely, but my thread list still comes up ordered by the date_time of the original starting thread post. So the Select statement groups the threads but still only shows them on the page by the original threads date_time. It doesn't push the thread with the a newer reply to the top of the display list.

 

In response to "kney", yes any new post has the new posting date, so a reply will have the new post's date_time.

No need to repeat the original threads date_time in the new post, since the reply will not need to use that date_time.

 

[creata.physics]

I'm kinda sure on how you're going about it.

 

I do know we are taking extra steps here by you having a field with an already formatted date and a unix timestamp, you should always use timestamps for all date entry fields by the way.

 

With that said, we need to change and understand the logic of what you're trying to do.

 

You have a table for forum threads and forum posts i'm assuming.

 

Both fields, let's say forum_threads and forum_replies have a field in their table with a date that corresponds to the time it was posted.

 

So let's say both tables have the field 'posted_date' - This is just to see what time the post was made. 

 

NOW, you should make a field in the forum_threads table called 'lastpost_time' - this will contain the time a reply is posted.  Why do it this way? Well mainly because we shouldn't have to join or check forum_replies just to find out how threads should be ordered.

 

So now you have 'lastpost_time' in your 'forum_threads' table, the default value for this field, will also be the same EXACT time as when the topic was posted. BUT, if a reply is posted to that thread, the lastpost_time field's timestamp will update to when the most recent reply was made.

 

With this logic and structure, all you would have to do is make a simple query like this to output threads by activity.

mysql_query('select * from forum_threads order by lastpost_time desc');

 

That makes things a lot more simple and easier to understand.  This is also how most popular forum systems like smf, phpbb and ipb does it.

 

Good luck, any further questions feel free to ask.

[Andrew777]

Thanks for your response.

I'm just wondering if it's absolutely necessary to have two tables?

I can understand having two fields "original_postdate", and "mostrecent_postdate"....

But is there no way to do the select statement so that it groups them by id and then displays the order by most recent post date within one table?

 

 

[Andrew777]

Never mind, I figured it out by using:

 

"SELECT DISTINCT(blog_id) AS blog_id, MAX((auto_id) as auto_id .....etc...... GROUP BY blog_id ORDER BY auto_id DESC"

 

That gave me the result set I was looking for.