strange behavior while inserting rows into a table

[Aoshi]

I have a mySQL table called users.
i'm inserting rows to it through a webpage.
two behaviors i'm noticing.
1) ...sometimes..after i've inserted a couple of new rows, the rows will show up in reverse order. The first column of my table is a user_id column with the auto_increment attribute. so...sometimes...I havne't been able to consistently reproduce this...but sometimes i'll add a few rows and then look at the table and it'll look like:
user_id[nbsp][nbsp]name[nbsp][nbsp]
5[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]james
4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]jimbo
3[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]jimmy

any ideas?

the second behavior is documented in my mysql book, but it doesn't offer a resolution....perhaps there isnt' any?
so my first column is an auto_incremented column.
when i delete a row from the table, the user_id value that i deleted is never used again.
so if i had 5 users in my table listed 1 through 5....and i then deleted each row so that my table is empty, and then i added a new user...that new user will show up as user 6.
is there any way to get it to start over at 1?
the only way i found is to go into mysql and issue this query "DELETE FROM users";
that will allow me to start back at 1 for the user_id...but obviously..that kills all the existing records.

Thanks!

-James

[dank]

I believe your two problems are somewhat related, although I don't know if there's an answer to the second one.

When you say the inserted rows are in reverse order, I assume you mean just the order they appear in the database, not that their actual numbering is wrong?[nbsp][nbsp]If so, that's the same thing I've noticed.[nbsp][nbsp]MySQL appears to "plug the gaps" in the numbering sequence if there are openings from having deleted something previously occupying that spot.[nbsp][nbsp]The only workaround I know of is to *always* use an ORDER BY statement in search queries, modifying the sort order as needed with different input.[nbsp][nbsp]If nothing is to be added to the "default" sort order, then simply sort by the auto-incrementing ID field.[nbsp][nbsp]That will ensure that you at least get the results returned in numerical order.

Dan

[Aoshi]

oh man...totally forgot about the ORDER BY clause.
i was up way too late last night! ...thanks!

i'm wondering if the numbering thing is just an inherent nature of having a column with the auto_increment attribute.
argh...i hope not.

-James

[Rich]

....and i then deleted each row so that my table is empty, and then i added a new user...that new user will show up as user 6.
is there any way to get it to start over at 1?
the only way i found is to go into mysql and issue this query "DELETE FROM users";
that will allow me to start back at 1 for the user_id...but obviously..that kills all the existing records. This really shouldn't matter because you already killed all the existing records. The later versions of MySQL do not reuse numbers for autoincrement columns. The only way around this and to gain control of precisely how autoincrement functions, is to create your own table used for autoincrementing numbers and use the auto_increment() function.

Rich