[SOLVED] Next available number in seqence

[dlebowski]

This may actually be a question for the MYSQL forum, but I will give it a shot here first.  I have a php/mysql page site that I am putting together and what I need is a mysql query that will query the database, select a specific table, a specific column, and output the next available number in sequence that has not been assigned yet.  For example, this database is a customer database with each customer having their own ID.  Every time I add a new customer, I have to know what ID is available.  I would like the DB query to tell me what the next available number is.  Any help on this would be great.  Thanks in advance.

 

-Ryan

[AndyB]

If the customer_id was an auto-increment field, MySQL would automatically increment it when a new record was added and the value would be unique.

[dlebowski]

In this particular case, I need to define a number for them.  I have another field I created within my table that auto increments that is the primary key.  This field is one I can just manually put a number into.

[AndyB]

http://www.tizag.com/mysqlTutorial/mysqlmax.php

 

Sounds like SELECT max and add one to it for the next customer number

[dlebowski]

Thanks for the link.  This will work with one exception.  If I have 1, 2, 3, 4, 5, 7, 8, 9, 10 all used as customer numbers.  The number 6 is an available number, but with "SELECT max" I will get the number 11.  Is there any kind of query that may recognize that 6 is available?  If not, I will go with the "SELECT max" suggestion provided to me as it will do the trick.

 

Ryan

[AndyB]

Life is simpler if you ignore 'gaps' (like 6 being available) and use MAX + 1 for the next number.

[dlebowski]

I completely agree, but if it's possible it would be better for my client.  They are an auction company and some customers like having 1 digit numbers and other want four digits numbers.  The first person that wants an ID in the 9000's, will result in the next available number as 10,000 before too long.

 

But, I understand that there may be not other choice.

[zfred09]

Couldn't you just check each id until you find one that is available?  Like have a field named available with 1 being available and 0 being taken, then a simple query like

SELECT customer_id FROM db WHERE available = '1';  //didn't check syntax

for the available slots would return the number available.

[AndyB]

Couldn't you just check each id until you find one that is available? 

 

Rather impractical in a table with 10,000 records and 9999 is available. 9999 mysql queries instead of one or retrieving the entire table and stepping through a 10,000 element array to get 10000+1:)

[zfred09]

Very true.

[dlebowski]

So am I S.O.L on this one?

[AndyB]

No.  There's the easy way - already described - and there's the hard way. And the hard way is going to involve a load of coding and even more database-bashing or processing that'll slow down your site.  In the end, it's up to you.

[sasa]

try

<?php
mysql_connect('localhost');
mysql_select_db('test');

$res = mysql_query('select customer_id FROM customer ORDER BY customer_id' );
$j = mysql_num_rows($res);
$i = 0;
while ($i++ < $j and mysql_result($res, $i - 1, 0) == $i);
echo 'Next available number is: ', $i;
?>

[dlebowski]

This query is just giving me the first ID in my list.  In this case it's 1.  Let me do some more testing.  Thanks for giving it a shot.

[cooldude832]

why does their ID matter if you want uniformness make it a 6 digit number like

000001

000011

001111

010092

 

etc etc

[dlebowski]

Sasa,

 

This actually works on my primary key field, but not the ID??  Not sure why that would be, but it does seem to work on the primary key field.

[sasa]

what is name of your field?

[dlebowski]

THe problem is that in the ID field was the value "0".  It didn't like that.  Once I deleted it, it appears to work!  I will try and figure out what I need to add to that code you provided to work with a field of 0 in it.  Thanks again!!!

[sasa]

try

<?php
mysql_connect('localhost');
mysql_select_db('test');

$res = mysql_query('select customer_id FROM customer ORDER BY customer_id' );
$j = mysql_num_rows($res);
$i = 0;
while ($i < $j and mysql_result($res, $i, 0)  ==  $i) $i++;
echo 'Next available number is: ', $i;
?>

[dlebowski]

 

"Warning: mysql_result() [function.mysql-result]: Unable to jump to row -1 on MySQL result index 3 in /test.php on line 11" is the error I get.  I may just not be able to have an ID of zero in there.  If that is the case, I can deal with that.

 

[sasa]

 

"Warning: mysql_result() [function.mysql-result]: Unable to jump to row -1 on MySQL result index 3 in /test.php on line 11" is the error I get.  I may just not be able to have an ID of zero in there.  If that is the case, I can deal with that.

 

$i always >=0 Warning is impossible 

[dlebowski]

Sasa,

 

I just modifed my code to prevent the entry of "0" as an ID.  This is going to work fine.  Thank you very much!  Spent many hours on this one.

 

-Ryan

[chocopi]

EDIT: Nevermind, its been sorted