Reverse Ordering While Loop

[Tibblez]

I have a comments page and I wanted the results to be taken in reverse order out of the mysql database so that the newest would be at the top, problem is that the way I'm doing it right now leaves an empty table row before actually printing anything from the database, my code is:

 

<table align="center">
<th>Comments</th>

<?php

  $username="username";
  $password="password";
  $database="database";

  mysql_connect(localhost,$username,$password);
  @mysql_select_db($database) or die ("Could not connect: " . mysql_error());

  $query="SELECT * FROM comments";
  $result=mysql_query($query);
  $num=mysql_numrows($result);
  $i=$num;

  while ($i >= 0) {
  $name=mysql_result($result,$i,"name");
  $comment=mysql_result($result,$i,"comment");

  print "<tr>";
  print "<td><b>$name</b> <br /><br />$comment</td>";
  print "</tr>";
  
  $i--;
  
};

?>
</table>

 

Any help would be appreciated!

[Naez]

Add an ID element to your table, primary key, auto_increment, not NULL, etc.. You probably have one already

 

so change your query to 

SELECT * FROM `comments` ORDER BY `id` DESC LIMIT 20

[Psycho]

I agree with Naez, but to explain why you have this problem...

 

If a record set has 10 records then mysql_numrows() will return 10, obviously. But, the records go from 0 to 9! I wouldn't suggest using the code you have above, but the correct thing to do would be this:

 

$num=mysql_numrows($result) - 1;

[Tibblez]

Thanks mjdamato! That did it!

[dk1983]

...But seriously, listen to Naez.

(and add yourself a TIMESTAMP in there so you can ORDER BY it, not id).

[Naez]

Good suggestion on the timestamp.

 

Make a PHP file and run this code:

<?php

$sql = "ALTER TABLE `comments` ADD `post_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP";

 

mysql_query($sql);

?>

 

Do that, then change your code in that page to:

 

 

<th>Comments</th>

<?php

  $username="username";
  $password="password";
  $database="database";

  mysql_connect(localhost,$username,$password);
  @mysql_select_db($database) or die ("Could not connect: " . mysql_error());

  $query="SELECT * FROM comments ORDER BY post_time DESC";
  $result=mysql_query($query);

  while ($row = mysql_fetch_array($result))
{
	echo "<tr>";
  		echo "<td><b>" . $row['name'] . " </b> <br /><br />" . $row['comment'] . "</td>";
  		echo "</tr>";
}
?>

 

 

 

Note: the results might be a little off unless you update the timestamps of older posts within the DB (as they will default their timestamps to "0000-00-00 00:00:00", but newer posts will be in the correct order.)

[Tibblez]

I took your guys advice and ordered it by timestamps. Seems to be working well. Thanks for the help!