Not able to get last inserted ID from MySQL

Question

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 using beginTransaction and commit and therefore it is handled as one transaction?

Answer 1

Yes, it is. You need to get the ID before you commit the transaction.