Unique number

NZ_Kiwis

I'm making a page which will add data into my database, I want it to also return a number. This number will be 6 numbers long such as 12356 this will have to be unique? maybe a auto number but can I get this to start from either 00001 or something like that??

cahva

If you add auto-incrementing int value to your database(eg. how you normally use id to identify row), it will be unique for every row. It doesnt matter if the number is eg. 12, 255, 456 in the database as you can format it the way you want with php(hint: [man]sprintf[/man] 🙂 ).

If you dont want it to be sequential, then mark the field as unique so there wont be any duplicates and when adding, you have to check that the number doesnt exist first.

NZ_Kiwis

I don't see how that can help sorry

dagon

unique, auto increment, you can start it at 100000, so it will be 6 digit (till you have enough rows for 7), use mysql_insert_id() to retrieve it after you do each insert (if that's what you want).

NZ_Kiwis

how in MyPHPAdmin do I set up the start value?

dagon

db->table ->operations tab -> Table options box, auto_increment (last option)

NZ_Kiwis

Thanks, if i empty my table it resets this however, why or how can i fix this?

dagon

yes truncating table does that, however you dont have to do that very often do you?. Should only need to set it once, after that it will just increment for you, you can set the value on table creation if you like.

dagon

if you delete rather than truncate, you don't lose the auto_inc value, but its slower. Truncate drops and recreates the table.

johanafm

But if the auto_inc value is specified on table creation, it should be reset to this value on truncate as well.
As for the first reply, he meant that you format things yourself and start the id from 1 instead. You just pad the value with leading characters of whatever sort you like, spaces, 0s... And it's possible to do directly in queries as well with lpad()