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 Link to comment https://forums.phpfreaks.com/topic/96576-forums/ Share on other sites More sharing options...
schilly Posted March 17, 2008 Share Posted March 17, 2008 Move to Mysql help? Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494329 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"; Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494334 Share on other sites More sharing options...
Daney11 Posted March 17, 2008 Author Share Posted March 17, 2008 Hmm, doesnt work. Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494489 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"; Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494499 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. Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494510 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? Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494513 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.) Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494518 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 Link to comment https://forums.phpfreaks.com/topic/96576-forums/#findComment-494560 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.