0
$sql = "INSERT INTO book (bookname) values('kkkkkkkkk'); 
SET @bookid = LAST_INSERT_ID(); 

INSERT INTO paper (papername) values('hhhhhhh'); 
SET @paperid = LAST_INSERT_ID(); 

UPDATE author SET  bookid = @bookid, paperid = @paperid WHERE id = 11;

SELECT @bookid as bookid, @paperid as paperid FROM DUAL;"

$stmt = $pdoConnect->prepare($sql);
$stmt->execute();

$numofnewParn =$stmt->rowCount();
if($numofnewParn>0){
    $newParentDt = $stmt->fetch(PDO::FETCH_ASSOC);
    print_r($newParentDt);
}

I have set of inserts with LAST_INSERT_ID assigned to respective parameters.

Later, updating a table with the parameters.

until $stmt->execute(); is not problem.

My question is can I continue the query by adding SELECT and fetch the data like $stmt->fetch(PDO::FETCH_ASSOC)?

or does it not make sense? if so, is there any source?

because above code does not print out.

Elyor
  • 5,396
  • 8
  • 48
  • 76

1 Answers1

2

You need to use PDOStatement::nextRowset see here to move onto the next queries result in your multi statement... however a cleaner setup would be to break this down into single statement queries and use PHP variables to save your bookid and paperid values:

<?php

$sql = "INSERT INTO book (bookname) values('kkkkkkkkk');"
$stmt = $pdoConnect->prepare($sql);
$stmt->execute();

$bookid = $pdoConnect->lastInsertId();

$sql = "INSERT INTO paper (papername) values('hhhhhhh');"
$stmt = $pdoConnect->prepare($sql);
$stmt->execute();

$paperID = $pdoConnect->lastInsertId();

$sql = "UPDATE author SET  bookid = $bookid, paperid = $paperid WHERE id = 11;"
$stmt = $pdoConnect->prepare($sql);
$stmt->execute();
DevWithZachary
  • 3,545
  • 11
  • 49
  • 101
  • this is what I was trying to avoid. because number of inserts are dynamic. I was wandering if I can squeeze all into one execute – Elyor Jun 30 '21 at 09:35
  • 1
    @Elyor if the number of inserts is dynamic then you could presumably use whatever logic you would use to build up the single sql statement to create and execute multiple statements - I'm guessing you're just going to use some sort of loop – ADyson Jun 30 '21 at 09:40
  • As @ADyson said, if its dynamic write the logic in PHP to loop the insert statements... trying to run multi queries and get some result back out really is just a big head ache waiting to happen – DevWithZachary Jun 30 '21 at 09:43
  • I will definitely go with this if I cannot find a way of doing it with single PDO execution. thanks @Zachary – Elyor Jun 30 '21 at 09:48
  • @Elyor don't waste your time looking for a way to do it within a single execute command. It's just not supported for individual SQL, simple as that. The rowset functionality only works with stored procedures (and only with certain db providers, as the manual link above explains. And it's intended to be used with multiple selects really, not inserts etc.) Just use one execute command per statement. You can generate them easily enough as needed, just by looping or similar. – ADyson Jun 30 '21 at 09:54
  • @ADyson you are right. it was just for micro optimization purpose, Thanks. – Elyor Jun 30 '21 at 09:58