php – mysql using lastInsertID in the same mysql insert?-ThrowExceptions

Exception or error:

I am inserting a single row into a table, one of the columns though needs the lastInsertID to append another column. We are building file names off of record IDs. Instead of doing a 2nd update to the newly inserted record, is it possible to get the value of the ID column and append to it in the same mysql insert?

Using PHP, currently I am doing:

$query = "INSERT INTO table (id,idName) values (null,?)" ;
list($lastID,$inError,$inResult) = dbInsert($query,array("")) ;

$appString = $lastID . "-S1001.pdf" ;
$query = "UPDATE table SET idName=? WHERE id=?" ;
list($upCount,$upError) = dbUpdate($query,array($appString,$lastID)) ;

But is there a way to merge the INSERT and UPDATE into a single statment?

$apString = "-S1001.pdf" ;
$query = "INSERT INTO table (id,idName) values (null,id.$appString)" ;
list($lastID,$inError,$inResult) = dbInsert($query,array()) ;
How to solve:

Why not create a trigger that will do your update whenever you insert a row into the table?
This way, you don’t need to manually do the second query. Just the insert.

Leave a Reply

Your email address will not be published. Required fields are marked *