PHP双查询异常：INSERT INTO无法写入scan表求助

Let's dive into why your scan table isn't getting populated, then fix the code properly.

Identified Problems #

Your INSERT statement has several critical issues that are preventing it from executing:

• Mismatched Columns & Values: You listed email and picture in the INSERT column list, but didn't include their values in the VALUES section. This causes an immediate SQL syntax error.
• Broken String Concatenation: The way you're building the SQL string has duplicate quote pairs ('" . "'"), which generates invalid SQL like '123', '', 'Jane', '', 'Doe'—inserting empty strings between real values and throwing off the column-value count.
• Missing Data: You didn't fetch email and picture from the Users table, so even if you fixed the query, you'd have no data to insert for those fields.
• No Error Handling: You're executing the INSERT but not checking if it succeeded, so you can't see the exact SQL errors that are happening.

Fixed Code #

Here's the revised version with all issues resolved, plus improved security using prepared statements (to avoid SQL injection and syntax errors):

<?php 
// Initialize variables, including missing email and picture
$osha = "";
$firstname = "";
$lastname = "";
$company = "";
$trade = "";
$email = "";
$picture = "";

if(isset($_POST['search'])) {
    // Connect to database
    $dbc = mysqli_connect("127.0.0.1", "root", "root","demodb");
    
    // Check connection first
    if (!$dbc) {
        die("Database connection failed: " . mysqli_connect_error());
    }

    // Sanitize input (though prepared statements will handle this better)
    $user_id = mysqli_real_escape_string($dbc, $_POST['user_id']);
    
    // Query Users table with all needed fields
    $query = "SELECT user_id, osha, firstname, lastname, company, trade, email, picture FROM Users WHERE user_id = ? LIMIT 1";
    
    // Use prepared statement for safe querying
    $stmt = mysqli_prepare($dbc, $query);
    mysqli_stmt_bind_param($stmt, "i", $user_id);
    mysqli_stmt_execute($stmt);
    $rs = mysqli_stmt_get_result($stmt);

    if (mysqli_num_rows($rs) == 1) {
        $row = mysqli_fetch_array($rs);
        // Assign all fetched values, including email and picture
        $osha = $row['osha'];
        $firstname = $row['firstname'];
        $lastname = $row['lastname'];
        $company = $row['company'];
        $trade = $row['trade'];
        $email = $row['email'];
        $picture = $row['picture'];

        // Insert into scan table using prepared statement
        $insertQuery = "INSERT INTO scan (user_id, osha, firstname, lastname, company, trade, email, picture) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        $insertStmt = mysqli_prepare($dbc, $insertQuery);
        // Bind parameters: i=int, s=string (adjust types if needed)
        mysqli_stmt_bind_param($insertStmt, "iissssss", $user_id, $osha, $firstname, $lastname, $company, $trade, $email, $picture);
        
        // Execute and check if insert succeeded
        if(mysqli_stmt_execute($insertStmt)) {
            // Optional: Uncomment to show success message
            // echo "Scan record added successfully";
        } else {
            // Debug: Show the error if insert fails
            echo "Error inserting scan record: " . mysqli_error($dbc);
        }

        // Close statements
        mysqli_stmt_close($stmt);
        mysqli_stmt_close($insertStmt);
    } else {
        echo "Undefined ID";
    }

    // Close database connection
    mysqli_close($dbc);
}
?>
<!DOCTYPE html>
<html>
<head>
<title> PHP FIND DATA </title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<form action="barcode.php" method="post">
Id:<input type="text" name="user_id"><br><br>
Osha #:<input type="text" name="osha" value="<?= htmlspecialchars($osha) ?>"><br><br>
First Name:<input type="text" name="firstname" value="<?= htmlspecialchars($firstname) ?>"><br>
<br>
Last Name:<input type="text" name="lastname" value="<?= htmlspecialchars($lastname) ?>"><br><br>
Company:<input type="text" name="company" value="<?= htmlspecialchars($company) ?>"><br><br>
Trade:<input type="text" name="trade" value="<?= htmlspecialchars($trade) ?>"><br><br>
<input type="submit" name="search" value="Find">
</form>
</body>
</html>

Key Improvements #

1. Prepared Statements: Replaced manual string concatenation with prepared statements to eliminate SQL injection risks and syntax errors from string handling.
2. Complete Data Fetch: Now retrieves email and picture from the Users table to match the scan table's required columns.
3. Error Handling: Added connection checks and INSERT success/failure feedback to help debug issues quickly.
4. Cleaner Code: Removed messy string concatenation that caused the original syntax errors.
5. Proper Resource Management: Closes prepared statements and database connections to avoid resource leaks.

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