0

Given the following block of code:

$q = "
BEGIN;
DECLARE User_ID int
INSERT INTO user (field1,field2) values (value1,value2);
set User_ID = select LAST_INSERT_ID();
INSERT INTO table2 (field1,field2) values (value1,LAST_INSERT_ID());
select User_ID
COMMIT;";

$sth = PDO::prepare($q);
$sth->execute();

How the heck to do I retreive the value "User_ID" ? fetch and fetchAll just return empty arrays, and there are no errors thrown in the process

EDIT 1 Sorry, should have been more clear... there is a second INSERT statement before returning the results.

Jonathan Coe
  • 1,485
  • 4
  • 18
  • 36

1 Answers1

1

$q = " BEGIN; DECLARE User_ID int INSERT INTO user (field1,field2) values (value1,value2); set User_ID = select LAST_INSERT_ID(); INSERT INTO table2 (field1,field2) values (value1,LAST_INSERT_ID() - 1); select User_ID COMMIT;";

$sth = PDO::prepare($q); $sth->execute();

Kyle Hudson
  • 898
  • 1
  • 14
  • 26
  • Providing your engine supports it – Kyle Hudson Sep 15 '11 at 19:33
  • sorry, should have been more clear... there is a second INSERT clause prior to returning the user_id... hence setting the variable value rather than just grabbing it with PDO's version. – Jonathan Coe Sep 15 '11 at 19:35
  • OK you will need to use a transaction then – Kyle Hudson Sep 15 '11 at 19:40
  • try looking at http://stackoverflow.com/questions/5941275/pdo-get-multiple-insert-ids-php5 – Kyle Hudson Sep 15 '11 at 19:45
  • Hmm... yea i'm still trying to find a transaction solution. I'll probably end up doing it in two stages for the time being, I think I just need to do some reading on PDO transactions and see if I find my answer there. Thanks for your help! – Jonathan Coe Sep 15 '11 at 22:16