0

I'm trying to get two INSERT statement into one SQL query. I tried several things but nothing seems to work. If I look on the internet it seems it has to work. But it doesn't work for me and I get an error. I'm using this code with PDO.

I tried multiple web browsers, multiple types of code and I tried the "BEGIN" and "COMMIT" statement. 

$sql = "INSERT INTO gebruiker (email, wachtwoord, rol_id) VALUES (?, ?, 2); ".
"INSERT INTO huisarts (voornaam, achternaam, email, straatnaam, huisnummer, postcode, plaats, telefoonnummer, uid, rol_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, LAST_INSERT_ID(), 2);";
$pdostatement = $pdo->prepare($sql);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wessel Maan
  • 1
  • 3
  • 7
    It's not javascript but php concat operator is `.` instead of `+`. Once have a look at your snippet. – Rahul May 29 '19 at 07:31
  • 2
    Why do you need 2 inserts in one go, 2 separate inserts can be easier to debug and you can always use a transaction if you need to ensure they are done together. – Nigel Ren May 29 '19 at 07:32
  • 1
    Combining 2 unrelated queries in 1 statement does not seem to make a lot of sense so there really is no need to do that. And if the queries are related - for example if the `uid` in the second query is the inserted id of the first - you would need separate queries to get the value anyway. – jeroen May 29 '19 at 07:39
  • 2
    Possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – Lelio Faieta May 29 '19 at 07:43
  • PDO doesn't support multy query so you cannot get what you want to do. Based on your example also a store procedure makes little use – Lelio Faieta May 29 '19 at 07:44
  • You say you get an error - what is the error? – Nigel Ren May 29 '19 at 07:46
  • @jeroen, couldn't you also use LAST_INSERT_ID() (as per https://dev.mysql.com/doc/refman/8.0/en/getting-unique-id.html) in that case, in which you do need to combine the two queries into a single call? – cvbattum May 29 '19 at 08:07
  • @Creator13 there are no reasons to combine two queries into a single call. – Your Common Sense May 29 '19 at 08:09

1 Answers1

3

That silly concatenation mistake aside, it is technically possible to run two INSERT queries in one call, but there is not a single reason to do so.

Besides, you will need to configure PDO specifically to allow multiple queries which is not advised.

Therefore, just run these two queries as two separate prepared statements.

$sql = "INSERT INTO gebruiker (email, wachtwoord, rol_id) VALUES (?, ?, 2)";
$stmt = $pdo->prepare($sql);
$stmt->execute($first_query_data);
$sql = "INSERT INTO huisarts (voornaam, achternaam, email, straatnaam, huisnummer, postcode, plaats, telefoonnummer, uid, rol_id) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, LAST_INSERT_ID(), 2);";
$stmt = $pdo->prepare($sql);
$stmt->execute($second_query_data);
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345