Reversing mySQL results for chat script

[ensellitis]

I am working on a chat script.  Problem is, I want to limit the posts shown to 20, while having the newest result on the bottom of the list...  I tried 'ORDER BY id ASC' and 'ORDER BY id DESC'.

 

It either shows the newest post on top of the list (assuming I have 40 entries):

ID #40. message

ID #39. message

...

ID #22. message

ID #21. message

 

Or shows 20 oldest posts...

ID #1. message

ID #2. message

...

ID #19. message

ID #20. message

 

 

All I want is this...

ID #20. message

ID #21. message

...

ID #39. message

ID #40. message

 

Does that make any sense?

 

Any help appreciated.

 

FYI, here is the code, so if I didn't make much sense, maybe this will help clear up what I'm doing.

<?
require_once('system.php');

$memcount = 0;
$sql = "SELECT * FROM ".$table_prefix."messages ORDER BY id ASC LIMIT 20";
$result = mysql_query($sql);

while ($each = mysql_fetch_assoc($result)) {
	$row_color	=	($memcount % 2) ? "odd" : "even";
	$username = $each['username'];
	$message = $each['message'];

	$theuser_q = "SELECT * FROM ".$table_prefix."users WHERE username = '$username'";
	$theuser_r = mysql_query($theuser_q) or die(mysql_error());
	$theuser = mysql_fetch_array($theuser_r) or die(mysql_error());

	$messages		.= "<div class='".$row_color." clear post'>\n";
	$messages		.= "<div class='userstuff'>\n";
	$messages		.= "<span class='author'><a href='javascript:viewProfile(".$theuser['ID'].");' id='".$theuser['username']."' style='color:#".$theuser['color']."'>".$username."</span></a><br />\n";
	$messages		.= "<span class='timeago'>".time_since($each['time'])."</span>\n";
	$messages		.= "</div>\n";
	$messages		.= "<p class='message'>".$message."</p>\n";
	$messages		.= "<div class='clearbr'><!-- I have ".$memcount." monkeys! --></div>\n";
	$messages		.= "</div>\n\n";

	$memcount++;

}

echo $messages
?>

 

[mmarif4u]

Try it this way:

$sql = "SELECT * FROM ".$table_prefix."messages ORDER BY id ASC LIMIT 20,40";

 

[ensellitis]

That wouldn't work if I have more than 40...

 

HOWEVER, you pointed me in the right direction...  So I replaced my old $sql with this...

 

$get_count = get_rows('messages');
$get_lower = $get_count - 20;

$sql = "SELECT * FROM ".$table_prefix."messages ORDER BY id ASC LIMIT $get_lower,$get_count";

 

And it works great.

 

Thanks for the help!

[mmarif4u]

That wouldn't work if I have more than 40...

 

HOWEVER, you pointed me in the right direction...  So I replaced my old $sql with this...

 

$get_count = get_rows('messages');
$get_lower = $get_count - 20;

$sql = "SELECT * FROM ".$table_prefix."messages ORDER BY id ASC LIMIT $get_lower,$get_count";

 

And it works great.

 

Thanks for the help!

 

Yup, i give you an example of it. Later you can modify it for pagination or something else, which you already sorted it out for your script to show different results in gap of 20.

Good luck.