MySQLi条件判断异常：用户注册时trial值始终被设为1

It looks like your code has a couple of key issues that are causing it to always insert a new device with trial=1 instead of checking for existing entries. Let's break down the problems and fix them step by step.

Key Issues in Your Original Code #

• Invalid SELECT Query: Your SELECT FROM device statement is missing the columns you want to fetch (e.g., * or a specific column like device_id). This throws a SQL error, so your check for existing devices never works correctly.
• Unchecked Query Results: Even if the query was valid, you're not properly verifying if any rows were returned to confirm the device exists.
• SQL Injection Risk: Directly inserting $serial into your query string is a major security vulnerability—always use prepared statements for user input.
• Incomplete Logic Flow: Your code snippet cuts off, but it’s clear you’re not branching correctly between updating an existing device and inserting a new one.

Corrected Code Implementation #

Here’s the fixed version that follows your intended logic, fixes the bugs, and adds security best practices:

// Retrieve user input
$serial = $_POST["serial"];
$fcm = $_POST["fcm"];

// Check if device exists using a prepared statement
$check_sql = "SELECT device_id FROM device WHERE device_id = ?";
$stmt = mysqli_prepare($conn, $check_sql);
mysqli_stmt_bind_param($stmt, "s", $serial);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

if (mysqli_num_rows($result) > 0) {
    // Device exists: update trial to 0 (optional: update FCM token too)
    $update_sql = "UPDATE device SET trial = 0, fcm = ? WHERE device_id = ?";
    $update_stmt = mysqli_prepare($conn, $update_sql);
    mysqli_stmt_bind_param($update_stmt, "ss", $fcm, $serial);
    mysqli_stmt_execute($update_stmt);
    echo "Existing device updated with trial=0";
} else {
    // Device doesn't exist: insert new entry with trial=1
    $insert_sql = "INSERT INTO device (device_id, fcm, trial) VALUES (?, ?, 1)";
    $insert_stmt = mysqli_prepare($conn, $insert_sql);
    mysqli_stmt_bind_param($insert_stmt, "ss", $serial, $fcm);
    mysqli_stmt_execute($insert_stmt);
    echo "New device registered with trial=1";
}

// Clean up database resources
mysqli_stmt_close($stmt);
if (isset($update_stmt)) mysqli_stmt_close($update_stmt);
if (isset($insert_stmt)) mysqli_stmt_close($insert_stmt);

Explanation of Changes #

• Valid SELECT Query: We now fetch device_id to properly confirm if the device exists in the table.
• Prepared Statements: All queries use parameter binding to eliminate SQL injection risks when handling user input.
• Row Count Check: mysqli_num_rows($result) > 0 accurately determines if the device is already registered.
• Proper Branching: We either update the existing device’s trial value to 0 (and refresh the FCM token if needed) or insert a new entry with trial=1.
• Resource Cleanup: Closing prepared statements after use prevents memory leaks and keeps your database connection efficient.

Test this code with both existing and new device IDs to confirm it behaves as expected.

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