PHP表单数据插入报错：product_pric字段整数值无效问题排查与解决

Hey there! Let's break down this error and work through how to fix it step by step.

错误原因 #

The core issue here is a data type mismatch:

• Your products table's product_pric column is defined as an integer (INT) type. When you tried to insert an empty string '' into it, MySQL's strict SQL mode (enabled by default) rejected this—empty strings aren't valid integers, so the database throws an error to protect data integrity.
• Looking at your INSERT query, multiple fields are being inserted as empty strings too. This means you're not validating or sanitizing user input from the form before sending it to the database, which is a recipe for both errors like this and security risks.

Fixes to Resolve This #

1. Handle Integer Values Correctly #

For the product_pric field, you need to ensure you're inserting either a valid integer or NULL (if the field allows empty values):

• If the price is a required field: Validate that the user entered a valid number, convert it to an integer, and reject empty inputs.
• If the price can be empty: Insert NULL instead of an empty string when there's no user input.

Here's how to handle this in PHP:

// Process product price
$product_pric = null;
if (isset($_POST['product_pric']) && trim($_POST['product_pric']) !== '') {
    $price_input = trim($_POST['product_pric']);
    // Ensure it's a valid non-negative number
    if (is_numeric($price_input) && $price_input >= 0) {
        $product_pric = (int)$price_input;
    } else {
        die("Error: Please enter a valid non-negative price.");
    }
} else {
    // Uncomment this line if price is a required field
    // die("Error: Product price cannot be empty.");
}

// Validate other required fields (e.g., product name)
$product_name = isset($_POST['product_name']) ? trim($_POST['product_name']) : '';
if (empty($product_name)) {
    die("Error: Product name cannot be empty.");
}

2. Add Frontend Validation #

Catch invalid inputs early with HTML form validation—this gives users immediate feedback without hitting your backend:

<!-- Force numeric input and require it if needed -->
<input type="number" name="product_pric" min="0" step="1" required>

type="number" restricts input to numbers, min="0" blocks negative values, and required makes the field mandatory.

3. Use Prepared Statements (Critical for Security & Data Types) #

Your current query directly inserts user input into SQL, which is a massive SQL injection risk. Prepared statements (using PDO or mysqli) not only fix security issues but also automatically handle data type matching between PHP and MySQL.

Example with PDO:

// Assuming you have a PDO connection set up
try {
    $stmt = $pdo->prepare("INSERT INTO products 
        (product_cat, product_name, product_pric, product_desc, product_image, product_keyword, product_ingrid)
        VALUES (:cat, :name, :price, :desc, :image, :keyword, :ingrid)");
    
    $stmt->execute([
        ':cat' => $_POST['product_cat'],
        ':name' => $product_name,
        ':price' => $product_pric,
        ':desc' => $_POST['product_desc'] ?? '',
        ':image' => $_POST['product_image'] ?? '',
        ':keyword' => $_POST['product_keyword'] ?? '',
        ':ingrid' => $_POST['product_ingrid'] ?? ''
    ]);
    
    echo "Product inserted successfully!";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

4. Verify Your Database Column Definition #

Double-check the product_pric column in your products table:

• If the price is required, ensure the column is set to NOT NULL.
• If it can be empty, make sure NULL is allowed (uncheck the NOT NULL option in your database manager).

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