MySQLi: How to insert into multiple tables with pr

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);

Answer 1

Try using mysqli_multi_query, Check this link for example http://blog.ulf-wendel.de/2011/using-mysql-multiple-statements-with-php-mysqli/

Answer 2

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

<?php
   try {

        $db->beginTransaction();

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

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

        $db->commit();

  } 
   catch(PDOException $ex) {
        //Something went wrong rollback!
        $db->rollBack();
        echo $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();

Hope this helps!

Answer 3

Maybe this one can help you: MySQL Insert into multiple tables?

I think you need a statement like this:

BEGIN;
INSERT INTO user(column_a,column_b) VALUES('value_a','value b');
INSERT INTO profile(column_x,column_y) VALUES('value_x','value_y');
COMMIT;

If you need the last id from user table, you can use the LAST_INSERT_ID() function.