[SOLVED] Help with one feature of a forum

[geoldr]

Hello guys, I have programmed a PHP forum, you can check it out here www.bayareahangout.com

 

Here is my question. Say somebody replied to a topic, how do I make that topic in the list go to the very top of the page?

 

My forum uses PHP and MySQL. If you guys need me to post SQL info or PHP code I would be happy to do so, because I cannot figure out how to do this!!

[cooldude832]

assuming you sql structure is similar to

 

Boards Table

 

Threads Table

 

Post Table

 

you would need to organize your query to generate each boards thread something like

SELECT
Threads.ThreadID, Threads.Name, MAX(Post.Date) as Sorter,
Boards.BoardID
FROM `Boards`
LEFT JOIN `Post` on (Post.ThreadID = Threads.ThreadID)
LEFT JOIN `Threads` on (Boards.BoardID = Threads.BoardID)
Where Boards.BoardID = $boardid
GROUP BY Threads.ThreadID
ORDER BY Sorter

as an example

[geoldr]

Well, the core of my forum is based off of this.

 

http://www.phpeasystep.com/workshopview.php?id=12

 

Because I had no idea where to start. The SQL setup is basically the same.

[geoldr]

Here is my SQL setup.

Table: forum_category (this is the main category in which you click a forum)

Table: forum_forum (this is the stuff you see under the Category. IE: Lounge, Off-topi, news, etc

Table: forum_topic (this is the main topics)

Table: forum_post (this is the posts)

Table setups

forum_category / forum_forum are't important, if they are ask me and I will post.

 

forum_topic:

id

forum_id

topic

detail

name

datetime

view

reply

poster_id

 

forum_post:

topic_id

post_id

post_name (this is the posters name)

post_post (this is the main post)

post_datetime

poster_id (this is the user's ID)

 

So say I reply to a topic. How do I make it go to the top of the page??

I don't really understand the LEFT JOIN SQL syntax, and how I would integrate it into my code.

 

Thanks for all the help so far!

 

[cooldude832]

rework the query  I wrote so it matches your tables' structure and then try it in phpmyadmin and report back any errors.

[geoldr]

Here is the problem it is giving.

 

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN `forum_post` on (forum_post.a_id = forum_topic.id)

LEFT JOIN `forum_to' at line 5 "

[dannyb785]

For my message boards, each row in my 'Topic' table has a 'last_post" column which gives the date(in strtotime() format) of the last post. So when someone makes a post, I update the 'Topic' row with the new value. Then when I'm showing topics in whatever section, I do "ORDER BY last_post DESC"

[geoldr]

Ah, actually I have just added a last_post to my topic table! But it displays the last poster. So on my forum its like "Hey everyone!" Started by: john Last Post by: Jake.

 

So what you are saying is, I should store the last post time in there as well. Hmm. That sounds like a good idea!!

[geoldr]

I would like to let you know that it worked!!! Thank you very much!!

[dannyb785]

 

 

So what you are saying is, I should store the last post time in there as well. Hmm. That sounds like a good idea!!

 

Yep! I think I might also have the last poster.. can't remember.. but yeah it's pretty easy, you just gotta make sure you update the topics table when a new post is made. Also remember to account for if a post is deleted in the topic, that if it was the most recent post that you update the topic row accordingly

[cooldude832]

you are storing a piece of data un needed

[dannyb785]

you are storing a piece of data un needed

 

 

lol I love how you make a post that is saying basically "you're wrong in what you did" without posting even so much as a hint of a better method.

 

Even IF there was a better way, albeit a complex query, don't you agree it's a good sacrifice to take up just a tiny bit more space if the method you're using makes it much easier for you to manage?

[cooldude832]

Even IF there was a better way, albeit a complex query, don't you agree it's a good sacrifice to take up just a tiny bit more space if the method you're using makes it much easier for you to manage?

 

The complexity of that query is non existent it is using mysql how it is designed.  A quality query is always a better option than storing an extra varchar field.

 

Image storing date for this forum unneeded for each thread. you are talking literally 100k dates.  It adds up building optimized in the beginning saves you in the end.

 

If you want to see complex queries they can be shown Join querys are the way mysql is meant to be used.

[dannyb785]

Even IF there was a better way, albeit a complex query, don't you agree it's a good sacrifice to take up just a tiny bit more space if the method you're using makes it much easier for you to manage?

 

The complexity of that query is non existent it is using mysql how it is designed.  A quality query is always a better option than storing an extra varchar field.

 

Image storing date for this forum unneeded for each thread. you are talking literally 100k dates.  It adds up building optimized in the beginning saves you in the end.

 

If you want to see complex queries they can be shown Join querys are the way mysql is meant to be used.

 

 

You're definitely not wrong, but at this guy's current state of experience and knowledge, it's better to use up a little more space and know exactly what he's doing and then down the road after he's learned more he can optimize his queries to use up better resources

[cooldude832]

you learn by taking steps forward not to the side dive in take a risk