How to return row id after copying row in MySQL table?

Question

I need to copy/duplicate a row in my table and then return the new row's 'id' value. My 'id' column is an auto-increment field.

$sth = $dbh->prepare("DROP TEMPORARY TABLE IF EXISTS tmp_users;
                      CREATE TEMPORARY TABLE tmp_users
                          SELECT * FROM users 
                          WHERE user_id = $user_id
                      UPDATE tmp_users 
                          SET id = NULL;
                      INSERT INTO users 
                          SELECT * FROM tmp_users;
                      DROP TEMPORARY TABLE IF EXISTS tmp_users;");

$sth->execute();

If I do $id_new = $dbh->lastInsertId(); this returns '0', but not sure why. Any ideas?

[`PDO::prepare`](http://php.net/manual/en/pdo.prepare.php) does not seem to support multiple statements in one string; even if it did, you're missing a semicolon after your `CREATE TEMPORARY TABLE` statement so it would still turn out invalid. — lanzz, Oct 28 '12 at 16:02
The statements above seems to be executing just fine using prepare. However, I cannot seem to return the id of the newly inserted row. — Michael, Oct 28 '12 at 16:06

lanzz · Answer 1 · 2012-10-28T16:34:36.277

Your query has a syntax error in it, you're missing a semicolon at the end of your CREATE TEMPORARY TABLE statement:

CREATE TEMPORARY TABLE tmp_users
    SELECT * FROM users 
    WHERE user_id = $user_id
UPDATE tmp_users 
    SET id = NULL;

Even though you're under the belief that the statement "executes just fine", you can plainly see that the above excerpt has only one semicolon at the end; i.e. it is a single statement with a syntax error in it, not two separate CREATE and UPDATE statements.

PDO seems to be handling multi-statement queries very poorly, failing to report errors in them. I have performed the following test:

$sth = $dbh->prepare("INSERT INTO test VALUES ('test1');
                      INVALID STATEMENT;
                      INSERT INTO test VALUES ('test2');");
$sth->execute();

As you can see, I have included an invalid statement between two valid INSERT statements. After I run the above code, my test table has only the 'test1' value inserted, but the 'test2' insert was not performed and no error was reported by PDO.

The syntax error in your CREATE TEMPORARY TABLE prevents the table from actually being created, which also prevents the rest of the statements from functioning correctly, thus you get no new row inserted into the users table and no last inserted ID.

UPDATE: Here is the actual PHP bug report for this PDO behavior, apparently still unresolved.

How to return row id after copying row in MySQL table?

1 Answers1