WordPress批量插入数据报错：wpdb::prepare调用错误及参数问题

Hey Julian, let's figure out what's going wrong here and fix those errors for you.

Why You're Seeing These Errors #

The core issue is that wpdb::prepare() doesn't accept arrays as direct values—it expects individual scalar values (strings, numbers, etc.) to match each placeholder in your SQL query. When you pass an array to it, WordPress throws that "Unsupported value type (array)" notice, and then the underlying mysqli_real_escape_string() function freaks out because it's trying to process an array like a string, hence the warning.

Step-by-Step Fix for Bulk Insertion #

Let's walk through a proper way to bulk insert rows into your custom WordPress table, while adhering to WordPress's security best practices.

First, let's assume your $_POST data looks something like this (adjust based on your actual form structure):

// Example $_POST structure for multiple rows
$_POST = [
    'custom_rows' => [
        ['name' => 'Item 1', 'price' => '10', 'quantity' => '2'],
        ['name' => 'Item 2', 'price' => '15', 'quantity' => '3'],
        // ... more rows
    ]
];

Here's the corrected code:

// Check if we have valid data coming in
if (isset($_POST['custom_rows']) && is_array($_POST['custom_rows'])) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'your_custom_table'; // Replace with your actual table name
    $table_columns = ['name', 'price', 'quantity']; // Match your table's column names

    $placeholders = [];
    $values = [];

    // Loop through each row of submitted data
    foreach ($_POST['custom_rows'] as $row) {
        // Sanitize each field to prevent SQL injection and invalid data
        $sanitized_name = sanitize_text_field($row['name']);
        $sanitized_price = (float) $row['price']; // Cast to appropriate type
        $sanitized_quantity = (int) $row['quantity'];

        // Add a set of placeholders for this row (match types to your columns: %s=string, %d=int, %f=float)
        $placeholders[] = '(%s, %f, %d)';
        // Add the sanitized values to our values array (in the same order as placeholders)
        $values[] = $sanitized_name;
        $values[] = $sanitized_price;
        $values[] = $sanitized_quantity;
    }

    if (!empty($placeholders)) {
        // Build the full SQL query
        $sql = sprintf(
            "INSERT INTO %s (%s) VALUES %s",
            $table_name,
            implode(', ', $table_columns),
            implode(', ', $placeholders)
        );

        // Prepare the query with all our values (this is the key part—no arrays passed directly!)
        $prepared_sql = $wpdb->prepare($sql, $values);
        // Execute the insertion
        $insert_result = $wpdb->query($prepared_sql);

        // Handle success/error
        if ($insert_result === false) {
            echo "Insert failed: " . $wpdb->last_error;
        } else {
            echo "Successfully inserted " . $insert_result . " rows!";
        }
    }
}

Key Fixes Explained #

• No Direct Arrays to prepare(): We break down each row's data into individual scalar values, which wpdb::prepare() can handle correctly.
• Data Sanitization: We use WordPress's built-in sanitization functions (like sanitize_text_field) and type casting to ensure only valid, safe data goes into the database.
• Proper Placeholder Matching: Each placeholder (%s, %d, %f) matches the data type of the column it's inserting into, which avoids type-related warnings.

If Your $_POST Structure Is Different #

If your form submits data as separate arrays for each column (e.g., $_POST['name'] is an array of all names, $_POST['price'] is an array of all prices), adjust the loop to index into each column array:

$names = $_POST['name'];
$prices = $_POST['price'];
$quantities = $_POST['quantity'];

for ($i = 0; $i < count($names); $i++) {
    $sanitized_name = sanitize_text_field($names[$i]);
    $sanitized_price = (float) $prices[$i];
    $sanitized_quantity = (int) $quantities[$i];

    // Add to placeholders and values as before
}

Just make sure to add checks to ensure all column arrays are the same length to avoid index errors!

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