-1

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();
John
  • 904
  • 8
  • 22
  • 56
  • Use the [`$mysqli->insert_id`](http://php.net/mysqli_insert_id) property after executing your first INSERT statement. – rickdenhaan Jul 03 '17 at 18:57
  • RTM, http://php.net/manual/en/mysqli.insert-id.php – Rajdeep Paul Jul 03 '17 at 18:57
  • So in my case I need to replace `$user_id` with `$insert_stmt->insert_id`? – John Jul 03 '17 at 19:00
  • No, `insert_id` is a property of your *connection*, not of your *statement*. So you'll need to use `$mysqli->insert_id`. – rickdenhaan Jul 03 '17 at 19:01
  • But I am using `$mysqli->prepare` in the first and second statement. If I use `$mysqli->insert_id` it will select the insert_id of the second statement – John Jul 03 '17 at 19:04
  • It shouldn't. The property is filled when you call `$insert_stmt->execute()` and should only be overwritten once you call `$insert_stmt2->execute()`. So in `$insert_stmt2->bind_param()` it should point to the auto increment value generated by your first `$insert_stmt`. – rickdenhaan Jul 03 '17 at 19:08
  • The statement: `$insert_stmt2->bind_param('sssss', $mysqli->insert_id, $username, $email, $password, $random_salt);` did not create a new row in the database – John Jul 03 '17 at 19:22
  • See edit 1 in my first post. The second statement is not creating a row – John Jul 03 '17 at 19:58

1 Answers1

0

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();
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Cavalier
  • 93
  • 9