sort by primary key

[khefner]

Hello, I am trying to perform a basic sort by the primary key but I am having no success.
Currently, I use PHP and SQL to pull data from the database to a html table for viewing - this works fine. But I want the data in the order that it is put into the database, so, I want to sort by the primary key.
Here is what I tried to do, but it does not sort:

mysql_query("SELECT * FROM warriors Order By userid ASC ", $db);

$result = mysql_query("SELECT * FROM warriors", $db);

I added the top line in an effort to sort, before the data is pulled. (userid is the name of the field that is the primary key)

Thanks,
Hef

[fenway]

I'm not sure what you mean by that... just do the following:

[code]$result = mysql_query("SELECT * FROM warriors Order By userid ASC ", $db);[/code]

BTW, why do you want to do this to begin with? The "proper" way is to store the create date, and sort by that -- you should never be using UIDs for anything other than FKs.

[khefner]

[!--quoteo(post=340720:date=Jan 28 2006, 06:51 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 28 2006, 06:51 PM) [snapback]340720[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm not sure what you mean by that... just do the following:

[code]$result = mysql_query("SELECT * FROM warriors Order By userid ASC ", $db);[/code]

BTW, why do you want to do this to begin with? The "proper" way is to store the create date, and sort by that -- you should never be using UIDs for anything other than FKs.
[/quote]

Ok I can sort by the create date thats fine.
What do you mean by VID? (Primary key?)
FK's?

Basically, I have data coming in via the input form. Its important, that when its viewed via the html table, that it is displayed in order of when when they were inserted into the database. So I would agree that the date would work for that purpose, however what if you have multiple entries of the same date? If you sorted by date, you would not know which one was first, second, third etc. (of the same date)

So, thats why I figured sorting by the "user id" which is a auto_incrementing field designated as the primary key, would make sense.

By the way this is something that has me perplexed also. When I view the table data in phpmyadmin, some of the records are not "in order" . Another words, most of the records are sequential based on the primary key. However out of lets say 100 records there may be 3 or 4 that are out of order. This is the default view of the database table that I see with phpmyadmin without doing any sorting of any kind. When I use phpmyadmin to sort by userid, everything is in order. So I guess my question is, what is the default order of the records in a mysql database?
Essentially this is why I need to sort - when users retrieve data from the database to a html table, some of the records are not in order of how they were received.
Sorry for my ignorance - I know a little to be dangerous. But the more I do this stuff its kind of addicting and I really like it.
Thanks,
Hef

[fenway]

First, it's UID, not VID -- which stands for unique identifier, which is usually the PRIMARY key. And FK stands for foreign key; it's what you use to establish relationships between tables.

As for your concern about the same date, I meant to use a DATETIME field and insert NOW() into the record -- that would mean that you'd have it sorted to the second (at which point I don't think order matters at the second level!). It's not that using the UID field for this is wrong, it's just unwise -- for example, in InnoDB tables, the UIDs are reused if records are deleted, so your technique won't work anymore.

The order displayed in PHPMyAdmin, or even the mysql command-line output, without an explicit sortorder is usually close to the order inserted, but it gets changed if any rows are deleted, because then the DB engine puts new rows "in between" in the empty spaces in the table file. That's why you can't rely on this order for anything. But as I said in a previous post, your script shouldn't care or rely on either the UID value or the native record order -- that's why you should use other fields to accomplish this task.

Hope that helps.

[khefner]

[!--quoteo(post=340909:date=Jan 29 2006, 01:47 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 29 2006, 01:47 PM) [snapback]340909[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First, it's UID, not VID -- which stands for unique identifier, which is usually the PRIMARY key. And FK stands for foreign key; it's what you use to establish relationships between tables.

As for your concern about the same date, I meant to use a DATETIME field and insert NOW() into the record -- that would mean that you'd have it sorted to the second (at which point I don't think order matters at the second level!). It's not that using the UID field for this is wrong, it's just unwise -- for example, in InnoDB tables, the UIDs are reused if records are deleted, so your technique won't work anymore.

The order displayed in PHPMyAdmin, or even the mysql command-line output, without an explicit sortorder is usually close to the order inserted, but it gets changed if any rows are deleted, because then the DB engine puts new rows "in between" in the empty spaces in the table file. That's why you can't rely on this order for anything. But as I said in a previous post, your script shouldn't care or rely on either the UID value or the native record order -- that's why you should use other fields to accomplish this task.

Hope that helps.
[/quote]

Ahhhhhhh that makes a lot of sense! I will try the DATETIME format and sort by that.

And you explained why my records were out of order. I had deleted some records, and thats where my order problems were located.

Thanks for your help, I really appreciate it!
Hef