there can be only one auto column and it must be defined as a key

[eluser]sikkle[/eluser]
Does anyone here already deal with simulation of two or three auto_increment field ?

like

unique id starting at 1
unique serial no starting at 100 000 001
unique client public number starting at 100 000 000 001


How do people deal with this ?

Thanks !

[eluser]Phil Sturgeon[/eluser]
Do it with MySQL commands in PHP. Set them all as unique keys and before you enter any new entry get the last number, increase it and put it back in.

It MIIIIGHT return an error if somebody else inserts the same command in the same fraction of a milisecond, but you can just put it in a loop that tries 3 or 4 times before failing. Problem solved!

[eluser]sikkle[/eluser]
how the get the last entry with a lightweight query ?

[eluser]linuxbz[/eluser]
[quote author="sikkle" date="1185952426"]how the get the last entry with a lightweight query ?[/quote]

How about:

Code:

SELECT max(FieldName) AS LastEntry from table

[eluser]richthegeek[/eluser]
just wondering, surely the following would be quicker

Code:

SELECT (FieldName + 1) AS FieldName FROM table ORDER BY FieldName DESC LIMIT 1

[eluser]kgill[/eluser]
If you're trying to use three auto_increment type fields in the same table you need to rethink the table design. Each time a new row is created each one of the ID's is going to bump up at the same time, so they'll essentially always be the same number; for any given person your unique serial number is just 100,000,000 + unique id, you're never going to have a case where unique serial number doesn't increment at the same time as unique id.