Mysql - Next Insert ID

is there a way to get the next insert id, without doing this?

SELECT MAX(id)+1 FROM table AS next_id

know thats prob dont work, but it looks good eh?

Hi,

You can use mysql_insert_id to obtain the id of a newly inserted row. Is that what you’re looking for?

i need the next insert ID, before i insert the row, my codes backwards, just trust me

hmm, i cany find anything like mysql_next_id, so im guessing im best of doing a

SELECT id as next_id FROM table ORDER BY id ASC LIMIT 1

$next_id = next_id + 1;

Will not work. Autoinc will increment the ID and then discard it if an insert fails. That is why you get numbers missing from the ID sequence ALWAYS. So taking last ID and adding 1 is no guarantee that it will equal the next ID that willl be issued. Also, last ID is per connection, so in a multi user environment it will not show the last ID issued when someone else adds a record just after this user.

You will have to create your own procedure to issue and allocate ID nos. Create a table to store the last number issued in and use that as the basis for generating the next one. Will require Read/Write table locks and transactions.

sql_nextid()
This is a useful command for determining an id number for an item that you’ve just inserted, ie auto-incremented field.
Code:
$newid = $db->sql_nextid()

http://www.phpbb.com/phpBB/viewtopic.php?t=137321

Does that help?

hmmm, not really helpful :frowning:

I think im gonna stick with my method, as there is only ever one conenction to the database, form an admin login, so im pretty sure itll be ok.

what about this, i thoughtj about it for a while but it seems to be ok…

SELECT MAX(auto_id_field)+1 AS next_id FROM table FOR UPDATE

… anyone got a comment? In theroy it gives the next ID field, and using FOR UPDATE will lock it from multiuser access yes~?

Actually if I’m not mistaken there’s a function in MySQL (not in PHP’s mysql extension) that returns the CURRENT insert id for a particular table. You can just add it by one.

However, why do you want it? I think it’s useless because it’s not usable. You MUST NOT use the returned value to insert a new record. Why? Because there is concurrency. If there are 100 users accessing your page then there may likely be someone inserting another record between your SELECT and INSERT. That’s why you need to use the autogenerated ID and know what ID you got afterwards.

Of course you can do a LOCK TABLE table WRITE but this is actually unnecessary.

The point is, that this for for a small project im making, and it produces the nescomer ID by getting the next insert ID, and because it will only be accessed from one user, or at least only one user could add a new user at any time, then i dont need to worry about that, and the lock table idea os very good :slight_smile:

Hi,

you may use SHOW TABLE STATUS:


$myTable = "table";
$result = mysql_query("SHOW TABLE STATUS");
while ($row = mysql_fetch_assoc($result)) {
  if ($row['Name'] == $myTable) {
    $next_insert_id = $row['Auto_increment'];
    break;
  }
}
echo "<br />(most presumably) next ID: $next_insert_id</br />";


If you are going to use Max(id) + 1 then you must not make it an auto-inc field: calculate the next no and save it with the rest of the data in the insert query. If you don’t then you WILL end up with errors at some point even if it is a single user system.

BTW may I ask why would you want to use next id manually and not let MySQL handle it for you?

It’s just sooo weird as I have never experienced any need for this functionality for some years…

You create a table with one field that you store the last ID in. When you want to issue the next id you

  1. Lock Last Issued ID table for Read and Write
  2. Read Last Issued ID
  3. Add 1 and use as new ID in Insert query
  4. Test for successfull Insert
  5. If successfull, update ID table with new ID as Last Issued ID
  6. Release Table Locks

This procedure does not require transactions so is suitable for mysql and web. In a transaction enabled environment one would definitely use them.
The table lock must not be released untill after you have tested the insert and updated the last issued id. If for any reason the procedure fails (eg connection failure causing abnormal termination), the locks must remain in place to prevent another user from issuing the same ID. Not a problem as you would need to investigate the cause of the failure anyway.

There are all sorts of situations where one would want tight control over a numeric sequence, and in particular would not want there to be any gaps in the sequence: Project or Job numbers, Invoice or Account nos, Activity logs, complex Action Plans, Inventory nos, to name just the obvious. Autoincs are just too arbitrary and leave too many gaps in the sequence.

:blush: And of course I forgot the most important point.

Autonincs are PURELY for internal database use. They provide a unique identifier for every record that is independent of the data it contains. This means that one can edit the data without then having to update all related tables or child records.
They also facilitate joins as integer indexes are much faster than other data types.

Autoincs have no intrinsic meaning outside of the database and should NEVER be revealed to users, or given a ‘real’ meaning and used as part of the data that the database contains. :good:

But of course we are all guilty of doing just that. :devil:

>There are all sorts of situations where one would want tight control over a numeric sequence, and in particular would not want there to be any gaps in the sequence: Project or Job numbers, Invoice or Account nos, Activity logs, complex Action Plans, Inventory nos, to name just the obvious. Autoincs are just too arbitrary and leave too many gaps in the sequence.

I agree with you completely, but that does not answer my question. In the cases you described, one would NOT use autoincrement. But Infizi USES autoincrement but doesn’t use autoincrement’s native capability. Why would anyone do this? If you just need MAX(id)+1 you don’t need to set the column as autoincrement…

Infizi is puzzling me so hard.

I dunno! Maybe he is asking the users if they like that number or want to wait for a different one??

>I dunno! Maybe he is asking the users if they like that number or want to wait for a different one??

That’s a cool idea! You’re a genious!

You should mail Google employees about it! Everytime they index a page on the WWW they’ll send an e-mail to the site’s webmaster asking whether they like the Autogenerated ID for the page(s) or they want to wait for next crawl to get “pretty” IDs? Looks fun to me! :wink:

LOL, no basically ina customer database, i use the autoinc column to create their userID. makes it easy…

the hard part, is that when the form opens they need to know the ID so i can be issued to a customer before the insert command, so i need the next id as the customer id.

Following that, if all fields are not filled in it rejects the attempt to add a customer and keeps that number form the post form, then uses it on the insert.

I know its backwards, but its how it needs to work…

So, im liking the idea of the LOCK function for total security :0

Thanks for your comments, ideas etc etc, all well appreciated.

actually, after reading about lock tables, im going for transactions. same amount, if not less code, and better reliability. Thanks for the idea