PHP+MySQL注册功能SQL插入失败（错误码23000）求助

Hey there! Let's break down why your registration data isn't saving to the database. The error code 23000 is a dead giveaway—it means you're hitting a SQL constraint violation (your INSERT query is breaking a rule defined on your tbl_users table). Here's how to fix this step by step:

1. Fix the Error Info Display (Get the Exact Issue) #

First, your current code isn't showing full error details because you're calling errorInfo incorrectly. It's a method, not a property. Update this line in your func_register3.php:

// Wrong
echo print_r($stmt->errorInfo, true);
// Correct
echo print_r($stmt->errorInfo(), true);

Running this will give you a specific message like "Duplicate entry 'john_doe' for key 'username'" or "Column 'email' cannot be null"—this will pinpoint the exact constraint being violated.

2. Check for Duplicate Usernames #

The most common cause of 23000 for registration systems is a duplicate username. If your tbl_users table has a UNIQUE constraint on the username column (which it should for a login system), trying to insert an existing username will fail.

Add a check before your INSERT query to catch this:

// Check if username already exists
$checkUserStmt = $dbh->prepare("SELECT COUNT(*) FROM tbl_users WHERE username = :frm_user");
$checkUserStmt->bindParam(':frm_user', $frm_user);
$checkUserStmt->execute();
$userExists = $checkUserStmt->fetchColumn();

if ($userExists > 0) {
    echo "This username is already taken. Please choose another one.";
    exit; // Stop execution if username exists
}

3. Verify Table Constraints #

Double-check your tbl_users table structure for other mandatory constraints:

• Are username or password set to NOT NULL? (They should be, but make sure your form is actually sending valid data for both.)
• Do you have any other required columns (like email, created_at) that aren't included in your INSERT query? If so, you'll need to either add them to your query or set default values for those columns in the database.

4. Add Password Validation & Security #

While not the cause of your current error, there are two critical improvements you should make:

a. Match Passwords Before Insert #

Add a check to ensure the password and its repetition match:

if ($frm_pass !== $frm_pass_rep) {
    echo "Passwords do not match. Please try again.";
    exit;
}

b. Hash Passwords (Never Store Plaintext!) #

Storing plaintext passwords is a huge security risk. Use PHP's built-in password_hash() function:

// Replace the plain password with a hashed version
$hashedPassword = password_hash($frm_pass, PASSWORD_DEFAULT);
$stmt->bindParam(':frm_pass', $hashedPassword);

When logging users in, use password_verify() to check the hashed password against the user's input.

5. Confirm Data is Being Sent Correctly #

Quickly verify that your form is actually sending the data to PHP. Add this at the top of func_register3.php to debug:

var_dump($_POST); // This will show all data received from the form

Make sure frm_user, frm_pass, and frm_pass_rep are all present and have values.

Once you fix the error info display, you'll get the exact reason for the 23000 error, which will make resolving it straightforward.

内容的提问来源于stack exchange，提问作者Greta