RE: [PHP-DB] Auto-increment questions...
OK. The problem is I don't want the next highest number. There are gaps in the UID sequence. I need to find the next UNUSED number in the sequence which is rarely the highest number. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 12:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: RE: [PHP-DB] Auto-increment questions... If its an autoincrement, the next highest number will be assigned by the db. There is no need to query the db to find it. Simply insert the record and leave the id field out of the insert statement. bastien From: NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Auto-increment questions... Date: Tue, 5 Oct 2004 11:58:41 -0500 I am working on a database application for Unix user accounts. I want to be able to have a system that will provide me the next available numeric user ID. I have created a 2 column index table that simply includes UID and user name. This field is autoincrement and the primary key for the table. Currently this field only contains a very small sample of users. Basically, I have numeric IDs 3000, 3001, 3008, and 3028. My problem is that I am not sure how to query this table to get the next available numeric ID which is 3002. If I do an insert into this table without specifying a value for the auto_increment column it comes up with 3029. Is there a way to query and/or insert such that it comes up with the next unused numeric value? Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Scan and help eliminate destructive viruses from your inbound and outbound e-mail and attachments. http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU =http://hotmail.com/encaHL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN(r) Premium right now and get the first two months FREE*. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Auto-increment questions...
As a record ID, you shouldn't really worry about the numbers being sequential without gaps - they're just a way of assigning a unique number to a record in the db. However, if you need the record's id to be sequential because you're going to use it as a piece of actual data to be displayed to your users then maybe you may want to consider adding a field to the database that you can manipulate and check for sequence and gaps. If your requirements specifically state that the record ids must be sequential then I guess you don't have a choice. But if having sequential ids is your personal preference, you may find that you're making life more difficult on yourself than it has to be. Also, I know this topic has come up in the past and has been discussed at length. You may want to check the archives to see what others have posted. Rich -Original Message- From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 2:00 PM To: Bastien Koert; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Auto-increment questions... OK. The problem is I don't want the next highest number. There are gaps in the UID sequence. I need to find the next UNUSED number in the sequence which is rarely the highest number. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 12:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: RE: [PHP-DB] Auto-increment questions... If its an autoincrement, the next highest number will be assigned by the db. There is no need to query the db to find it. Simply insert the record and leave the id field out of the insert statement. bastien From: NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Auto-increment questions... Date: Tue, 5 Oct 2004 11:58:41 -0500 I am working on a database application for Unix user accounts. I want to be able to have a system that will provide me the next available numeric user ID. I have created a 2 column index table that simply includes UID and user name. This field is autoincrement and the primary key for the table. Currently this field only contains a very small sample of users. Basically, I have numeric IDs 3000, 3001, 3008, and 3028. My problem is that I am not sure how to query this table to get the next available numeric ID which is 3002. If I do an insert into this table without specifying a value for the auto_increment column it comes up with 3029. Is there a way to query and/or insert such that it comes up with the next unused numeric value? Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Scan and help eliminate destructive viruses from your inbound and outbound e-mail and attachments. http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994; DI=1034SU =http://hotmail.com/encaHL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN(r) Premium right now and get the first two months FREE*. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Auto-increment questions...
What determines the gaps??? On Oct 5, 2004, at 12:59 PM, [EMAIL PROTECTED] wrote: OK. The problem is I don't want the next highest number. There are gaps in the UID sequence. I need to find the next UNUSED number in the sequence which is rarely the highest number. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 05, 2004 12:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: RE: [PHP-DB] Auto-increment questions... If its an autoincrement, the next highest number will be assigned by the db. There is no need to query the db to find it. Simply insert the record and leave the id field out of the insert statement. bastien From: NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Auto-increment questions... Date: Tue, 5 Oct 2004 11:58:41 -0500 I am working on a database application for Unix user accounts. I want to be able to have a system that will provide me the next available numeric user ID. I have created a 2 column index table that simply includes UID and user name. This field is autoincrement and the primary key for the table. Currently this field only contains a very small sample of users. Basically, I have numeric IDs 3000, 3001, 3008, and 3028. My problem is that I am not sure how to query this table to get the next available numeric ID which is 3002. If I do an insert into this table without specifying a value for the auto_increment column it comes up with 3029. Is there a way to query and/or insert such that it comes up with the next unused numeric value? Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Scan and help eliminate destructive viruses from your inbound and outbound e-mail and attachments. http://join.msn.com/?pgmarket=en-capage=byoa/ premxAPID=1994DI=1034SU =http://hotmail.com/encaHL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN(r) Premium right now and get the first two months FREE*. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Auto-increment questions...
On Tue, 5 Oct 2004 12:59:55 -0500, NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED] wrote:
OK. The problem is I don't want the next highest number.
There are gaps in the UID sequence. I need to find the next UNUSED
number in the sequence which is rarely the highest number.
As far as I know, you'll have to write a little function to do that
for you. I don't know of a built-in database function that does what
you require. Assuming your application is PHP, Something such as this
might work (pardon the shorthand):
function getNextMissing ()
{
$result = select id from the_table order by id;
while (list ($id) = mysql_fetch)
{
if (isset ($lastId) ($lastId $id - 1))
{
$returnVal = $lastId + 1;
}
$lastId = $id;
}
return $returnVal;
}
You'll need to modify the function to handle there being no gaps in
the sequence (i.e. return $id + 1 if you never hit the logic in the
if), or for that matter, there being no id's. Otherwise, I think it's
a good starting point. Good luck!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Auto-increment questions...
On Tue, 5 Oct 2004 14:18:55 -0700, Andrew Kreps [EMAIL PROTECTED] wrote:
if (isset ($lastId) ($lastId $id - 1))
{
$returnVal = $lastId + 1;
}
Sorry, I left a bit of a bug in there. You should exit the loop at
this point, if you've found a gap.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Auto-increment questions...
NIPP, SCOTT V (SBCSI) wrote: I am working on a database application for Unix user accounts. I want to be able to have a system that will provide me the next available numeric user ID. I have created a 2 column index table that simply includes UID and user name. This field is autoincrement and the primary key for the table. Currently this field only contains a very small sample of users. Basically, I have numeric IDs 3000, 3001, 3008, and 3028. My problem is that I am not sure how to query this table to get the next available numeric ID which is 3002. If I do an insert into this table without specifying a value for the auto_increment column it comes up with 3029. Is there a way to query and/or insert such that it comes up with the next unused numeric value? Why on Earth would you want to do that? Do you have a genuine reason? The whole point of auto_increment columns is that you do not need to reuse the ID numbers. Using an UNSIGNED INT column, there can be 4294967295 values. Do you really expect to go over that? If so, an UNSIGNED BIGINT can have 18446744073709551615 values. If you think you're going to go over that, you're f'n crazy. Do not worry about holes in the sequence. If you application depends upon sequential numbers, then you've written it wrong and it's using the auto_increment column for the wrong purpose. There are good reasons, too. If you delete user 3000 and the next user gets that UserID, now anything left over from the old user 3000 is now related/linked to the new user 3000. How do you even tell there was an old user 3000? Wouldn't you want to be able to detect that? I'm sure there are other reasons, too... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
