[SOLVED] AutoNumber

[jkkenzie]

Hi!

Say, i have in my mysql table a field named "id". It is an autonumber field and it is the primary key.

 

I woul like to know if it is possible to get the next autonumber which is going to be used to save a record.

 

I need to use the id field number to save something on a directory.

 

regards,

Joe

[runnerjp]

why not get the number of results... then +1

[jkkenzie]

The number of what results?

Sounds like a good idea.

[runnerjp]

as in if you search for how many results there are in your db soo

$num = mysql_num_rows($query);

 

will get you how many rows there are and then just add 1 to it to get ur next number

[PFMaBiSmAd]

Because of the possibility of multiple concurrent operations, you cannot retrieve the current highest number, add one, and actually use it. If you are at the point of needing to use the next auto increment number, insert the row in the database (even if you must insert some empty fields and go back to update them later), then get the actual id that was used in that insert operation.

[The Little Guy]

If you are getting this number after an INSERT method, you can do this:

 

mysql_query("INSERT INTO table_name (`col1`,`col2`) VALUES ('data1','data2')");
$cVal = mysql_insert_id();  // the insert number from above
$nVal = mysql_insert_id() + 1;  // the next insert number from above
echo '
Last insert id: '.$cVal.'<br>
Next insert id: '.$nVal;

[PFMaBiSmAd]

Only the last insert id will be 100% valid. You cannot guarantee that the number you get by adding one to it will be that same value at any point in the future.

[The Little Guy]

I WILL be the next insert id, doesn't matter if it has been inserted or not, it still will be after what you inserted, thus being the next insert.

[.josh]

I WILL be the next insert id, doesn't matter if it has been inserted or not, it still will be after what you inserted, thus being the next insert.

 

No, it might not be.  You are assuming that you are the only person accessing and running the script.  If you have 10 users, 100, 1000, etc... all doing stuff on your website, you have no guarantee that one (or more) didn't do something that updated your db in that time. 

[PFMaBiSmAd]

Multi-tasking operating systems use interrupt driven time slicing to execute multiple tasks concurrently. Multiple concurrent requests for web pages that perform database queries cannot guarantee the order in which the database queries are executed. You cannot guarantee what an auto increment id is until after it has been assigned.

[The Little Guy]

so... your saying that mysql_insert_id() doesn't show the id that you inserted?

 

I don't think you under stand my point.

[PFMaBiSmAd]

so... your saying that mysql_insert_id() doesn't show the id that you inserted?

No one stated that.

 

You stated (and showed code) that the next inserted id WILL be the last insert id plus one.

 

I would suggest you carefully reread what has been posted.

[The Little Guy]

It doesn't say that the next id MUST NOT be already taken.

[.josh]

mysql_insert_id() will show the last auto_inc'd id from your personal connection stream.  It is dependable in that aspect, yes.  But you can't just add 1 to that and assume that the next one is available, because it might not be.  Someone else could have gone to a webpage on your site that inserted another row in that time, and thus, the "next available" one would really be mysql_insert_id() +2.  Get it?

[The Little Guy]

mysql_insert_id() will show the last auto_inc'd id from your personal connection stream.  It is dependable in that aspect, yes.  But you can't just add 1 to that and assume that the next one is available, because it might not be.  Someone else could have gone to a webpage on your site that inserted another row in that time, and thus, the "next available" one would really be mysql_insert_id() +2.  Get it?

 

Completely under stand, and understood before my post.

[The Little Guy]

This may be as close as you can get:

 

<?php
include './incl/db.php'; // Include your database connection
$tablename = "users"; // Table name of get increment
$qShowStatus = "SHOW TABLE STATUS LIKE '$tablename'";
$qShowStatusResult = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() .  $qShowStatus );

$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];

echo "next increment number: [$next_increment]";
?>

[.josh]

Again, same thing TLG: If I run that script, I cannot guarantee that the next number will be that, by the time I get around to using it.  Any number of people can end up doing any number of things that will change that number by then. 

[The Little Guy]

So basically it is impossible, to find the next available id.

 

It could work fine, depending on how he uses it...

 

It could be for an admin system with only one admin, and one person allowed to edit that table.

[PFMaBiSmAd]

Please read the first post. He wants to use the number to "save something on a directory"

 

And he was already told the proper way to do this so that it will work under all circumstances without needing to create a lot of special case conditions that cannot be guaranteed to work.

[jkkenzie]

thanks guys!

i will go for :

mysql_query("INSERT INTO table_name (`col1`,`col2`) VALUES ('data1','data2')");
$cVal = mysql_insert_id(); //I WILL USE THIS ONE BECAUSE I NEED IT DURING THE THE INSERT OPERATION ONLY