Getting Next ID # From DB

run2web

I am creating a form in which the user will enter in new information. I want the next ID Number from an auto_increment field to appear in the ID field. They will not be entering into this field but I want it to show what the ID number is.

How do I do this?

Anon

select max(id_field)+1 from table;

Cheers
Justin

run2web

Thank you, I will give that a test run.

Michelle

Anon

Actually, this isn't quite right. It allows for race conditions. The better way to do it is:

select nextval('sequencename')

then, store it in the form as a hidden (you can show it too) and then use the number that comes back with a literal insert:

insert into table name, id values ('$name',$id);

That way you KNOW that the id you're showing won't get added while by another user while your user is staring the the screen filling in the blanks.

Of course, if your database system just has one user, and will never have two, then both ways work fine. But you probably don't really need to have a database at that point.

run2web

This part confuses me somewhat.
"use the number that comes back with a literal insert:

insert into table name, id values ('$name',$id);"

Sorry, I am just learning this and I'm not sure why I would need to have it inserted. The Database is designed so that it is done automatically but I want the person who will be entering the information into the database to know the next OwnerID #. This is for a cemetery and much of the data entry will be done by one or two people but they want it to be searchable both online and internally.

This is what I have it doing when the information is being added to the DB.

$sql = "INSERT INTO $table_name
(OwnerID, FirstName, LastName, StreetAddress, City, State, Zip, Phone, EMail)
VALUES
(NULL, \"$FirstName\", \"$LastName\", \"$StreetAddress\", \"$City\", \"$State\", \"$Zip\", \"$Phone\", \"$EMail\")
";

//result of mysql_query() function
$result = @($sql,$connection) or die("Could not execute query.");

I just want the user to not have to enter that into the db but allow them to know what the next sequential number is in case they want to write it down on the manual form that they are entering the data from.

Does any of this make sense or have I lost you completely? :-)

Thanks

Anon

Here's the problem. You're trying to tell the user what number is GOING to be inserted before it is. In a transactional system, you can't know what number will be inserted because you aren't the only person who might be getting ready to insert data, right?

Joe and Sue pull up your pre-insert page at the same time. Using the method of currentval+1, they both would get the same number. Let's say the sequence is currently at 3245. They would both see 3246 as the number they were going to insert. Obviously, one of them would NOT get that number. One would get 3246, and the other would get 3247 (assuming no one else logged in and inserted before them, in which case, well, you get the picture...)

The idea is that if you tell someone you are going to insert a record with a particular ID, you want to be telling them the truth. If you tell Joe and Sue that they will be getting ID 3246, then they should get that ID.

So, the solution to our problem is to ask the database for the nextval in the sequence. Asking a sequence for it's nextval should increment a sequence as well as give you the next value. Asking for the current value results in no change.

So, if Joe and Sue both run the same pre-insert page, but now it queries the database for nextval('seq'), then they will both get a different number, 3246 for one, and 3247 for the other.

Since sequences never repeat, you are guaranteed that 3246 and 3247 will not be handed out again.

So, since you already got the nextval, you have to insert it by hand into the field used to hold the sequential id, since a simple insert without the explicit setting of the ID would then get ANOTHER id, after 3247, which would be the wrong one. Note that since you are inserting the id by hand, the trigger used to increment the sequence will not fire, and the sequence will stay where you left it (3247 in our example.)

Whenever you write an app, always ask yourself "What would happen if TWO people did this at the same time?" It will save you HOURS of troubleshooting later on when the system is live and people are using it.

Good luck on your project!