One of the biggest challenges in writing applications that allow users to insert data into a database, is making sure that if you have to break up the sql statements, such as if you need to update multiple tables and don’t use a function or stored procedure, that you reference the correct row.
Sure, you can rely on PDO’s LastInsertid however, if you have a busy database environment, you can run into a situation where suddenly your script is referencing the wrong row.
Here’s how that happens..
Users A and B initiate an insert function at the same time.
User A’s instance runs, but the LastInsertid is pulled after User B’s instance updates the table.
So now, A’s instance is referencing User B’s row.
Another method I’ve seen used is pulling a specific row using the user id, insert time, and ORDER BY DESC.
That too can be effective, however, if there is a change to the database structure, or there are delays, you could still end up with problems.
Recently, I came up with a way that I think works better. Inserting a unique id generated by the php script.
I added a field to the table, insertuid. (Insert Unique Identifier)
This allows me to reference the row with a known value. If there is a delay and the row doesn’t exist when I call it, I can use error/exception handling to deal with that.
Here’s the code
$inuid = uniqid(); $sql = "INSERT INTO mytable (uid,insertuid) VALUES (?,?)'; $q = $dbh->prepare($sql); $q->execute(array($uid,$inuid)); $sql = "SELECT mtidx FROM mytable WHERE insertuid = ?"; $q = $dbh->prepare($sql); $r = $q->fetchAll(); $mtidx = $r['mtidx']; //you can now reference the mytable row accurately