在WordPress的PHP SQL查询中使用变量实现日期筛选

Hey there! Let's get your date-based database lookup working properly. Your current code has a few syntax and security issues that we can clean up to make it safe and functional.

Key Issues in Your Original Code #

• SQL query syntax error when concatenating the $num variable
• Directly using $_POST data in an SQL query poses a SQL injection risk (never do this!)
• Redundant variable assignment that can cause unexpected behavior

Corrected Code #

First, here's the updated PHP and HTML code with proper WordPress best practices:

PHP Code

<?php
// Set default date or use submitted date if form is posted
$selected_date = date('Y-m-d'); // Default to today's date
if (isset($_POST['subject'])) {
    $selected_date = sanitize_text_field($_POST['subject']);
}

// Use WordPress's $wpdb->prepare to safely run the query
global $wpdb;
$table_name = $wpdb->prefix . 'youtube_programs'; // Use prefix to be database agnostic
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM %i WHERE rating_date = %s",
        $table_name,
        $selected_date
    )
);
?>

HTML Form Code

<form name="date-form" action="" method="post">
    <input 
        type="date" 
        name="subject" 
        id="subject" 
        value="<?php echo esc_attr($selected_date); ?>" 
    />
    <input type="submit" value="View Records" />
</form>

<!-- Display the results -->
<?php if (!empty($results)) : ?>
    <h3>Records for <?php echo esc_html($selected_date); ?></h3>
    <ul>
        <?php foreach ($results as $program) : ?>
            <li><?php echo esc_html($program->some_field_name); ?></li>
            <!-- Replace "some_field_name" with actual column names from your table -->
        <?php endforeach; ?>
    </ul>
<?php elseif (isset($_POST['subject'])) : ?>
    <p>No records found for <?php echo esc_html($selected_date); ?>.</p>
<?php endif; ?>

Important Notes #

• Security First: Using $wpdb->prepare is mandatory when adding dynamic values to SQL queries in WordPress. It automatically escapes the input to prevent SQL injection attacks.
• Database Prefix: Using $wpdb->prefix instead of hardcoding wpsk_ makes your code work with any WordPress installation (since prefixes can be changed during setup).
• Sanitization & Escaping: We use sanitize_text_field() for the POST data and esc_attr()/esc_html() when outputting values to prevent XSS attacks.
• Date Format: Make sure the rating_date column in your database uses the YYYY-MM-DD format (which matches the HTML5 date input's default output).

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