0

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("")) ;

Then:
$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()) ;
rolinger
  • 2,787
  • 1
  • 31
  • 53
  • Does this answer your question? [PHP/MySQL insert row then get 'id'](https://stackoverflow.com/questions/897356/php-mysql-insert-row-then-get-id) – rjaycarl - Mar 17 '20 at 17:41
  • No, all of those solutions are just as I am currently doing it - pretty much INSERT, then get lastID, then do what ever. Can I get lastInsertID of the record being inserted to be used in the same insert? – rolinger Mar 17 '20 at 17:47
  • The id is already part of your table so why store it again? Anytime you do a query its a simple matter of using concat(id, '-', appString) as filename – Phaelax z Mar 17 '20 at 18:24

1 Answers1

0

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.

rjaycarl -
  • 53
  • 13