managing auto increment field in mysql

Hi guys,

I have created a table with a field call 'no' which i set to auto increment. The purpose of this field is to let me know how many clients i actually have in my database. Everytime i add a new cliet, i would put 0 in the no field so it will automatically put the last value for me..

but now i have a prob.. when the time i delete some of the row, the db seems to got prob... first prob is when i add a new row, it seems to replace the row i just deleted row but with different no.

For instance, i delete the row with no=1 and i wanna insert a new row that suppose to be the 23rd row... when i put the value for no field 0, it does inserted into db with the value 23 in no field. But the prob is the info that inserted does not seem to be correct. A part of the info in my newly inserted row seem to be the info of the row which i delete. It might sound strange but it really happened... may i know why this occured ?? is it i am not suppose to put 0 in the no field when insert new row ??? if that is the case, what i should put the value of no in the php code when i wanna insert a new row ??

another question i wish to ask is, is there anyway i can make it update the no field by itself if something is deleted ?? for instance i deleted the first row with no field value is 1, then the second row will replace the first one and the value of no field from the second row will automatically change to 1.. same goes to the other rows... is this possible ???

like in my case, if i delete a large number of files randomly, the auto increment number in my no field will start to get very messy. i just wish the auto increment works as what i expect..

please advise ...

Thanks.

are you just trying to count how many users?

You should never try to upate an auto_increment field. It does nothing but auto_increment. And you should set it to NULL rather than 0 when you insert a new row. If you want to get a count of your users, you should do "SELECT COUNT(*) FROM table" rather than messing with auto_increment values. Auto_increment fields are used to generate unique keys and really aren't meant to be used for generating counts, being edited, etc.