CF7页面文本框序列号验证：对接phpMyAdmin数据库查重

Got it, let's break down how to implement this serial number validation with duplicate checking for your Contact Form 7 setup—including field highlighting and even marking individual duplicate serials. Here's a step-by-step solution tailored to your needs:

1. Set Up the Contact Form 7 Field First #

First, configure your textarea in CF7 with an ID (for easy JS targeting) and enforce the character limit. Your CF7 form code should look like this:

<label>产品序列号（每行输入一个10位号码）
[textarea serial-numbers id:serial-numbers maxlength:1010 rows:10 placeholder="例如：1234567890"]</label>
[submit "提交"]

This gives the textarea a unique ID (serial-numbers), caps input at 1010 characters, and lets users know the expected format.

2. Backend Validation (Critical for Security) #

Frontend checks can be bypassed, so we need to validate against the database on the server. Add this code to your theme's functions.php file (or a custom plugin):

add_filter('wpcf7_validate_textarea', 'cf7_validate_serial_numbers', 10, 2);
function cf7_validate_serial_numbers($result, $tag) {
    // Only target our serial number textarea
    if ('serial-numbers' !== $tag->name) return $result;

    $user_input = trim($_POST['serial-numbers']);
    if (empty($user_input)) return $result;

    // Split input into individual serials (handles all line break types)
    $user_serials = preg_split('/\r\n|\r|\n/', $user_input);
    $user_serials = array_filter(array_map('trim', $user_serials)); // Remove empty lines

    // Connect to your database (using WordPress's wpdb class since CF7 runs on WP)
    global $wpdb;
    $table_name = $wpdb->prefix . 'your_serial_table'; // Replace with your actual table name
    $existing_serials = $wpdb->get_col("SELECT serial_number FROM $table_name"); // Replace with your serial column name

    $duplicates = [];
    foreach ($user_serials as $serial) {
        // First validate the format is 10 characters
        if (!preg_match('/^[0-9A-Za-z]{10}$/', $serial)) {
            $result->invalidate($tag, '请确保每个序列号是10位数字或字母！');
            return $result;
        }
        // Check if the serial exists in the database
        if (in_array($serial, $existing_serials)) {
            $duplicates[] = $serial;
        }
    }

    if (!empty($duplicates)) {
        // Store duplicates in session to pass to frontend
        session_start();
        $_SESSION['cf7_duplicate_serials'] = $duplicates;
        // Add error message to CF7
        $result->invalidate($tag, '发现重复的序列号：' . implode(', ', $duplicates));
    }

    return $result;
}

Make sure to replace your_serial_table and serial_number with your actual database table and column names.

3. Frontend Highlighting (Field + Individual Serials) #

Next, we'll add JavaScript to highlight the textarea and mark duplicate serials when validation fails. First, enqueue the script (add this to functions.php):

add_action('wp_enqueue_scripts', 'cf7_enqueue_serial_validation_script');
function cf7_enqueue_serial_validation_script() {
    wp_enqueue_script(
        'cf7-serial-validation',
        get_template_directory_uri() . '/js/cf7-serial-validation.js', // Create this JS file in your theme
        ['jquery'],
        '1.0',
        true
    );

    // Pass duplicate serials to frontend if they exist
    if (session_id() && isset($_SESSION['cf7_duplicate_serials'])) {
        wp_localize_script(
            'cf7-serial-validation',
            'CF7SerialData',
            ['duplicates' => $_SESSION['cf7_duplicate_serials']]
        );
        // Clear the session so it doesn't persist on page refresh
        unset($_SESSION['cf7_duplicate_serials']);
    }
}

Then create the cf7-serial-validation.js file in your theme's js folder with this code:

document.addEventListener('wpcf7submit', function(event) {
    const form = event.target;
    const serialTextarea = form.querySelector('#serial-numbers');
    
    // Reset previous highlights
    serialTextarea.classList.remove('duplicate-field');
    serialTextarea.innerHTML = serialTextarea.textContent; // Clear old <mark> tags

    // Check if validation failed due to duplicates
    if (event.detail.status === 'validation_failed') {
        const errorTips = form.querySelectorAll('.wpcf7-not-valid-tip');
        let hasDuplicateError = false;

        errorTips.forEach(tip => {
            if (tip.textContent.includes('重复的序列号')) {
                hasDuplicateError = true;
            }
        });

        if (hasDuplicateError && typeof CF7SerialData !== 'undefined') {
            // Highlight the entire textarea
            serialTextarea.classList.add('duplicate-field');

            // Mark individual duplicate serials
            let textContent = serialTextarea.textContent;
            CF7SerialData.duplicates.forEach(serial => {
                const regex = new RegExp(`\\b${serial}\\b`, 'g');
                textContent = textContent.replace(regex, `<mark class="duplicate-serial">${serial}</mark>`);
            });
            serialTextarea.innerHTML = textContent;
        }
    }
});

4. Add CSS for Visual Highlighting #

Finally, add these styles to your theme's style.css to make the highlights visible:

/* Highlight the textarea when duplicates are found */
.duplicate-field {
    border: 2px solid #dc3232 !important;
    background-color: #fef0f0 !important;
}

/* Highlight individual duplicate serials */
mark.duplicate-serial {
    background-color: #ffeb3b;
    padding: 2px 4px;
    border-radius: 3px;
    font-weight: 500;
}

Important Notes #

• Security: Never rely solely on frontend validation—backend checks are mandatory to prevent malicious submissions.
• Database Connection: If you're not using WordPress (unlikely since CF7 is a WP plugin), adjust the database connection code to match your setup.
• Session Handling: The code uses PHP sessions to pass duplicate serials to the frontend; ensure your server has sessions enabled.

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