CODEWITHSUNDEEP
phpmysqlimysql-insert-id
John
John

Reputation: 952

Get ID of first statement and post it in the second

I have a script that creates an user in the database:

  if ($insert_stmt = $mysqli->prepare("INSERT INTO members (username, email, password, salt) VALUES (?, ?, ?, ?)")) {
    $insert_stmt->bind_param('ssss', $username, $email, $password, $random_salt);
    if (! $insert_stmt->execute()) {
      header('Location: ../error.php?err=Registration failure: INSERT');
      exit();
    }
  }
  header('Location: ./register_success.php');
  exit();

The table members also haves an ID column which is unique and auto increment.

After the user is created I want to run a new statement. 

 if ($insert_stmt2 = $mysqli->prepare("INSERT INTO users (user_id, username, email, password, salt) VALUES (?, ?, ?, ?, ?)")) {
    $insert_stmt2->bind_param('sssss', $user_id, $username, $email, $password, $random_salt);

This time I have a new column named user_id. I want to get the inserted ID of the first INSERT and insert it as user_id into the second table.

Does someone know how I can do that?

Edit 1:

if ($insert_stmt = $mysqli->prepare("INSERT INTO members (username, email, password, salt) VALUES (?, ?, ?, ?)")) {
    $insert_stmt->bind_param('ssss', $username, $email, $password, $random_salt);
    // Execute the prepared query.
    if (! $insert_stmt->execute()) {
        header('Location: ../error.php?err=Registration failure: INSERT');
        exit();
    }
    if ($insert_stmt2 = $mysqli->prepare("INSERT INTO users (user_id, username, email, password, salt) VALUES (?, ?, ?, ?, ?)")) {
        $insert_stmt2->bind_param('sssss', $mysqli->insert_id, $username, $email, $password, $random_salt);
        // Execute the prepared query.
        if (! $insert_stmt2->execute()) {
            header('Location: ../error.php?err=Registration failure: INSERT');
            exit();
        }
    }
}
header('Location: ./register_success.php');
exit();

Upvotes: -1

Views: 46

Answers (1)

Cavalier
Cavalier

Reputation: 91

you need to use the $mysqli->insert_id after your first statement and before your second statement. Then it is available for your second statement.

$stmt = $mysqli->prepare("INSERT INTO members (username, email, password, salt) VALUES (?, ?, ?, ?)");
$stmt->bind_param('ssss', $username, $email, $password, $random_salt);
$stmt->execute();
$user_id = $mysqli->insert_id;
    
$stmt = $mysqli->prepare("INSERT INTO users (user_id, username, email, password, salt) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('sssss', $user_id, $username, $email, $password, $random_salt);
$stmt->execute();

Upvotes: 0

Related Questions