Return after insert w/ prepared statement

[Fooglmog]

			if(!$stmt = $mysqli->prepare("INSERT INTO qpawn_classes (className,classPersonnel,classSalary,classMaintenance,classBranch,classDesc) VALUES (?,?,?,?,?,?)")){$issue = 1;}
			if(!$stmt->bind_param("siiiss", $className, $classPersonnel, $classSalary, $classMaintenance, $classBranch, $classDesc)){$issue = 1;}
			if (!$stmt->execute()){$issue = 1;}
			$stmt->close();

I'm using this prepared statement to insert a row into my DB. When it inserts a row, I have an ID field in the row which is being generated by an auto-increment. I need to get the value of that ID field returned so I can use it in my next section of code... I feel like this should be easy, but for the life of me, I can't figure out how to do it. Any help would be appreciated.

[laserlight]

Perhaps mysqli_stmt::$insert_id is what you're looking for.

[bradgrafelman]

Before you close() the statement object, try accessing the property named $insert_id.

[Derokorian]

$mysqli->insert_id will give you want you need.

[laserlight]

Hmm... I notice that the $insert_id property on the mysqli object does not have the "undocumented" warning as for the mysqli_stmt object, so perhaps it is better to use that.

[WyCnet]

Auto increments are special fields in databases. You only have known access iff you specify it during an INSERT. and iff it is greater (+j where j > 1) than the last incremented index. Auto increments act like the record numbers for random file access, something like a "C/C++" pointer for memory access. Modern databases are structured so you can do things like add columns to a Table, or partition the Table as necessity indicates, without moving data around.

With the above in mind and the fact that asynchronous INSERTS are possible, a database programmer can only be assured of one auto increment index if the columns are unique or uniquely combined keys are being used, otherwise auto increment acts like a secondary non-unique key.

Therefore to use an auto increment index, it must be read from the database after the fact and after database commits if you use a start transaction.

If your Table has no keys then the auto increment index acts like one, but only after you perform a relational query like :

SELECT id, myNAME from someTABLE where myNAME ="Sammy"

and in the subsequent requests having Tables that are related by Informational content, using the same auto increment index,

SELECT Title from customerINFORMATION where customerID = id

works really quickly. This is usally achieved by database programmers by locking associated tables from write access when data is being inserted into the primary relational table. Using this concept there is no need to lock tables or records during updates, for a particular id.

There is a vast difference between relational databases and ISAM databases.

[johanafm]

WyCnet;11006981 wrote:

You only have known access iff you specify it during an INSERT.

Never ever specify value for an auto increment field. If you specify what to put in your auto increment field, don't make it auto increment in the first place.

As for only having "known access", no! You can always retrieve its value from the last row YOU inserted and that's the whole point. The auto incremented value used will be separate for each instance connected to the DB. If you insert a row which gets id 2 and I insert a row which gets value 3, you will still get 2 when you check what last insert id is, while I will get 3. You have access to your AI value and I to mine.

WyCnet;11006981 wrote:

and iff it is greater (+j where j > 1) than the last incremented index.

