准备好INSERT语句以使insert_id在第二个准备好的INSERT语句中使用

Question

Im trying to create my own register form but im having issues with prepared statements.

the idea is to create a prepared statement to insert info into the user table, then from that get the insert_id from the generated content to use in another insert statement

here is a version of my register script

  <?php

    $returnedId = '';

    include "includes/dbconnect.php";

    $stmt = $db->prepare("INSERT INTO `users`(`Username`, `Email`, `Password`) VALUES (?, ?, ?)");
    $stmt->bind_param('sss', $_POST['username'], $_POST['email'], $_POST['password']);
    $stmt->execute();
    $returnedId = $stmt->insert_id;
    $stmt->close();

    echo $returnedId;

    $allergystmt = $db->prepare("INSERT INTO 'user_allergy' ('user_id', 'allergy_id') VALUES (?, ?)");
    $allergystmt->bind_param('ss', $returnedId, $_POST['check_list']);
    $allergystmt->execute();
    $allergystmt->close();

    header('Location: index.php');

?>

the first prepared statement runs correctly and inserts information into the table, after that the $returnId variable is successfully echoed. next in the script is my second prepared statement, when it tries to run im getting the error that says:

Fatal error: Call to a member function bind_param() on a non-object in D:\filepathegister.php on line 17

it seems that my variable isnt being carried into the second prepared statement.

Answer 1

Your second query has syntax errors and failed to prepare. Since you have no error handling for database failures like this, your later code just blunders onwards:

$allergystmt = $db->prepare("INSERT INTO 'user_allergy' ('user_id', 'allergy_id') VALUES (?, ?)");
                                         ^---         ^--^---    ^-- etc...

You cannot use ' quotes on table and field names. ' indicate strings. None of those field/table names are reserved words, so there is NO need to quote them at at all:

$allergystmt = $db->prepare("INSERT INTO user_allergy (user_id, allergy_id) VALUES (?, ?)");
if (!$allergystmt) { die($dbh->errorInfo()); }

Note the addition of the errorInfo() output. Never assume a DB operation was successful. Always assume failure, and treat success as a pleasant surprise.