PHP: Ensuring that you can reference the last row inserted into a database by a user

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s