如何将带有短代码的 SQL 查询显示到 WordPress 前端

Question

我使用文字压缩与伍商务。某些订单不仅应该在后端可见，在前端也应该可见。现在我已经创建了一个SQL查询（在phpMy管理员中工作）。结果应显示在网站上的表格中。SELECT            p.ID as order_id,            p.post_date,            max( CASE WHEN pm.meta_key = 'donateAnonym' AND p.ID = pm.post_id THEN pm.meta_value END ) as donateAnonym,            max( CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,            max( CASE WHEN pm.meta_key = '_billing_first_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,            max( CASE WHEN pm.meta_key = '_billing_last_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,            max( CASE WHEN pm.meta_key = '_billing_city' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,            max( CASE WHEN pm.meta_key = '_billing_state' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,            max( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) as order_total,            ( SELECT GROUP_CONCAT( order_item_name separator '|' ) FROM wp_woocommerce_order_items WHERE order_id = p.ID ) as order_items        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        GROUP BY            p.ID我已将此代码添加到主题的函数.php文件中。现在，可以通过添加到任何页面或帖子来显示来自函数的信息。[showAllHrOrdersTable]add_shortcode( 'showAllHrOrdersTable', 'showAllHrOrders' );function showAllHrOrders( $args ) {}如何实现我的sql查询而没有任何错误？

Answer 1

您忘记在 以下位置转义引号：$SQLstring

$SQLstring = 'SELECT

            p.ID as order_id,

            p.post_date,

            max( CASE WHEN pm.meta_key = \'donateAnonym\' AND p.ID = pm.post_id THEN pm.meta_value END ) as donateAnonym,

            max( CASE WHEN pm.meta_key = \'_billing_email\' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,

            max( CASE WHEN pm.meta_key = \'_billing_first_name\' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,

            max( CASE WHEN pm.meta_key = \'_billing_last_name\' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,

            max( CASE WHEN pm.meta_key = \'_billing_city\' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,

            max( CASE WHEN pm.meta_key = \'_billing_state\' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,

            max( CASE WHEN pm.meta_key = \'_order_total\' AND p.ID = pm.post_id THEN pm.meta_value END ) as order_total,

            ( SELECT GROUP_CONCAT( order_item_name separator \'|\' ) FROM wp_woocommerce_order_items WHERE order_id = p.ID ) as order_items

        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

        GROUP BY

            p.ID';

更好，更易读：

$SQLstring = "SELECT

            p.ID as order_id,

            p.post_date,

            max( CASE WHEN pm.meta_key = 'donateAnonym' AND p.ID = pm.post_id THEN pm.meta_value END ) as donateAnonym,

            max( CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,

            max( CASE WHEN pm.meta_key = '_billing_first_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,

            max( CASE WHEN pm.meta_key = '_billing_last_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,

            max( CASE WHEN pm.meta_key = '_billing_city' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,

            max( CASE WHEN pm.meta_key = '_billing_state' AND p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,

            max( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) as order_total,

            ( SELECT GROUP_CONCAT( order_item_name separator '|' ) FROM wp_woocommerce_order_items WHERE order_id = p.ID ) as order_items

        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

        GROUP BY

            p.ID";