Page 1 of 1

MySQL order of records, how?

Posted: Sun May 18, 2003 10:11 am
by scr0p
I have a news script and a guestbook script (that I wrote). They seem to work fine, but 'sometimes' the posts get out of order. Like I would post 1 thing and it would stay on top, now MySQL adds records to the end, so my loop reads them backwards, placing the newest on top, I do not have an ID, but how does this get sorted? How would I put a unique ID if I need one.

here is my loop code for my guestbook:

Code: Select all

$results   = mysql_query("SELECT * FROM gbtable");
	$NumOfRows = mysql_numrows($results);

	$r = $NumOfRows;
	while($r > 0)
	{
		--$r;
		$r_Nick     = mysql_result($results, $r, 'Nick');
		$r_Nick_IP  = mysql_result($results, $r, 'Nick_IP');
		$r_Email    = mysql_result($results, $r, 'Email');
		$r_Body     = mysql_result($results, $r, 'Body');
		$r_PostDate = mysql_result($results, $r, 'PostDate');
		ShowComment($r_Nick, $r_Nick_IP, $r_Email, $r_Body, $r_PostDate); #this just prints out a table with the info.
	}

Posted: Sun May 18, 2003 2:33 pm
by twigletmac
Maybe you just need to order the results from the SQL statement - assuming that postdate is what you need them ordered by you could have an ORDER BY statement set up as either DESC or ASC depending on which way you want the results to go:

Code: Select all

// Make sure you know what you expect to return from the table and put the 
// field names in the SQL
$sql = "SELECT Nick, Nick_IP, Email, Body, PostDate FROM gbtable ORDER BY PostDate DESC";
// Add some sort of error handling so there's something in case there's a problem
$results = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>'); 

// Check that records have been returned
if (mysql_num_rows($results) > 0) {
    // Make your code more efficient and use mysql_fetch_assoc() instead of mysql_result()
   while($row = mysql_fetch_assoc($results)) {
       $r_Nick = $row['Nick']; 
       $r_Nick_IP = $row['Nick_IP']; 
       $r_Email = $row['Email']; 
       $r_Body = $row['Body']; 
       $r_PostDate = $row['PostDate']; 
       ShowComment($r_Nick, $r_Nick_IP, $r_Email, $r_Body, $r_PostDate); #this just prints out a table with the info. 
    }
} else {
    echo 'No records were returned';
}
For more on SELECT statements:
http://www.mysql.com/doc/en/SELECT.html

Mac

Simple solution

Posted: Mon May 19, 2003 1:01 am
by coolpravin
Very simple solution is
add a new column may be named srno and make it
autoincreament from mysql.
phpmyadmin is the bets to do this.
You don't have to change your insert query or anything
only for select query use
select * FROM gbtable order by srno desc
and thats it.
Your code will work as expected now..
............. Pravin