There is a system in place to handle exactly this (it's called auto increment and works by leaving the specifics of it alone by simply NOT specifying anything for that column).

WyCnet;11006981 wrote:

With the above in mind and the fact that asynchronous INSERTS are possible, a database programmer can only be assured of one auto increment index if the columns are unique or uniquely combined keys are being used, otherwise auto increment acts like a secondary non-unique key.

If your auto increment field is NOT the primary key, then why is it there in the first place?

WyCnet;11006981 wrote:

Therefore to use an auto increment index, it must be read from the database after the fact and after database commits if you use a start transaction.

And from the 5.5 docs

InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction.

Which means that you do not have to commit before accessing the AI value. You could say the AI value is pre-generated for eventual later use on commit. And if you do not commit, that is you rollback the transaction, then the current AI value to be used for next insertion isn't rolled back. In a way you could say that the DBMS is not in the same state after the rollback as it was before the rollback, but that doens't matter. What matters is that the generated keys are unique, and that they are replicable if you have to reconstruct the db from logs or if you replicate data from master to slave. What the actual values are doesn't matter. Moreover, the rollback will not affect the actual values used by other peoples' transactions either. Thus, the state of the database will be consistent and unchanged (its state is what it has stored), so wether the DBMS has the same state or not is irrelevant as long as the consistency of the DB state is guaranteed.

WyCnet;11006981 wrote:

If your Table has no keys then the auto increment index acts like one

No... you either have a key or you do not have a key. You can't NOT have one and while somehow having a key actor.

WyCnet;11006981 wrote:

, but only after you perform a relational query like :

No, it's a key or it's not a key. Either you have a key when you created the table, as per the create table definition, or you do not have one. You may of course also add/remove keys later by alter table statements and their equivalents.

WyCnet;11006981 wrote:

and in the subsequent requests having Tables that are related by Informational content, using the same auto increment index,

Which is a separate issue. You may have table rows that are related on non-auto increment fields. You may have table rows that are related on non-unique fields.

WyCnet;11006981 wrote:

This is usally achieved by database programmers by locking associated tables from write access when data is being inserted into the primary relational table.

No. Since you will insert a new unique value into the AI column, this value should NOT allready exist anywhere else (which is one of the reasons you SHOULD, as in always, use FOREIGN KEY constraints). Obviously, inserting a new row into a table to which other tables may relate, but is not being related to by those tables, there should exist no relation upon insertion. Given this constraint and NOT mucking about with choosing your own values to insert into an AI column, there will NEVER be any related rows anywhere else, and thus there is no need for locking any related tables.

WyCnet;11006981 wrote:

Using this concept there is no need to lock tables or records during updates, for a particular id.

Err, I really don't get what your after with this combination: "by locking associated tables there is no need to lock tables".
If you need to lock a table and lock it, then there will be no need to lock it since it's allready locked?

WyCnet;11006981 wrote:

There is a vast difference between relational databases and ISAM databases.

In the context of all this, mainly the use of foreign key constraints. And if you forgo the use of them you might as well not use InnoDB to begin with and stick with MyISAM. Sure, there are other differences, but if you do not ascertain a consistent database state to begin with, there is no point to use other thing that will keep it in a consistent state as far as they go, since the state either has a guarantee to be consistent or it doens't.

[WyCnet]

johanafm;11007323 wrote:

...
As for only having "known access", no! You can always retrieve its value from the last row YOU inserted and that's the whole point. The auto incremented value used will be separate for each instance connected to the DB. If you insert a row which gets id 2 and I insert a row which gets value 3, you will still get 2 when you check what last insert id is, while I will get 3. You have access to your AI value and I to mine.
...

Ok. How does one check what last insert id is, for the non-specified auto-incremented column if it was not specidied in the Query?

Is this how it is done:

INSERT INTO TableA (id, Name) VALUES (DEFAULT,"myNAME") 

rather than:

INSERT INTO TableA (Name) VALUES ("myNAME") 

I always thought that mySQL returns a success value on an inserted row, and not the values inserted. Is this correct?

[WyCnet]

I am thinking that the point is two queries must be used in order to obtain the AI index generated from the first query.

[Weedpacket]

WyCnet wrote:

I am thinking that the point is two queries must be used in order to obtain the AI index generated from the first query.

You're right, it is: one to make the insertion, and one to retrieve the autoincremented value used. Fortunately, the DBMS driver provides an interface specifically for making the second query, partly because that interface varies from DBMS to DBMS ([font=monospace]SELECT lastval()[/font], [font=monospace]SELECT @@IDENTITY[/font], [font=monospace]SELECT LAST_INSERT_ID()[/font]...).

[WyCnet]

Johanafm, I could be wrong so correct me. This scenario has four tables each with an AI index. The lead table has only a name and an id. The other three tables contain Information related to the Name. The database is empty, and the first Name is inserted. Many Users are adding their Names around the same time. Simply it does not make sense to me to have in the other three tables two id's, one for the NameID and one for its AI index, I was trying to suggest a method where all 4 tables have the same AI index, which can be guarenteed by locking the tables when a Name is inserted. The tables are not only relational, but three of them are dependant on the Name tables and its AI index. In other words the four tables are auto-incremented at the same time. This is a special case for static updatable Information. Is this an inappropriate use of InnoDB?

[Weedpacket]

WyCnet wrote:

This is a special case for static updatable Information. Is this an inappropriate use of InnoDB?

Category error. MyISAM and InnoDB are both engines used by MySQL to implement relational database storage. The difference between them is that InnoDB supports transactions and MyISAM doesn't, so InnoDB can take steps to ensure that corruption doesn't occur when multiple people try to alter data in the same table at the same time.

[WyCnet]

johanafm;11007323 wrote:

...
Which means that you do not have to commit before accessing the AI value. You could say the AI value is pre-generated for eventual later use on commit. And if you do not commit, that is you rollback the transaction, then the current AI value to be used for next insertion isn't rolled back. In a way you could say that the DBMS is not in the same state after the rollback as it was before the rollback, but that doens't matter. What matters is that the generated keys are unique, and that they are replicable if you have to reconstruct the db from logs or if you replicate data from master to slave. What the actual values are doesn't matter. Moreover, the rollback will not affect the actual values used by other peoples' transactions either. Thus, the state of the database will be consistent and unchanged (its state is what it has stored), so wether the DBMS has the same state or not is irrelevant as long as the consistency of the DB state is guaranteed.
...

I may have confused a local context and a global context. What I meant was the AI index is not generally available until a commit is performed. However I stand corrected on the point that the AI index is available to a single context during transactions.

Thanks.

[johanafm]

Correct. A transaction not commited will not be visible by issuing a SELECT MAX(id) FROM table for any other user. Thus, in short, each user can issue INSERT INTO user(name) VALUES('thename'); and then use PDO::lastInsertId() or mysqli::$insertId to check its own value from the last insert and then use this id to perform the 4 inserts into the related tables.