[PHP-DB] Displaying by post time instead of topic time...

[David Balatero]

Ok, I have a forum script I’m writing with the following table
structure:
 
///////////////////////////////////////
 
CREATE TABLE forum_topics (
   topic_id int(11) NOT NULL auto_increment,
   topic_title varchar(100) NOT NULL,
   topic_poster int(11) DEFAULT '0' NOT NULL,
   topic_time int(14) DEFAULT '0' NOT NULL,
   topic_views int(10) DEFAULT '0' NOT NULL,
   forum_id int(10) DEFAULT '0' NOT NULL,
   topic_status int(10) DEFAULT '0' NOT NULL,
   topic_notify int(2) DEFAULT '0' NOT NULL,
   PRIMARY KEY (topic_id)
);
 
 
CREATE TABLE forum_posts (
   post_id int(11) NOT NULL auto_increment,
   topic_id int(11) DEFAULT '0' NOT NULL,
   forum_id int(11) DEFAULT '0' NOT NULL,
   poster_id int(11) DEFAULT '0' NOT NULL,
   post_text text NOT NULL,
   post_time int(14) DEFAULT '0' NOT NULL,
   poster_ip varchar(16) NOT NULL,
   PRIMARY KEY (post_id)
);
 
/////////////////////////////////////////////
 
The posts are linked to the topics using the topic_id field in each one.
As well, topic_time and post_time are both UNIX datestamps that are made
with mktime(). My current sql and display code is:
 
<?php
$sql = "SELECT t.*, u.user FROM forum_topics t, frontend_users u WHERE
t.forum_id = '$id' AND t.topic_poster = u.id ORDER BY topic_time DESC”;
$result = mysql_query($sql)
      or die("</table></td></tr></table>Error: Could not query forum
database.<br>".$sql."<br>Mysql said:".mysql_error()."");
 
while ($myrow = mysql_fetch_array($result)) {
      if ($i % 2) {
             echo '<tr bgcolor='.$rowcolor.' width="100%">';
      }
      else {
             echo '<tr bgcolor='.$altrowcolor.' width="100%">';
      }
      $i++;
      // the following three vars are custom functions I wrote, don’t
worry about them.
      $poster = getUserName($myrow["topic_poster"]);  
      $replies = (getTotalPosts($myrow["topic_id"], "topic") - 1);  
      $lastpost = getLastPost($myrow["topic_id"]); // post_time,
poster_id, username as array()
      $date = returnDateNoTime($lastpost["post_time"]);
      $user = $lastpost["username"];
 
      echo '<td>&nbsp;</td>';
      echo '<td>'.$font.'<a
href="viewtopic.php?id='.$myrow["topic_id"].'">'.$myrow["topic_title"].'
</a></td>';
      echo '<td>'.$font.$poster.'</font></td>';
      echo '<td>'.$font.$myrow["topic_views"].'</font></td>';
      echo "<td width=\"100%\">$font<small>$user posted on
$date</small></font></td>";
      echo '<td>'.$font.$replies.'</font></td>';
      echo '</tr>';
}
 
?>
 
What I’m doing right now is querying the database and pulling out the
latest topics in order of topic creation. What I want to do is ORDER
them by the most recent post_time in each topic. If anyone has any
suggestions on how I might do that, I’d be very grateful.
 
Thanks!
David Balatero
[EMAIL PROTECTED]