PDO fetch failure on multi line query

Question

I think I'm having a fundamental confusion in how PDO works. I have this semi-complicated multiline query that inserts a row into the DB, but I need the id (aid) of the insert after it's finished. I have similar code for another function that works perfectly fine, but this one is not returning anything. I get a result of null. When I execute the same thing in phpmyadmin (replacing params with test values), I get the expected aid back as a result. What am I doing wrong?

function add_list_activity($activity, $lid, $db) 
{
  $data = array();

  $query = " 
      INSERT INTO activities
      (`name`) VALUES (:activity);

      SET @act_id = LAST_INSERT_ID();

      INSERT INTO lists_activities 
      (`aid`, `lid`) VALUES (@act_id, :lid);

      SELECT aid FROM activities WHERE aid = @act_id;
  "; 

  // The parameter values 
  $query_params = array( 
      ':activity' => $activity,
      ':lid' => $lid
  ); 

  try 
  { 
      $stmt = $db->prepare($query); 
      $result = $stmt->execute($query_params); 
  } 
  catch(PDOException $ex) 
  {  
      die("Failed to run query: " . $ex->getMessage()); 
  }
  $data = $stmt->fetch();

  return $data;
}

Thanks!

Answer 1

You're right, you have a misunderstanding about PDO's prepared statement capability. You can't pass it more than one SQL statement at a time; what you're trying to do requires four different prepared statements.

Notice also that you can simplify your use of LAST_INSERT_ID() by using these statements, each as a separate prepared statement.

INSERT INTO activities (`name`) VALUES (:activity)

SELECT LAST_INSERT_ID() AS aid

INSERT INTO lists_activities (`aid`, `lid`) VALUES (LAST_INSERT_ID(), :lid)

If you needed to do all this in one server round-trip for performance reasons, you could create a stored function to do that.