[SOLVED] NEXT AVAILABLE NUMBER IN SEQENCE

[dlebowski]

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

[Yesideez]

If the table containing the customers has a customer ID number which is set to auto increment and primary key then the next available ID number would be the last one plus 1.

 

Every time you insert a row (customer) you can get this by using this:

<?php
  //mysql query to insert a new customer
  $nextID=mysql_insert_id()+1;
?>

 

If you want to get this at any other time something like this would work:

<?php
  $fetch=mysql_fetch_assoc(mysql_query("SELECT `id` FROM `customers` ORDER BY `id` DESC LIMIT 1"));
  $nextID=$fetch['id']+1;
?>

[bubblegum.anarchy]

Might as well just:

 

SELECT id + 1

[fenway]

Might as well just:

 

SELECT id + 1

 

*shudder*... these are all very, very evil.

[dlebowski]

Got it to work by doing this:

 

<?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;
?>