Copy Row Get Last Insert 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?

Can you confirm `id` column has `auto_increment` in table `itinerary_ref`, if not then `lastInsertId` will not work on non-auto increment PRIMARY_KEY column — AamirR, Mar 03 '18 at 18:48
Thanks - yes can confirm both, and that the inserted row is updating the AI column properly, value just not being returned. — HG123, Mar 03 '18 at 18:58
Can you show output of `return $this->db->query('SELECT LAST_INSERT_ID()')->fetchColumn(0);` instead of `PDO->lastInsertId();` — AamirR, Mar 03 '18 at 19:12
Thanks - sorry was using that originally - cannot get PDO->lastInsertId() to work — HG123, Mar 03 '18 at 22:52

score 0 · Answer 1 · answered Mar 03 '18 at 18:44

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.

Thanks - yes you are right about needing prepare, was just trying to get a simple version to work first. Unfortunately this is still returning 0 - thanks for the help though. — HG123, Mar 03 '18 at 18:55

Copy Row Get Last Insert ID

1 Answers1