Daney11 Posted March 17, 2008 Share Posted March 17, 2008 Hey guys, This is going to be a long one. I have created my own simple forum for my website. http://one.team-who.co.uk/forums.php I have 3 tables for the forums. forums -forum_id -forum_name -forum_type -forum_heading forum_topics -forum_topic_id -forum_topic_forumid -forum_topic_date -forum_topic_memberid -forum_topic_name -forum_topic_body forum_posts -forum_post_id -forum_post_forumid -forum_post_date -forum_post_topicid -forum_post_memberid -forum_post_body Now if you see my forums, http://one.team-who.co.uk/forum.php?forum_id=1, i need to code it so that the Last Post date is the highest, and currently it is now. For example Last Post - would be wrong 2008-03-17 20:00:00 2008-03-17 21:00:00 Last Post - would be correct 2008-03-17 21:00:00 2008-03-17 20:00:00 So obviously i need to JOIN the tables but im not really sure how. At the moment i have $forumTopicQuery = "SELECT * FROM forum_topics WHERE forum_topic_forumid = $forum_id AND forum_topic_id = $forum_topic_id LIMIT 1"; $forumTopicresult = mysql_query($forumTopicQuery, $connect) or die(mysql_error()); AND $forumPostsQuery = "SELECT * FROM forum_posts WHERE forum_post_topicid = $forum_topic_id ORDER BY forum_post_date ASC"; $forumPostsresult = mysql_query($forumPostsQuery, $connect) or die(mysql_error()); So now the forum_posts ORDER by date DESC. But i need it so the LAST POST entered in ANY topic is at the top of my forum list. I hope this makes sense to people. Any help would be great Thanks a lot Quote Link to comment Share on other sites More sharing options...
schilly Posted March 17, 2008 Share Posted March 17, 2008 Move to Mysql help? Quote Link to comment Share on other sites More sharing options...
schilly Posted March 17, 2008 Share Posted March 17, 2008 But I think your looking for something like this. $forumTopicQuery = "SELECT * FROM forum_topics, forum_posts WHERE forum_topics.forum_topic_id = forum_posts.forum_post_topicid and forum_topics.forum_topic_forumid = $forum_id AND forum_topics.forum_topic_id = $forum_topic_id ORDER BY forum_posts.forum_post_date ASC LIMIT 1"; Quote Link to comment Share on other sites More sharing options...
Daney11 Posted March 17, 2008 Author Share Posted March 17, 2008 Hmm, doesnt work. Quote Link to comment Share on other sites More sharing options...
Daney11 Posted March 18, 2008 Author Share Posted March 18, 2008 I get 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 'ORDER BY forum_posts.forum_post_date DESC' at line 1 $ForumTopicQuery = "SELECT * FROM forum_topics, forum_posts WHERE forum_topics.forum_topic_teamid = $team_url AND forum_topics.forum_topic_id = forum_posts.forum_post_topicid AND forum_topics.forum_topic_forumid = $forum_id AND forum_topics.forum_topic_id = $forum_topic_id ORDER BY forum_posts.forum_post_date DESC"; Quote Link to comment Share on other sites More sharing options...
Jeremysr Posted March 18, 2008 Share Posted March 18, 2008 What I do is have a `lastpost` column which contains the timestamp of when the last post of the topic was posted, which gets updated whenever a new reply is posted or deleted. Then I order the topics by that column. Quote Link to comment Share on other sites More sharing options...
Daney11 Posted March 18, 2008 Author Share Posted March 18, 2008 forum_topics -forum_topic_id -forum_topic_forumid -forum_topic_date -forum_topic_memberid -forum_topic_name -forum_topic_body -forum_topic_lastpost Then what you'd do is UPDATE forum_topics SET forum_topic_lastpost = $date(ofnewpost) Along the right lines? Quote Link to comment Share on other sites More sharing options...
Jeremysr Posted March 18, 2008 Share Posted March 18, 2008 Well I just do "SET forum_topic_lastpost = '".time()."'" but yeah something like that. (I've never used SQL dates or date functions before.) Quote Link to comment Share on other sites More sharing options...
Daney11 Posted March 18, 2008 Author Share Posted March 18, 2008 i decided to going with you way on how to do it. It works perfect and my forums aint going to be complex anyways. Thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.