php, mySQL return last x rows

Question

I'm working on a php/mySQL/ajax shoutbox and have run into a small snag. On the page I want the content to be loaded from oldest to newest, with the newest on the bottom. I also want to limit the output to reduce load times once the database starts to get a lot of data in it.

Here is the current code;

    <?php

include_once("../includes/db.php");
include_once("../includes/functions.php");

$q="SELECT tM.*, tC.char_name as username, tC.char_id as char_id
FROM shoutbox tM JOIN characters tC
ON tC.char_id=tM.char_id
ORDER BY shout_id DESC LIMIT 25";

db_Connect();

$result=mysql_query($q);

while($row=mysql_fetch_array($result))
{
    $classColor = getClassColor($row['char_id']);
    echo "<span class='".$classColor."'>".$row['username']."</span>: ",nl2br($row['shout_message'])."<br />";
}

mysql_Close();

?>

I have tried using while($row=array_reverse(mysql_fetch_array($result))) as well as $result = array_reverse(mysql_query($q)) but both return an error that array_reverse needs to be fed an array.

So far anything I have found on the web from the SQL side have all been answered "just use DESC or ASC accordingly."

MartinodF · Accepted Answer · 2010-11-09 23:06:04Z

1

$res = mysql_query($q);
$shouts = array();

while($row = mysql_fetch_assoc($res))
  $shouts[] = $row;
$shouts = array_reverse($shouts);

foreach($shouts as $shout) {
  // show them...
}

answered Nov 9, 2010 at 23:06

MartinodF

8,2842 gold badges34 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashley · Accepted Answer · 2010-11-09 23:06:28Z

1

If you want oldest to newest, and shout_id is auto increment just use

ORDER BY shout_id ASC LIMIT 25

You have DESC/ASC mixed up

answered Nov 9, 2010 at 23:06

Ashley

5,9579 gold badges41 silver badges82 bronze badges

1 Comment

Jesse Over a year ago

ASC LIMIT 25 will return the first 25, so anyone posting after the first 25 never show up (this is how I discovered the problem in the first place, scratching my head wondering why new posts stopped showing up as soon as I switched to a different user)DESC LIMIT 25 will return the last, but in the wrong order because it effectively reverses the order of the entire table before selecting results. I've tried both ways and neither works, thus why I asked here.

AndreKR · Accepted Answer · 2010-11-09 23:29:26Z

1

$result=mysql_query($q);

unset($temp_array);

while($row = mysql_fetch_array($result))
    $temp_array []= $row;

$temp_array = array_reverse($temp_array);

foreach ($temp_array as $row)
{
    $classColor = getClassColor($row['char_id']);
    echo "<span class='".$classColor."'>".$row['username']."</span>: ",nl2br($row['shout_message'])."<br />";
}

edited Nov 9, 2010 at 23:29

answered Nov 9, 2010 at 23:06

AndreKR

34k21 gold badges120 silver badges181 bronze badges

2 Comments

MartinodF Over a year ago

Are you completely sure about $temp_array []= ...? Last time i checked, I believe this creates a false element as the last element of the array, since the last time you call it, mysql_fetch_array returns false.

MartinodF Over a year ago

And i upvoted yours since you had the patience to put in the "display" part ;)

Wally Kolcz · Accepted Answer · 2012-10-22 12:46:40Z

0

Seems like a lit of work. I just used this:

$result = mysql_query($strQuery) or die(mysql_error());

while ($row = array_reverse(mysql_fetch_array($result))) {

$row['something'];

}

answered Oct 22, 2012 at 12:46

Wally Kolcz

1,6643 gold badges25 silver badges47 bronze badges

Collectives™ on Stack Overflow

php, mySQL return last x rows

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related