[RESOLVED] MySql inserts not in order

IceRegent

I am having an issue with my insert statements apparently not being put into the database at the end. The appear to be inserting into random locations. I have a key, and 4 fields. The insert command is as follows:

$uname=$_SESSION['user'];
$cdate=time();
$message=$_POST['chatmess'];
$chatroom=$_SESSION['chatroom'];
$query = "INSERT INTO chat (cid, uname, message, chatroom, timestamp) VALUES (NULL, '$uname', '$message', '$chatroom', '$cdate')";
$result = mysql_query($query) or exit(mysql_error());

However, the inserts dont appear to be appending to the end of the file, rather they are showing up at random locations. Any Ideas?

Ice

dagon

if you have an auto increment id field and return the rows with order by id there should be no issue.

Weedpacket

Further, the database is free to physically store records in any way it likes. Further still, if you don't supply an ORDER BY clause it means you don't care what order you want the results in, and again the database is free to return them in whatever order is most convenient for it.

tomhath

the inserts dont appear to be appending to the end of the file

Further even stiller, the above quote is true; a database table is not a sequential file. It's an unordered set.

IceRegent

So, you are saying that, in order to get an ordered list out, I need to retrieve the data by basically sorting it using an 'order by' statement? I understand what you are saying, but, how would I do this then? Here is the entire display script:

<?php
session_start();
include 'include/config.php';
echo '<head>';
echo '<meta http-equiv="refresh" content="10">';
echo '</head>';
$chatroom=$_SESSION['chatroom'];
$query = "SELECT * FROM chat";
$result = mysql_query($query) or exit(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
	{
		if($row['chatroom'] == $chatroom)
			{
				$cname=$row['uname'];
				$mess=$row['message'];		
				echo '<b style="color:#FF0000">' . $cname . '</b> : ' . $mess;
				echo '<br>';
			}
	}

// Now we need to perform housecleaning function - check for older messages and delet if older than certain time frame from now.
$timenow=time();
$query = "SELECT * FROM chat";
$result = mysql_query($query) or exit(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
	{
		$messnow=$row['timestamp'];
		$timediff=($timenow) - $row['timestamp'];
		if($timediff > "1200") 
		{ 		

		$query1="DELETE FROM chat WHERE timestamp='$messnow' LIMIT 1";
		$result1 = mysql_query($query1) or exit(mysql_error());	

	}

}

It displays the data one record at a time, since the amounts of records in the particular table changes. The display script also checks for outdated data and deletes it if the data is older than a certain time frame. This seems to work excellently. However, I guess my question is, since I dont know how much data is in the table at any certain time, how do I read it back in order when I am reading one record at a time, displaying it, then going back for more? Is the a more efficient way to do this?

Ice

dagon

the first query should be

$query = "SELECT * FROM chat ORDER BY timestamp ";

im assuming timestamp in this case

your clean up can be done with one mysql querry

$query1="DELETE FROM chat WHERE timestamp> NOW()+ INTERVAL 20 MINUTES";

IceRegent

Hey, that works good. Uhm, any idea on how to reverse the order to start from the largest and work down to the lowest?

NogDog

. . . ORDER BY timestamp [color=red]DESC[/color]

IceRegent

Ok, got it..That works. Thank you so much for the help guys! Kudos to you!

Ice