Pull next available record id from database

TCovert

IS there a function of some sort that I can use to get the next available id for a record in the database.

For instance, I have a field in the database named "id" this field is auto_incrementing. So, when I go to add a record to the database, I want to look ahead of it, and see what its record id will be(the next available id).

Any ideas?

MrAlaska

That looks like trouble to me. Suppose you get the next available number, do something with it, then somebody else makes an insert in the meantime. You would need to make a pessimistic SQL statement to guard against that which raises the risk of failed transactions.

I think it would be better actually inserting the item using dummy values if you need to, retrieve the id number, then do whatever it is you need to do with it.

I can't imagine actually needing the ID number before making an insert, but my brain is not working that well today.

TCovert

Why I need this function:

When I add a record to the database, it also outputs that data to an html file. And I want to name that file "storyID.html". But, the story doesn't have an ID until it is added. However, if I can grab the next ID available, that WILL be the id that the story will have.

So, anybody have a clue as to how I do this?

MrAlaska

To get the next ID you would use a sql statement (assuming you increment by 1):

select max(id)+1 from tablename;

It would still be better to insert your story first, collect the ID of the story you just entered, update the table with the new title, then output the HTML, unless you are the ONLY one who will ever be adding a story in which case you will maintain integrity by only allowing one user to insert data.