Not able to get last inserted ID from MySQL
I'm inserting multiple rows from one table to another, based on ID. For this project I'm using PDO for all DB queries. This is the code / function I'm using:
protected function importData($data) {
$i = 0;
$this->db->beginTransaction();
foreach($data as $item) {
$id = $item['id'];
$sql .= "INSERT INTO table1 (name,age)
SELECT name, age
FROM table12
WHERE id = $id; ";
$this->db->exec($sql);
$i++;
}
$this->db->commit();
// None of these are working
$last_id1 = $this->db->exec('SELECT LAST_INSERT_ID()');
$last_id2 = $this->db->lastInsertId();
echo 'id1: '.$last_id1.', id2:'.$last_id2;
}
But for some resaon, I'm not able to get the last inserted ID.
If I try SELECT LAST_INSERT_ID()
in 开发者_运维百科Toad for MySQL
, I do get a result, but it's not the ID of the last inserted row.
Why isn't the last inserted row id registered when I insert rows this way?
Is it because I'm usingbeginTransaction
and commit
and therefore it is handled as one transaction?Yes, it is. You need to get the ID before you commit the transaction.
精彩评论