PHP预处理语句疑问：INSERT前需使用$stmt = $conn->prepare吗？

Let's break down your problems step by step to get your registration flow working properly.

1. Do You Need mysqli_prepare() (or $conn->prepare()) Before INSERT? #

Absolutely yes. Prepared statements are critical for preventing SQL injection, and they’re required to use parameterized placeholders like ? in your query. Your code uses the procedural mysqli_prepare($connection, $sql) syntax, which is totally valid—you could also use the object-oriented $stmt = $connection->prepare($sql) if you prefer, but both approaches work. The core idea is the same: you must prepare the statement before binding parameters and executing it.

2. The Critical Bug Stopping Data from Inserting #

Your code has a simple but fatal variable name typo in parameter binding. Look at this section:

mysqli_stmt_bind_param($stmt, "sss", $param_username, $param_hashed_password, $param_email);
$param_username = $username;
$param_password = $hashed_password; // ❌ Wrong variable name!
$param_email = $email;

You bound $param_hashed_password to the statement, but then assigned your hashed password to $param_password instead. This means the password field in your INSERT ends up using an undefined variable (which defaults to an empty value), causing the insert to fail silently.

3. Is Your password_hash() Usage Correct? #

Nope, that’s not the problem! Your password_hash($password, PASSWORD_DEFAULT) call is exactly the right way to securely hash passwords in PHP. This function handles all the complex hashing logic for you—no issues here.

4. Corrected Full Code #

Here’s your fixed code with proper variable naming, error handling, and best practices added:

// Get POST values (make sure to validate/sanitize these in production!)
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

// Securely hash the password
$hashed_password = password_hash($password, PASSWORD_DEFAULT);

// Create prepared statement
$sql = "INSERT INTO `user` (username, password, email) VALUES (?, ?, ?)";

// Prepare the statement and check for errors
if ($stmt = mysqli_prepare($connection, $sql)) {
    // Bind parameters: "sss" = 3 string values (username, hashed password, email)
    mysqli_stmt_bind_param($stmt, "sss", $param_username, $param_hashed_password, $param_email);
    
    // Assign values to bound parameters
    $param_username = $username;
    $param_hashed_password = $hashed_password; // ✅ Fixed variable name
    $param_email = $email;
    
    // Execute the statement
    if(mysqli_stmt_execute($stmt)) {
        $_SESSION["username"] = $username;
        header("location: login.php");
        exit(); // Always exit after a header redirect to stop further code execution
    } else {
        // Debug: Show execution error (remove in production)
        echo "Execution error: " . mysqli_stmt_error($stmt);
    }
    
    // Clean up: Close the statement
    mysqli_stmt_close($stmt);
} else {
    // Debug: Show preparation error (remove in production)
    echo "Preparation error: " . mysqli_error($connection);
}

// Close database connection (optional if using persistent connections)
mysqli_close($connection);

5. Quick Notes on Syntax Confusion #

Your INSERT...VALUES(?, ?, ?) syntax is 100% correct for mysqli prepared statements. The "different examples" you saw might be using PDO instead of mysqli (which uses named placeholders like :username) or object-oriented vs procedural mysqli syntax—both are valid, but stick to one style to avoid confusion.

For easier debugging during development, add these lines at the top of your script to see all PHP errors:

error_reporting(E_ALL);
ini_set('display_errors', 1);

内容的提问来源于stack exchange，提问作者Joe S.