MySQLi: How to insert into multiple tables with prepared statements

Question

I am in a situation where I need to insert into 2 tables in a query. I've searched around web and could not find solution. What I want to do is insert values in user table & insert values in profile simultaneously. I could do one after the other way but I've read that it is not efficient and is considered as poor coding technique.

Current Code:

$statement = $db->prepare("
    BEGIN;
    INSERT INTO `user`(`username`, `email`, `password_hashed`, `fname`, `lname`, `dob`, `agreement`, `gender`, `access_token`)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
    INSERT INTO `profile_picture`(`owner`) VALUES (LAST_INSERT_ID());
    COMMIT;
");
if($statement) {
    $statement->bind_param("ssssssiss", $username, $email, $hashedPassword, $fname, $lname, $dob, $agreement, $gender, $access_token);
    $statement->execute();
    $statement->close();

    echo "DONE";
    exit();
}
else printf("Error: %s.\n", $db->error);

Error thrown is in French maybe. **Erreur de syntaxe près de 'INSERT INTO `user`(`username`, `email`, `password_hashed`, `fname`, `lname`, `do' à la ligne 2** — Muhammed Talha Akbar, Aug 01 '14 at 12:51
Why not use [`multi_query()`](http://php.net/manual/en/mysqli.multi-query.php)? Could work well. See [**this page**](http://www.daniweb.com/web-development/databases/threads/476929/mysqli-insert-to-multiple-tables-with-prepared-statements) also, could give you some ideas how. — Funk Forty Niner, Aug 01 '14 at 13:02
@Fred-ii- I got it from SO, btw. Well, how should I do that then? — Muhammed Talha Akbar, Aug 01 '14 at 13:05
Can you give me the link for it, I'm curious as to what's in that page on SO. — Funk Forty Niner, Aug 01 '14 at 13:06
Plus, I don't think you can use/inject transactions syntax in there like that. You need to create a transaction and `CALL` that procedure, something to the effect of `$stmt = $dbh->prepare("CALL sp_returns_string(?)");` this taken from a PDO example here http://php.net/manual/en/pdo.prepared-statements.php - See this page also http://www.mysqltutorial.org/mysql-transaction.aspx — Funk Forty Niner, Aug 01 '14 at 13:14
@Fred-ii- Thanks, I am using `multi_query()` now but I had to escape all the variables. Here is the link to the answer from which I got above code: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization I hope your curiousity's over — Muhammed Talha Akbar, Aug 01 '14 at 13:24
You're welcome. However and as I said earlier, I don't think you can use transactions-related code like that. — Funk Forty Niner, Aug 01 '14 at 13:49

score 2 · Answer 1 · edited May 11 '23 at 14:49

I had issues with this trying to copy answers like Frank's. The proper way to do it is:

try {
    $db->begin_transaction();
  
    $stmt = $db->prepare("INSERT INTO `user`(`username`, `email`, `password_hashed`, `fname`, `lname`, `dob`, `agreement`, `gender`, `access_token`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssssssiss", $username, $email, $hashedPassword, $fname, $lname, $dob, $agreement, $gender, $access_token);
    $stmt->execute();

    $stmt = $db->prepare("ANOTHER QUERY");
    $stmt->bind_param(...)
    $stmt->execute();

    $db->commit();
} catch(mysqli_sql_exception $ex) {
    //Something went wrong rollback!
    $db->rollback();
    throw $ex->getMessage();
}

After the first statement is executed, you can then gain access to the insertID from PHP using the following: $last_id = $db->lastInsertId();

I received a "pass-by-reference" error when trying this. I used mysqli and I couldn't get past the prepared statement. I have had successful inserts until I tried to do multiple prepared statements so I doubt anything is wrong with the inserts. — kamilah carlisle, Mar 31 '20 at 15:18

MySQLi: How to insert into multiple tables with prepared statements

1 Answers1

Linked