点击Div的ID值传入SQL查询，实现表格数据条件筛选

需求说明 #

• 点击ID为all或one的Div时，显示全部订单查询结果
• 点击ID为two或three的Div时，在SQL查询的WHERE条件中添加pm.meta_key = 'additional_' AND pm.meta_value = [点击的Div ID]来筛选对应城市的订单

现有代码 #

前端城市切换按钮 #

<div class="cities">
    <div id="all" class="all city">
        All
    </div>
    <div id="one" class="one city">
        ONE
    </div>
    <div id="two" class="two city">
        TWO
    </div>
    <div id="three" class="three city">
        three
    </div>       
</div> 

后端PHP订单查询代码 #

<div class="all">
<?php 
global $wpdb;
$resi = $wpdb->get_results("SELECT 
    p.ID as order_id,                 
    max( CASE WHEN pm.meta_key = 'additional_' and p.ID = pm.post_id THEN pm.meta_value END ) as city
  
FROM
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
    join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
    join wp_wc_order_stats oimi on pm.post_id = oimi.order_id
WHERE
    post_type = 'shop_order' AND
    oim.meta_key = '_qty'
group by
    p.ID DESC
    ");

$i = 1;
echo '<table id="customers"><tr>';
echo '<th>ID order</th>';    
echo '<th>City</th>';
echo '</tr>';

foreach( $resi as $key => $res ){
    echo '<tr id="'.$i.'">'; 
    echo '<td class="row-data">' . $res->order_id. '</td>'; // 补全原代码遗漏的订单ID输出
    echo '<td class="row-data">' . $res->city. '</td>';
    echo '</tr>';
    $i++;
}
    echo '</table>';
?>
</div>

解决方案 #

1. 添加前端AJAX事件监听 #

给城市按钮绑定点击事件，通过AJAX传递筛选参数并更新表格：

<script>
document.addEventListener('DOMContentLoaded', function() {
    const cityButtons = document.querySelectorAll('.city');
    const tableContainer = document.querySelector('.all');

    cityButtons.forEach(button => {
        button.addEventListener('click', function() {
            const filterId = this.id;
            fetch(window.location.href, {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/x-www-form-urlencoded',
                    'X-WP-Nonce': '<?php echo wp_create_nonce("order_filter_nonce"); ?>'
                },
                body: 'action=filter_orders&city_filter=' + encodeURIComponent(filterId)
            })
            .then(response => response.text())
            .then(data => {
                tableContainer.innerHTML = data;
            })
            .catch(error => console.error('筛选出错:', error));
        });
    });
});
</script>

2. 后端添加AJAX处理逻辑 #

将以下代码添加到主题functions.php或当前页面PHP代码中：

<?php
// 处理订单筛选AJAX请求
add_action('wp_ajax_filter_orders', 'filter_orders_callback');
add_action('wp_ajax_nopriv_filter_orders', 'filter_orders_callback'); // 允许未登录用户访问（按需开启）

function filter_orders_callback() {
    // 验证安全令牌
    if (!isset($_POST['X-WP-Nonce']) || !wp_verify_nonce($_POST['X-WP-Nonce'], 'order_filter_nonce')) {
        wp_send_json_error('无效请求');
        wp_die();
    }

    global $wpdb;
    $filter_id = isset($_POST['city_filter']) ? sanitize_text_field($_POST['city_filter']) : 'all';

    // 基础SQL查询
    $sql = "SELECT 
        p.ID as order_id,                 
        max( CASE WHEN pm.meta_key = 'additional_' and p.ID = pm.post_id THEN pm.meta_value END ) as city
    FROM
        wp_posts p 
        join wp_postmeta pm on p.ID = pm.post_id
        join wp_woocommerce_order_items oi on p.ID = oi.order_id
        join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
        join wp_wc_order_stats oimi on pm.post_id = oimi.order_id
    WHERE
        post_type = 'shop_order' AND
        oim.meta_key = '_qty'";

    // 仅在点击two/three时添加筛选条件
    if ($filter_id !== 'all' && $filter_id !== 'one') {
        $sql .= $wpdb->prepare(" AND pm.meta_key = 'additional_' AND pm.meta_value = %s", $filter_id);
    }

    $sql .= " GROUP BY p.ID DESC";

    $resi = $wpdb->get_results($sql);

    // 输出表格内容
    $i = 1;
    echo '<table id="customers"><tr>';
    echo '<th>ID order</th>';    
    echo '<th>City</th>';
    echo '</tr>';

    foreach( $resi as $key => $res ){
        echo '<tr id="'.$i.'">'; 
        echo '<td class="row-data">' . $res->order_id. '</td>';
        echo '<td class="row-data">' . $res->city. '</td>';
        echo '</tr>';
        $i++;
    }
    echo '</table>';

    wp_die(); // 结束AJAX请求
}
?>

3. 调整原页面表格输出逻辑 #

修改原页面的PHP代码，区分普通请求和AJAX请求：

<div class="all">
<?php
// 非AJAX请求时输出初始表格
if (!wp_doing_ajax()) {
    global $wpdb;
    $resi = $wpdb->get_results("SELECT 
        p.ID as order_id,                 
        max( CASE WHEN pm.meta_key = 'additional_' and p.ID = pm.post_id THEN pm.meta_value END ) as city
    FROM
        wp_posts p 
        join wp_postmeta pm on p.ID = pm.post_id
        join wp_woocommerce_order_items oi on p.ID = oi.order_id
        join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
        join wp_wc_order_stats oimi on pm.post_id = oimi.order_id
    WHERE
        post_type = 'shop_order' AND
        oim.meta_key = '_qty'
    GROUP BY p.ID DESC
    ");

    $i = 1;
    echo '<table id="customers"><tr>';
    echo '<th>ID order</th>';    
    echo '<th>City</th>';
    echo '</tr>';

    foreach( $resi as $key => $res ){
        echo '<tr id="'.$i.'">'; 
        echo '<td class="row-data">' . $res->order_id. '</td>';
        echo '<td class="row-data">' . $res->city. '</td>';
        echo '</tr>';
        $i++;
    }
    echo '</table>';
}
?>
</div>

<!-- 引入之前的AJAX脚本 -->
<script>
document.addEventListener('DOMContentLoaded', function() {
    const cityButtons = document.querySelectorAll('.city');
    const tableContainer = document.querySelector('.all');

    cityButtons.forEach(button => {
        button.addEventListener('click', function() {
            const filterId = this.id;
            fetch(window.location.href, {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/x-www-form-urlencoded',
                    'X-WP-Nonce': '<?php echo wp_create_nonce("order_filter_nonce"); ?>'
                },
                body: 'action=filter_orders&city_filter=' + encodeURIComponent(filterId)
            })
            .then(response => response.text())
            .then(data => {
                tableContainer.innerHTML = data;
            })
            .catch(error => console.error('筛选出错:', error));
        });
    });
});
</script>

关键说明 #

• 使用wpdb->prepare()防止SQL注入，保障数据安全
• 添加WP Nonce安全验证，拦截恶意请求
• 针对all和one按钮不添加筛选条件，保持显示全部数据
• 修复了原代码中遗漏的订单ID列输出问题

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