[UPHPU] Sequentially number records with php/mysql

Jeff Moss 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

begin transaction
  set @customerNum = select value from Admin where variable = 
nextCustomerNum
   update Admin set value = (@customerNum + 1) where variable = 
nextCustomerNum
   return @customerNum
end

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.

-Jeff

David Smith wrote:

><quote who="Brent Wilkinson">
>  
>
>>Well you could do it this way.
>>
>>function getNextUserId()
>>{
>> $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.
>
>--Dave
>
>
>_______________________________________________
>
>UPHPU mailing list
>UPHPU at uphpu.org
>http://uphpu.org/mailman/listinfo/uphpu
>IRC: #uphpu on irc.freenode.net
>
>Sponsored by hostinginferno.com!
>  
>




More information about the UPHPU mailing list