more efficent loop?

[MWpro]

I've created a news update script, which allows the user to post news, which is stored in the mysql database. On the view news page, I have this code below to display the last four entries in descending order:

[quote]
<?
$connection = mysql_connect("localhost" , "username" , "password");
$db = mysql_select_db(database , $connection);
$result=mysql_query("SELECT * FROM news");
$ctr=mysql_num_rows($result);
for($i=$ctr;$i>($ctr-4);$i--){
$result = mysql_query("SELECT * FROM news WHERE id='$i'");
$row = mysql_fetch_array($result);
$date = $row["date"];
$content = $row["content"];
echo "<strong>$date</strong>\n";
echo "<p>$content</p>\n";
}
?>
[/quote]

I was wondering if there was a more efficent way to display these results (without doing the backwards for loop method that I am doing, and without having to query the database to get the number of rows). Thanks for any help you can give me.

[NickCoons]

I'm sure you can see that your loop is going to take longer and longer to run as more articles are added. You can add DESC to the end of your query string, and order by date. This will request all of the same information, in date order, starting from the newest to the oldest. This way, it doesn't matter how many records there are, your loop will display rows 1-4 because those are the newest. Also, your query can be before your loop.. you don't need to call it every time, because you're requesting the same information each time, and "$result" already has it.

<?
  $connection = mysql_connect("localhost" , "username" , "password");
    $db = mysql_select_db(database , $connection);
    $result = mysql_query("SELECT * FROM news WHERE id='$i' ORDER BY date DESC");
    for($i=1;$i>5;++$i)
    {
      $row = mysql_fetch_array($result);
      echo "<strong>" . $row["date"] . </strong>\n";
      echo "<p>" . $row["content"] . "</p>\n";
    }
  mysql_close($connection);
?>

[dcrombie]

SELECT * FROM news ORDER BY date DESC LIMIT 4;

;)

[Birdman]

How about this:

$result=mysql_query("SELECT * FROM news ORDER BY id or timestamp LIMIT 4 DESC");
while ($i = mysql_fetch_array($result)){
display results
}

Hopefully you have a timestamp row in your table. If not, use your id field(hopefully that's auto-increment).

damn, I'm slow ;)

[MWpro]

That worked perfectly; thank you very much for showing me the right way to do it :)