[UPHPU] Sequentially number records with php/mysql
jeff at opendbms.com
Wed Nov 10 12:52:59 MST 2004
Postgress has sequences, Mysql doesn't, (I think Oracle has sequences
also, not sure). A sequence is an atomic way of doing this. I did a
project with Sybase, which doesn't have sequences, where I created an
atomic transaction to do this. It went something like this
set @customerNum = select value from Admin where variable =
update Admin set value = (@customerNum + 1) where variable =
The only reason to do it this way is to have better control over how the
numbers are assigned. I think most if not all auto_increment
implementations will creat a random gap every now and then. Having the
numbers in order is not all that useful though. I would just use the
auto_increment feature. There is a variable you can access which will
give you the last value of the primary key created on the last insert
statement, you could just insert a blank row to reserve.
David Smith wrote:
><quote who="Brent Wilkinson">
>>Well you could do it this way.
>> $result = mysql_query("select max(user_id) from user") or die("Unable to
>>get next user id");
>> $id = mysql_result($result, 0) + 1;
>> return $id;
>>This is clunky though and the autoincrement way is the best and easiest
>>way to do it. I would also probably just add a extra field in the db and
>>mark a order inactive or deleted instead of actually deleting it.
>Not only is this clunky, it is incorrect. This code is subject to a race
>condition, and can lead to duplicate user_id values. I will leave the
>details to the readership, but if you're curious, please inquire.
>UPHPU mailing list
>UPHPU at uphpu.org
>IRC: #uphpu on irc.freenode.net
>Sponsored by hostinginferno.com!
More information about the UPHPU