MySQL sorts data by primary key

[Donotalo]

Just noticed that MySQL sorts a table records by primary key. I want to prevent this behaviour. I don't need any sorted data. I need the sequence at which user entered data. How can I do that?

Thanks.

EDIT: MySQL version 5.0.16, PHP version 5.2.5, Server IIS 5, OS Windows XP SP 2.

[davide++]

Hi all

Standard SQL doesn't say how data are sorted when you execute a query without the ORDER BY clause; usually by primary key, but it isn't a rule. I don't understand your requirement; if you want to sort data you have to add an ORDER BY, if not because you don't need sorting you won't use ORDER BY. Where's the problem?

[Donotalo]

Sorry for unclear explanation. What I meant is this:

I'm inserting records using INSERT command.
When required, I'm reading value using SELECT command and storing the records for later use:

$query = "select * from bookmark where loweruname=\"" . $_SESSION["loweruname"] . "\";";
$response = execute_query($query);//wraps mysql_query(), die() if unseccessful, returns the return value of mysql_query()
$i = 0;
while($dbarray = mysql_fetch_array($response)) {
//do stuff
}

Here I'm getting the records sorted by primary key, which is id field. id is a randomly generated 30 character length string. So the sequences "user's inputs" and "retrieved records" are not same, since I'm getting this records sorted by id. I want to stop this behaviour. Is there any way to do that?

[davide++]

OK, now I understand; you want to get the record sorted by the time of insertion in descendand order, don't you?
You should add an auto-increment field and apply the ORDER BY clause on it; or you can use a insertion timestamp field as well, but it's dangerous because some records can have the same timestamp.

I hope this will help you.

[PeejAvery]

I agree with David. If you set your primary key to auto-increment, you yourself won't have to handle the creating of unique ids. However, this will auto-sort by the primary column. You can just create a unique or index column which will not auto-sort. Your other option is to create a timestamp column and sort by that after every query.