title: Insert rows for an m:m relationship (pupil-evening_activity) consistently
body: <p>I have three tables in my database (MySQL/InnoDB): <code>pupil</code>, <code>evening_activity</code> and <code>pupil_evening_activity</code>. As you can guess there is an <code>m:m</code> relationship between <code>pupil</code> and <code>evening_activity</code>.</p>
  
  <p>I have a HTML form to insert new pupils and associate to them evening activities. After submitting the form, I would like to insert pupils and their associated activities in the database. So I have written this code:</p>
  
  <pre><code>$db = new PDO('mysql:host=localhost;dbname=school;charset=utf8','root', '****');
  $db-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
  
  try {
      $sql = "INSERT INTO pupil 
              (name, surname) 
              VALUES
              ('test_name', 'test_surname')";
  $st = $db-&gt;prepare($sql);
  $st-&gt;execute();
  
      foreach ($eveningActivityIds as $eveningActivityId) { 
      $sql = "INSERT INTO pupil_evening_activity 
                  (pupil_id, evening_activity_id) 
                  VALUES 
                  (?, ?)
          ";
      $st = $db-&gt;prepare($sql);
      $st-&gt;execute(array($db-&gt;lastInsertId(), $eveningActivityId));
      }
  
  } catch (PDOException $e) {
  
  echo $e-&gt;getMessage();
  return false;
  
  }
  </code></pre>
  
  <p>I have written that code <strong>expecting <code>PDO::lastInsertId()</code> always returns the last inserted pupil's id</strong>, but I've found out that what it really returns is <strong>the last inserted <code>id</code> on the whole database</strong>. So if another row is inserted in a hypothetical <code>teacher</code> table <em>just before</em> calling to <code>$db-&gt;lastInsertId()</code> on the code above, <code>$db-&gt;lastInsertId()</code> would return the <code>id</code> of the inserted teacher instead of the <code>id</code> of the last pupil.</p>
  
  <p>So how I can get 100% safety the last inserted <code>id</code> of <code>pupil</code> table after submitting the form?</p>
  
  <p>I have thought about using a transaction and <code>MAX()</code> function to get last <code>id</code> inserted in the <code>pupil</code> table, but I'm not sure about it.</p>