[SOLVED] MySQL Order problem

[freelancer]

Well, I use news adding script, can add new newsposts from admin panel, at the main page where I include them they will be ordered as newest one is on top - All fine. Now I made script for latest headlines, witch selects headline from table and includes it at my latest news menu. But I want that only 5 latest ones will be displayed only - I set my loop to 1 and it will display me 5 headlines, but it won't display me latest, for example I made 6 newsposts and at my latestheadlines menu it will display only 1,2,3,4,5. Here is my script:

<?php
include("config.php");

mysql_select_db($database) or die( "Unable to select database");

$query2="SELECT * FROM phpnews_news ORDER BY id DESC";
$result2=mysql_query($query2);

$num=mysql_numrows($result2);
mysql_close();
?>

<table border="0" cellspacing="0" cellpadding="0" class="headline">

<?php
$i=1;
while ($i < $num) {
$headline=mysql_result($result2,$i,"subject");
$mid=mysql_result($result2,$i,"id");

echo "<tr>";
echo "<td background=\"images/menubg1.jpg\" width=\"10\"></td>";
echo "<td background=\"images/menubg1.jpg\" width=\"192\" height=\"21\"><a href=\"{$Settings['index.php']}?action=fullnews&showcomments=1&id=$mid\">$headline</a></td>";
echo "</tr>";
$i++;
}

echo "</table>";
?>

 

My question is how I could make them displayed so that I see last 5 only. Thank you

[trq]

For starters, don't rely on an auto-incrementing id to stay in order, that is not there intended use. Use a timestamp type field and order by that. eg;

 

SELECT * FROM phpnews_news ORDER BY tstamp DESC LIMIT 5

 

Always select only what you need (LIMIT).

[freelancer]

I tried to order by time too but gave same result, I added limit and this fixed, thaks alot!

 

Solved:)

[camdagr81]

The problem is that you have $i set as 1 where it should be 0 but here's a better way to do it

$query2 = "SELECT * FROM `phpnews_news` ORDER BY `id` DESC LIMIT 6";
$result2 = mysql_query($query2);
$num = mysql_num_rows($result2);
mysql_close();

for($i = 0; $i < $num; $i++) {
// Your loop stuff here
}

 

[trq]

Once you have a LIMIT in your query you need not worry about counting your loops. Use a while.

 

<?php

  $sql = "SELECT * FROM phpnews_news ORDER BY tstamp DESC LIMIT 5";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      while ($row = mysql_fetch_assoc($result)) {
        // display data
      }
    }
  }

?>