Help With Forum

ldtiw

Ive created a simple forum, but cant put the last 5 recent posts on my home page.

Im not sure what im supposed to be grouping, or selecting as DISTINCT but i cant get it to work. (also need a count of replies)

Heres what ive tried:

SELECT DISTINCT forum_posts.forum_id, forum_posts.subject, forum_posts.thread_id
FROM forum_posts
GROUP BY thread_id
ORDER BY forum_posts.date_added DESC

This is my table:

post_id
forum_id // Id of the forum
thread_id
subject // Post Title
post // Post Body
usr // User who added the post
date_added // the date it was added (timestamp)
notify // If subscribed an email gets sent when a reply is added

If post is the new thread:

post_id | forum_id | thread_id
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3

If a post is a reply:

post_id | forum_id | thread_id
1 | 1 | 1
2 | 1 | 1
3 | 1 | 1

So if there are 30 Threads, and someone adds a reply to a post that would have been the 10th record shown if it wasnt limited to 5. The Main thread name of that recent post goes to the top of the list. Or if its a new post that goes to the top!

Hope thats clear. Not sure how to explain it.

Thanks In Advance,
Danny

halojoy

I would consider an alternative database.
With easier understandable syntax. A more distinkt syntax from user point of view

There are many good ways to use only PHP fwrite fread and your own "data.txt" file.
I have used flat file data base storage in whatever application
With great success and greater SPEED.
PHP should not have to rely on an outside program (MSSQL or you know what ....)
for operations that PHP can do better itself!

/halojoy
knows what he is talking about

Namadoor

What kind of syntax would you use for getting data from flat files? For example, how would you do what ldtiw is doing? And is flat files noticably faster? I have used MySQL for years and I have never noticed performance or stress issues. I don't get very much traffic, and probably no more than 5 users at a time, so will it start to slow down with more users? I don't think that SQL syntax is that hard to understand, and I find it an easy and relable way to store data.

ldtiw

Ok,

Ive changed the way it works a litte, and preffer it. BUT, how can i get the last post info??? And also, how would i sort the table showing new posts/replys at the top?

This is the SQL i have for getting the forum cat info:

SELECT COUNT(forum_posts.post_id) as total_posts, forum_threads.thread_id, forum_threads.`timestamp`, forum_posts.subject, forum_posts.`timestamp`, members.m_username
FROM forum_threads LEFT JOIN forum_posts ON forum_threads.thread_id = forum_posts.thread_id LEFT JOIN members ON forum_posts.user = members.m_id
WHERE forum_threads.forum_id = colname
GROUP BY forum_threads.thread_id

And im trying to pin another SQL into this but dont know how to:

SELECT forum_posts.`timestamp` as lastpost_date, members.m_username as lastpost_user, forum_threads.thread_id
FROM forum_threads LEFT JOIN forum_posts ON forum_threads.thread_id = forum_posts.thread_id LEFT JOIN members ON forum_posts.user = members.m_id
WHERE forum_threads.thread_id = 'colname'
ORDER BY forum_posts.`timestamp` DESC

Then doing this to print it out:

                <?php do { ?>
                <tr>
                    <td class="botbb"><a href="view_post.php?thread_id=<?php echo $row_posts['thread_id']; ?>"><?php echo $row_posts['subject']; ?></a><br>Started By: <?php echo $row_posts['m_username']; ?></td>
                    <td align="center" class="botbb"><?php echo $row_posts['total_posts']-1; ?></td>
                    <td align="right" nowrap class="botbb"><br>
                      <?php echo $row_lastPost['lastpost_user']; ?></td>
                </tr>
                <?php } while ($row_posts = mysql_fetch_assoc($posts)); ?>

But cant get it to display the correct last poster. Has anyone an idea how i would do this??

Thanks,
Danny