如何编写WooCommerce短代码实现按序列号查询数据库自定义字段

现存问题 #

• 表单字段命名带$符号，容易和PHP变量混淆，取值逻辑也没有做非空判断，未提交表单时会抛出PHP警告
• SQL查询未加序列号过滤条件，始终查询全量数据，不会根据用户输入筛选
• 数据库查询结果没有遍历输出到表格，表格只有表头没有内容
• 单独定义的searchSerial函数没有实际匹配逻辑，和主函数的查询逻辑割裂
• 缺少生效日期、失效日期的查询逻辑，可通过增加postmeta子查询实现

完整实现代码 #

function wpb_demo_shortcode() { 
    global $wpdb;
    // 先获取用户输入的序列号
    $entered_serial = isset( $_GET['serial'] ) ? sanitize_text_field( $_GET['serial'] ) : '';
    ?>
    <form action="" method="get">   
        <label for="serial">Serial number:</label><br>   
        <input type="text" id="serial" name="serial" value="<?php echo esc_attr($entered_serial); ?>"><br>   
        <input type="submit" value="Submit"> 
    </form> 
    <?php

    // 只有用户输入了序列号才执行查询
    if( !empty($entered_serial) ){
        // 编写带序列号过滤的SQL，增加生效、失效日期的查询，可根据实际的postmeta键名修改
        $sql = $wpdb->prepare("
            SELECT DISTINCT 
                o.order_id, 
                o.`order_item_name`, 
                om.`meta_value` as 'bcs',  
                (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '2-certificate') as 'certificate',  
                (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '3-serial') as 'serial',
                -- 下面两个是生效、失效日期的查询，把meta_key改成你实际的字段名即可
                (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '4-effective_date') as 'effective_date',
                (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '5-expire_date') as 'expire_date'
            FROM `wp_woocommerce_order_items` as o, `wp_woocommerce_order_itemmeta` as om, `wp_postmeta` as pm 
            WHERE o.order_item_id = om.order_item_id 
                AND pm.`post_id` = o.order_id 
                AND om.`meta_key` = 'bcs' 
                AND (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '2-certificate') IS NOT null  
                AND (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '3-serial') IS NOT null
                -- 增加序列号匹配条件
                AND (select pm.`meta_value` from `wp_postmeta` as pm WHERE o.order_item_id = om.order_item_id AND pm.`post_id` = o.order_id AND pm.`meta_key` = '3-serial') = %s
        ", $entered_serial);

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

        // 输出表格
        echo '<table border="1" cellpadding="8" cellspacing="0">';
        echo '<tr>';
        echo '<th>Order ID</th>';
        echo '<th>Product Name</th>';
        echo '<th>BCS</th>';
        echo '<th>Serial</th>';
        echo '<th>Certificate</th>';
        echo '<th>生效日期</th>';
        echo '<th>失效日期</th>';
        echo '</tr>';

        if( empty($results) ){
            echo '<tr><td colspan="7" style="text-align:center;">未找到匹配的序列号数据</td></tr>';
        }else{
            // 遍历结果输出行
            foreach( $results as $row ){
                echo '<tr>';
                echo '<td>'.esc_html($row->order_id).'</td>';
                echo '<td>'.esc_html($row->order_item_name).'</td>';
                echo '<td>'.esc_html($row->bcs).'</td>';
                echo '<td>'.esc_html($row->serial).'</td>';
                echo '<td>'.esc_html($row->certificate).'</td>';
                echo '<td>'.esc_html($row->effective_date).'</td>';
                echo '<td>'.esc_html($row->expire_date).'</td>';
                echo '</tr>';
            }
        }
        echo '</table>';
    }
} 

add_shortcode('greeting', 'wpb_demo_shortcode');

需自行调整的内容 #

• 将SQL中生效日期、失效日期对应的meta_key值，替换为你后台实际设置的自定义字段键名
• 可自行调整表格的样式属性，匹配你的站点前端风格

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