复制行获取上次插入ID

Question

I am copying a row in one of the tables in my database and trying to get the last inserted ID. Copying the row works fine, and the AI field updates properly, but it is not returning the last insert ID (although it does return true - or if I echo something else out in its place that works fine as well).

Some of the other queries also use last insert ID and those work well.

I suspect it is something to do with the temporary table, but cannot find anything on SO about a similar problem with last insert ID and temporary tables. Does anyone know what is causing the problem or if there is a workaround?

public function duplicateItinRow($itineraryID){
    $this->db->query("CREATE TEMPORARY TABLE temporary_itin_table AS SELECT * FROM itinerary_ref WHERE Itinerary_ID = $itineraryID;

    UPDATE temporary_itin_table SET Itinerary_ID=NULL;

    INSERT INTO itinerary_ref SELECT * FROM temporary_itin_table;

    DROP TEMPORARY TABLE temporary_itin_table");

    //Execute
    if($this->db->execute()){           
        return $this->db->lastInsertId();

    } else {
        return false;
    }


}

UPDATE:

Not the ideal solution I'm sure, but have got it working using the copy method detailed here: How to copy a row and insert in same table with a autoincrement field in MySQL?

Answer 1

I think lastInsertedId will be updated by your last "DROP TEMPORARY TABLE" statement.

Try to split the query into two separate queries instead:

<?php

public function duplicateItinRow($itineraryID) {
    $this->db->query("CREATE TEMPORARY TABLE temporary_itin_table AS SELECT * FROM itinerary_ref WHERE Itinerary_ID = $itineraryID;

    UPDATE temporary_itin_table SET Itinerary_ID=NULL;

    INSERT INTO itinerary_ref SELECT * FROM temporary_itin_table;");


    if(!$this->db->execute()) {
        return false;
    }

    $lastId = $this->db->lastInsertId();
    $this->db->query("DROP TEMPORARY TABLE temporary_itin_table");

    if(!$this->db->execute()) {
        return false;
    }

    return $lastId;
}

Also note that you have a SQL injection vulnerability when you add $itineraryID directly to the SQL query without using prepared statements. If the $itineraryID is populated from user input people can hack your website.