Jump to content

Topics Not Going In Correct Order


smerny
 Share

Recommended Posts

I'm working on a forum, but I am having trouble getting it to sort the topics by which one has a post with ID_topic that is most recent...The URL is http://www.submitandrate.com/forum/index.phpand the part I'm having trouble is the page_mode=boardview ( http://www.submitandrate.com/forum/index.p...&board_id=1 )here is the code from that page_mode...

if($page_mode == "boardview"){	$board_id = $_REQUEST['board_id'];		$search = "SELECT * FROM boards WHERE ID='".$board_id."'";	$result = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode boardview: boards: " . mysql_error());	$boards = mysql_fetch_array($result);		$board_name = $boards['name'];		echo "	<div id='forumhead'>	  <a href='index.php'>Main Forum</a> - <a href='index.php?page_mode=boardview&board_id=".$board_id."'>".$board_name."</a>	</div>	<div class='fsub'> 	<table cellspacing='1px' width='100%'>			<tr>			<th width='54%'>			  Topic			</th>			<th width='7%' style='text-align:center'>			  Views			</th>			<th width='7%' style='text-align:center'>			  Replies			</th>			<th width='16%'>			  First Post			</th>			<th width='16%'>			  Last Post			</th></tr>";	   $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))	{		$search = "SELECT posttime, ID_poster FROM posts WHERE ID_topic='".$topics['ID']."' ORDER BY posttime DESC LIMIT 1";		$postresult = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode none: posts: ". mysql_error());		$posts = mysql_fetch_array($postresult);					$lasttime = $posts['posttime'];		$id_lastposter = $posts['ID_poster'];					$search = "SELECT DisplayName FROM users WHERE ID='".$id_lastposter."'";		$userresult = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode none: users: " . mysql_error());		$users = mysql_fetch_array($userresult);					$name_lastposter = $users['DisplayName'];				$search = "SELECT posttime, ID_poster FROM posts WHERE ID_topic='".$topics['ID']."' ORDER BY posttime ASC LIMIT 1";		$postresult = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode none: posts2: ". mysql_error());		$posts = mysql_fetch_array($postresult);					$firsttime = $posts['posttime'];		$id_firstposter = $posts['ID_poster'];				$search = "SELECT ID FROM posts WHERE ID_topic='".$topics['ID']."'";		$postresult = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode none: posts3: ". mysql_error());		$replies = mysql_num_rows($postresult)-1;					$search = "SELECT DisplayName FROM users WHERE ID='".$id_firstposter."'";		$userresult = mysql_query($search) or die ("SQL Error: forum/index.php: pagemode none: users2: " . mysql_error());		$users = mysql_fetch_array($userresult);					$name_firstposter = $users['DisplayName'];				echo "<tr>		<td>				  <a href='index.php?page_mode=topicview&topic_id=".$topics['ID']."'>".$topics['name']."</a> - 		  ".$topics['description']."		</td>		<td>		  ".$topics['views']."		</td>		<td>		  ".$replies."		</td>		<td>		  ".$firsttime."<br />		  by <a href='../members.php?action=getinfo&userid=".$id_firstposter."'>".$name_firstposter."</a>		</td>		<td>		  ".$lasttime."<br />		  by <a href='../members.php?action=getinfo&userid=".$id_lastposter."'>".$name_lastposter."</a>		</td></tr>";  	} //end topic loop	echo "</table></div><div style='clear:both'></div>";		if (!is_numeric($userrank))	{	echo "Please <a href='../login.php'>log in</a> or <a href='../register.php'>register</a> before posting.";	}	else if ($userrank == 0)	{	echo "Please <a href='../edit.php'>change your password</a> to verify your account before posting.";	}	else if ($userrank > 0)	{	echo "  	  <div class='post'>		Reply		<form method='post' action='post.php'>		  Subject:<input type='text' name='subject'>		  Description:<input type='text' name='description'>		  Message:				  <textarea name='message'></textarea>		  <input type='hidden' name='board' value='".$board_id."'>		  <input type='hidden' name='page_mode' value='post'>		  <input type='submit' name='submit' value='Submit Reply'>		</form>	  </div>";	}}//end pagemode boardview

the part i thought would sort it correctly is....

	   "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";

Edited by smerny
Link to comment
Share on other sites

Instead of doing a join, maybe it would be better to add some columns to the topics table to store information about the latest post, you can store the post ID, date, title, author etc of the latest post in the topics table and update that every time someone makes a post in a topic. It would be a little bit of duplicate information, but you wouldn't need to use a join to get the topic list, you would just get the topics and order by the last post date.

Edited by justsomeguy
Link to comment
Share on other sites

that was my original thought, but i figured doing that would cause some problems... like if the last post was deleted, it would still show up... or i'd have to add more code/queries for it to take on the value of the next last post if the post that was deleted was the last one

Link to comment
Share on other sites

Right, there are other things to consider but it's probably the best solution. You'll only have to do those special queries once when something gets deleted or whatever, but you'll be saving time and work every time the index gets loaded since you no longer have to do the join. Joins are expensive, and when your database grows you'll realize that the fewer joins you need to use, the better. Sometimes it makes more sense to add some code to handle the special cases if you can remove a join that happens in the common case.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...