[Resolved] ORDER BY help

[cmccomas]

If I want to get the second most recent entry in a table, how do I ORDER BY?

Like if I post a news story that is story_id=10 then below I want to get the headline of ONLY the story_id that is one less than the previous.

Thannnnks

[petitduc]

For example, you could use ORDER BY .... LIMIT 2. THat would only return the 2 first rows.
Then manage to only get the 2nd row.

[cmccomas]

Then manage to only get the 2nd row.

How would I 'manage' to do that?

Cheers!

[cmccomas]

I've got it getting two rows, but not sure how to get the info from the 2nd row.

[petitduc]

For example, use a counter :

$i = 0;

/*the desired row index. 1 is the index of the 2nd row*/
$n = 1;

/* a variable to hold all the data from the row are looking for */
$therow=array();

while($row=mysql_fetch_assoc($result)) {
   if ($i ==$n) {
      $therow=$row;
      break;  /*row found : we exit from the loop */     

   }
    $i++;
}

You can now access all your fields through $therow["id"], $therow["headline"] or whatever are your field names.
The above code is self-explanatory I think.

[coppercoins]

I would think a much easier way to handle it would be with two queries. First query runs a count of rows, assign that to $count. Second query could get the row at $count-1.

[cmccomas]

Originally posted by coppercoins
I would think a much easier way to handle it would be with two queries. First query runs a count of rows, assign that to $count. Second query could get the row at $count-1.

Can you post an example of this?

[coppercoins]

Sure:

$result = mysql_query("SELECT count(*) FROM table1");

$rows = mysql_num_rows($result);

$rows--;

$select = "SELECT * FROM table1 LIMIT ".$rows.",1";

$newResult = mysql_query($select);

$article = mysql_fetch_array($newResult);

Deal with the resulting set as $article and you should have your next-to-last article.

[cmccomas]

Thanks mate!

[coppercoins]

Hope it works for you. Luck!