表单无法写入数据库求助：无报错但数据无法插入

Let’s walk through the hidden issues in your code that are blocking data from saving to the database—even though everything looks like it’s working, there are several critical problems here.

1. Critical Variable Assignment Mistake #

First, look at how you’re passing data to insertDb in your page code:

$result = insertDb('products', array(
    'category' => $_POST['name'], // ❌ This is wrong!
    'name' => $_POST['name'],
    'price' => $_POST['price'],
    'active' => $_POST['active'], 
    'condition' => $_POST['condition']
));

You’re assigning $_POST['name'] to both the category and name fields. The category should come from your select box, so it should be $_POST['category'] instead. This is a logical error that would corrupt your data even if insertion worked.

2. MySQL Reserved Keyword Conflict #

Your products table has a column named condition—this is a reserved keyword in MySQL (used in clauses like WHERE ... CONDITION). When you try to insert into this field without escaping it, MySQL throws a syntax error, but you’re not capturing that error.

Fix this by wrapping reserved column names in backticks (```) in your SQL query.

3. No Error Handling for Database Queries #

Your insertDb function runs mysqli_query($link, $query) but never checks if it succeeded. Even if the query fails, you just return true, so you have no way of knowing what broke.

Add error checking after mysqli_query during development:

if (!mysqli_query($link, $query)) {
    die(mysqli_error($link)); // Shows raw error for debugging
}

For production, replace this with a user-friendly message instead of exposing raw database errors.

4. SQL Injection Vulnerability & Data Type Issues #

You’re directly concatenating user input into your SQL query—this is a huge security risk (SQL injection) and can cause data type mismatches (like inserting string values into the price INT column).

The proper fix is to use prepared statements with parameter binding. Here’s a revised insertDb function that fixes this, handles reserved keywords, and includes error checking:

function insertDb($table, $data, $validation = null) { 
    global $link; 

    // Trim all input data
    foreach($data as $key => &$value) { 
        $data[$key] = trim($value); 
    } 

    // Validation logic (keep as-is, note typo: min_lenght → min_length)
    $errors = [];
    if(!empty($validation)) { 
        foreach($validation as $field => $rules) { 
            foreach($rules as $rule_type => $rule_value) { 
                if($rule_type == 'unique') { 
                    $check_exists = find('first', $table, array(array('field' => $field, 'operator' => '=', 'value' => $data[$field]))); 
                    if(!empty($check_exists)) { 
                        $errors['error'][$field][$rule_type] = "$field must be unique"; 
                    } 
                } elseif($rule_type == 'min_lenght') { 
                    if(strlen($data[$field]) < $rule_value) { 
                        $errors['error'][$field][$rule_type] = "$field requires at least $rule_value characters"; 
                    } 
                } 
            } 
        } 
        if(!empty($errors)) { 
            return $errors; 
        } 
    } 

    // Prepare insert with backticks for reserved words
    $fields = array_map(function($key) {
        return "`$key`"; // Wrap each field to avoid keyword conflicts
    }, array_keys($data));
    $placeholders = array_fill(0, count($data), '?');

    $query = "INSERT INTO `$table` (" . implode(',', $fields) . ") VALUES (" . implode(',', $placeholders) . ")";
    
    $stmt = mysqli_prepare($link, $query);
    if (!$stmt) {
        return ['error' => ['database' => mysqli_error($link)]];
    }

    // Bind parameters (all as strings for simplicity; adjust types if needed)
    $types = str_repeat('s', count($data));
    mysqli_stmt_bind_param($stmt, $types, ...array_values($data));

    // Execute and check result
    if (!mysqli_stmt_execute($stmt)) {
        return ['error' => ['database' => mysqli_stmt_error($stmt)]];
    }

    mysqli_stmt_close($stmt);
    return true; 
}

5. Additional Debugging & Fixes #

• Enable PHP error reporting during development to catch hidden issues:

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

• Handle checkbox values: The active checkbox won’t send a value if unchecked. Update your page code to set a default:

  'active' => isset($_POST['active']) ? 'Y' : 'N',
  

• Verify $_POST data: Add var_dump($_POST); at the top of your page to confirm all form fields are submitting correctly.
• Check database connection: Add a check in configuration.php to ensure your connection works:

  if (!$link) {
      die("Connection failed: " . mysqli_connect_error());
  }
  

Final Page Code Adjustment #

Correct the category assignment and add error display:

<?php 
include "configuration.php";
error_reporting(E_ALL);
ini_set('display_errors', 1);

$errors = [];
if(isset($_POST['submit'])) { 
    $result = insertDb('products', array(
        'category' => $_POST['category'], // Fixed!
        'name' => $_POST['name'],
        'price' => $_POST['price'],
        'active' => isset($_POST['active']) ? 'Y' : 'N',
        'condition' => $_POST['condition']
    )); 

    if($result === true) { 
        header("Location: products.php");
        exit; // Always exit after redirect to prevent extra code execution
    } else {
        $errors = $result;
    }
} 
?>

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