Find Next Available Id

Hi,

Is there a way to query the database to find the next available id number?

Cheers,
Adam

 

If you are just trying to write a new item into it I think it automatically just adds it to the newest one otherwise I don't know of anyway to find it.

 

No, its a long story but i just need the next available number.

 

function get_insert_id($table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $table";
    return mysql_num_rows(mysql_query($q)) + 1;
}

Code (markup):

hope this helps you

 

thank you very much

 

mysql_num_rows()

 

I have tried that function but it does not seem to be bringing back the correct number? Is it correct?

 

i use a function like this.. i take from the table index itself

function next_id($table) {
$sql = 'SHOW INDEX FROM '.$table;
$result = mysql_query($sql);
return $result[Cardinality];
}

 

If you've just performed an insert query you could also do:

$available = mysql_insert_id() + 1;

PHP:

assuming id is auto_incr and next available is +1

 

No sorry i have not just performed an insert.

 

If this is a publicly accessed page, this is probably not a good way to do this. If you have 2 concurrent visitors, and you are pre-seeding a value based on the next available, you're very likely to get crossed id's, or collisions.

If you need the next ID, and need to guarantee it will be accurate when you use it, best bet is to make an insert into the table, then get the insert_id(), and then do an update when you actually want to put something in there.

It is an extra step, but will prevent some potentially ugly problems resulting from id collisions.

 

Thanks for you concern but it will not have multiple visitors as this did cross my mind.

 

last_insert_id gets the last insert from the current open mysql resource/connection. You can't get the insert_id without making an insert on the same mysql resource/connection.

To get the id, do something like this...

SELECT id FROM my_table ORDER BY id DESC LIMIT 1;

You can then just add 1 to the value returned...

 

You should probably just setup your database so that the id field is set to be auto_increment, this way you can insert without including an Id and it'll automatically autoincrement to the next value. Would save you a heck of a hassle than trying to figure out what's the last ID then going from there.