smerny Posted July 8, 2009 Share Posted July 8, 2009 $search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime FROM topics LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub ON topics.ID_board = postSub.ID_board WHERE topics.ID_board='$board_id' ORDER BY postSub.LatestPostTime DESC"; $result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: topics: " . mysql_error()); while($topics = mysql_fetch_array($result)) { Making a forum, trying to get the topics to be ordered with the one with the latest post first... this is the code I was trying to work it... and it's not working... it is in the order in which the topic was created Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/ Share on other sites More sharing options...
fenway Posted July 15, 2009 Share Posted July 15, 2009 What's not working... show output. Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-875983 Share on other sites More sharing options...
smerny Posted July 16, 2009 Author Share Posted July 16, 2009 trying to get the topics to be ordered with the one with the latest post first... but it is in the order in which the topic was created Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-876443 Share on other sites More sharing options...
fenway Posted July 19, 2009 Share Posted July 19, 2009 Well, the query looks like it should be OK.... have you checked the output values of created and pasttime? Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-878112 Share on other sites More sharing options...
smerny Posted July 21, 2009 Author Share Posted July 21, 2009 $search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime FROM topics LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub ON topics.ID_board = postSub.ID_board WHERE topics.ID_board='$board_id' ORDER BY postSub.LatestPostTime DESC"; $result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: topics: " . mysql_error()); while($topics = mysql_fetch_array($result)) { echo $topics['LatestPostTime'] ." Latest Post Time -- ". $topics['ID']; gives me... 2009-07-15 12:12:09 Latest Post Time -- 1 2009-07-15 12:12:09 Latest Post Time -- 2 2009-07-15 12:12:09 Latest Post Time -- 3 2009-07-15 12:12:09 Latest Post Time -- 4 2009-07-15 12:12:09 Latest Post Time -- 10 when it should give me... 2009-07-15 12:12:09 Latest Post Time -- 10 2009-07-15 11:24:57 Latest Post Time -- 1 2009-07-08 16:44:46 Latest Post Time -- 4 2009-07-08 14:23:03 Latest Post Time -- 2 2009-07-07 22:46:44 Latest Post Time -- 3 Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-879217 Share on other sites More sharing options...
kickstart Posted July 21, 2009 Share Posted July 21, 2009 Hi Not sure of the exact layout of your tables, but I presume that ID_board is the id of a particular forum on your board (ie, like this one is MySQL Help). If so you appear to be getting the latest post for any thread on the board you are looking at (so all threads would land up with the same latest post date). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-879328 Share on other sites More sharing options...
smerny Posted July 21, 2009 Author Share Posted July 21, 2009 the ID is actually a number, there is another table in the DB for boards which has one field being "ID" which increments, and both the table for posts and for topics have a field called "ID_board" which specifies which board the topic/post belongs to on this site, I believe the ID of MySQL Help is 3.0 Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-879485 Share on other sites More sharing options...
kickstart Posted July 21, 2009 Share Posted July 21, 2009 Hi Think you need the id of the topic in the join. Something like this (guessing at column names):- $search = "SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime FROM topics LEFT OUTER JOIN (SELECT ID_topic, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_topic) postSub ON topics.ID = postSub.ID_topic WHERE topics.ID_board='$board_id' ORDER BY postSub.LatestPostTime DESC"; $result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: topics: " . mysql_error()); while($topics = mysql_fetch_array($result)) { echo $topics['LatestPostTime'] ." Latest Post Time -- ". $topics['ID']; All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-879505 Share on other sites More sharing options...
smerny Posted July 21, 2009 Author Share Posted July 21, 2009 oh dang, now i get what you were saying before. thanks a lot Quote Link to comment https://forums.phpfreaks.com/topic/165270-solved-order-by-the-max-value-in-another-table/#findComment-879509 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.