php pdo last inserted id
PHP PDO Last Insert Id [closed]
Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.
With mysqli the last inserted id can be retrieved from a prepared-statement object, e.g.:
However with PDO this has to be done at the database level. E.g:
I though that PDO was meant to replace mysql/mysqli, why was this design decision taken? What if two insert commands run at the same time? Because the inserted id is not scoped to a given command may it retrieve the wrong id?
1 Answer 1
The MySQL LAST_INSERT_ID function is maintained at the session (database connection) level. Not at the individual prepared statement. That function returns
the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.
«I thought that PDO was meant to replace mysql/mysqli»
I think PDO was meant to be an alternative to the mysql_ and mysqli_ interfaces. PDO is quite a bit different than either of those. For one thing, there are PDO database drivers for many databases other than MySQL (such as DB2, Oracle, PostgreSQL, SQL Server, et al.).
In contrast, the mysqli_ interface specifically supports MySQL (and MariaDB versions
«Why was this design decision taken?»
I think the PDO design more closely follows the pattern of the LAST_INSERT_ID function in the MySQL database itself. PDO and mysqli both provide «wrapper» functions as a convenient way of executing and retrieving the resultset from a SELECT LAST_INSERT_ID() statement.
I believe the developers of PDO intended it to be a lightweight and consistent interface for multiple databases. Creating a drop in replacement for mysql/mysqli was not a primary design goal.
«What if two insert commands run at the same time?»
It’s not possible to run two INSERT statements «at the same time» on a single MySQL database session. They would run consecutively.
In MySQL, to retrieve the AUTO_INCREMENT value from the first INSERT statement, that first statement would need to be followed by a SELECT LAST_INSERT_ID() before the second INSERT statement was executed.
«Because the inserted id is not scoped to a given command may it retrieve the wrong id?»
This is by design of the MySQL LAST_INSERT_ID() function. It’s not «scoped» to a particular statement. The value returned by that function is maintained for the session.
And mysqli interface provides a wrapper for the LAST_INSERT_ID() function at the session level.
As far as making a method/function available for a prepared statement, I’ve never used that.
I suspect that on the prepared statement, the insert_id function is really a wrapper/passthru to a function at the session (or link, as mysqli refers to it.)
If that’s the case, that means that the call to insert_id needs to be made immediately following the execute of the prepared statement INSERT, before any other INSERT statement is executed.
(I’m not really sure about any of that. It’s possible that the mysqli developers implemented code that automatically runs the SELECT LAST_INSERT_ID() statement immediately following the successful execution of a prepared statement INSERT, and cache that, before the execute call returns. Then, when the insert_id function is called, it returns the cached value. I don’t really think that’s the case. But it is possible. If that is the case, I still wouldn’t have my code rely on that behavior. I would want to get the LAST_INSERT_ID() immediately following the INSERT, before I ran any other statements on the MySQL session.
Retrieving last inserted iD then uploading to MySQL. #PDO #PHP
I have a form that uploads Textarea Information and an Image to MySQL simultaneously.
This is my current code for the isset that uploads the information to the Function and/or query:
Here is the Insert_Update that is uploading the textarea:
More Information within the database which I doubt it’ll be useful.
posts table : where the textarea information will be located.
*post_iD | message | uid_fk | ip | created |*
user_uploads table : Where the image location will be located.
*image_iD | image_path | uid_fk | image_id_fk*
NOTICE: image_id_fk should equal post_iD ( which is obvious ).
How is the lastInsertId() ; suppose to be used?
EDIT 1: After upload completes the inserted value for the image_id_fk equals 0, instead of the post_iD value. Any ideas for this reason?
2 Answers 2
It can be used right after you successfully execute an INSERT statement. However, you have to always check whether the execution was successful (or are there affected rows) because there can be last insert ID from previously executed statement.
For example: You have a function that creates a new post. This function on success can return the last insert ID value, so you can display that value to user. Like:
Update:
Here is my suggestion to your problem: lastInsertId always retuns 0 :
PHP MySQL PDO: get last inserted ROW not ID
I have currently a script to insert new data automatically when using the form to add a new element.
Logic of course. Now I am only wondering if this might cause any problems in case a table has no ID field and no auto increment field.
Aside the fact if this means there is a bad database structure used. I want to make sure that this routine works in any situation. Good database or a bad database structure. It should not matter.
What I mean is that I would like to get back the row with data so I can search for it.
Insert new data into the database
Fetch the last inserted row entirely
Then update the last inserted row with exactly the same data as just inserted
The last query is basically:
Of course there is the possibility that duplicate data exists. And you want to modify only the last row. That is where I meant aside from the fact if the database has any good structure.
But to prevent duplicate data one could add:
The point is
Step 2
Does not work since I just made it up a couple of minutes a go. However I would like to know if there are any similar functions or routines that do the same as I just mentioned.
Hope that it is clear what I want.
My solution eventually is:
But an AutoIncrement field seems to be essential.
Second solution but still with an AutoIncrement field.
(as proposed by: Edwin Lambregts)
Note that this solution is not failsafe. Because the query selects the last inserted row. But by ALL instances! Hence if another user just inserted a new row. You will get to see his input and not yours. This can occur if the update happens in between.
2 Answers 2
I’m not sure what your question is but, in order to not mess the things up in your table you have to implement this things:
Have a PK (primary key) on the table. It provides you a way to uniquely identify each row. It should be either a field or a combination of fields that uniquely identify each row. In theory, he best candidate for a PK is an intrinsic property of the entity stored in the table that identifies the entity. For example, for a Country table a good candidate for the PK is the full country name. In the real world, the AUTO_INCREMENT fields were invented because:
On INSERT you can either generate a value for the PK and put it into the query or rely on the database to generate one for you (if the PK is an AUTO_INCREMENT field).
You can let the database generate an unique value for your PK (by providing NULL for the PK in the INSERT query or not providing it at all) then ask it about the value it generated. MySQL provides the LAST_INSERT_ID() function for this purpose. An excerpt from the documentation:
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
The database takes care of everything: the value is unique, it doesn’t interfere with other instances of the script (or even other code) that tries to insert in the same table on the same time; the INSERT succeeds, the PK is unique, I get back the value that identifies my row and not someone else’s row.
Having an additional column that is not an intrinsic property of the entities stored in the table is a small price to pay for the benefits it produces.
This is just a guideline and an artificial AUTO_INCREMENT column should be added only when it helps. In the example above with the countries table, an AUTO_INCREMENTED column for PK is not needed: