MySQL条件校验失败时返回错误的实现方案咨询

Hey mate, I see exactly what's going on here! The problem isn't with your INSERT logic itself—it's how you're checking if the insertion actually happened. Let me break it down and fix this for you.

Why Your Current Code Isn't Working #

When you run an INSERT ... SELECT query that doesn't return any rows to insert (because the HAVING COUNT(*) = 0 condition fails), mysqli_query() still returns true—because the query executed without syntax errors, even if no rows were added. That's why you're seeing "New record created successfully" even when the submission should be blocked.

Critical Side Note: SQL Injection Risk! #

Before we fix the feedback issue, let's address a huge security hole in your current code: you're directly interpolating user input variables into your SQL string. This is a classic SQL injection vulnerability. We'll fix this using prepared statements—never skip this step!

The Fixed Code #

Here's the updated, secure version of your code that correctly handles the submission feedback:

<?php
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Use prepared statement to prevent SQL injection
$sql = "INSERT INTO participants (name, pin, nric, contact, email, address) 
        SELECT ?, ?, ?, ?, ?, ? 
        FROM users 
        WHERE (contact = ? AND time >= now() - INTERVAL 1 DAY) 
        HAVING COUNT(*) = 0";

// Prepare and bind parameters
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "sssssss", $name, $pin, $nric, $contact, $email, $address, $contact);

// Execute the statement
if (mysqli_stmt_execute($stmt)) {
    // Check how many rows were actually inserted
    $affected_rows = mysqli_stmt_affected_rows($stmt);
    if ($affected_rows > 0) {
        echo "New record created successfully";
    } else {
        echo "Error: You have already submitted a request within the last 24 hours. Please try again later.";
    }
} else {
    echo "Error: " . mysqli_error($conn);
}

// Clean up resources
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

Key Changes Explained #

• Prepared Statements: We replaced raw variable interpolation with ? placeholders and used mysqli_stmt_bind_param() to safely pass user input. This completely eliminates SQL injection risks.
• Check Affected Rows: Instead of relying solely on the execute function's return value, we use mysqli_stmt_affected_rows() to verify if any rows were actually inserted. A return value of 0 means the 24-hour duplicate check failed, so we show the appropriate error message.
• Granular Feedback: We split the success case into two scenarios: actual insertion success vs. blocked duplicate submission, so users get clear, accurate feedback.

Bonus Recommendation #

For better performance, add an index on your users table for the contact and time columns. This will speed up the WHERE clause check significantly as your database grows.

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