MySQL order of records, how?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
scr0p
Forum Newbie
Posts: 23
Joined: Mon May 05, 2003 6:49 pm
Location: NY

MySQL order of records, how?

Post 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.
	}
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
coolpravin
Forum Newbie
Posts: 20
Joined: Mon May 19, 2003 12:56 am
Location: India

Simple solution

Post 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
Post Reply