从 Woocommerce 中显示为列表的 SQL 查询中获取订单 [英] Get orders from a SQL query displayed on as a list in Woocommerce

问题描述

在 woocommerce 中，我正在尝试创建一个短代码以放入我的 function.php 文档，该文档在页面上显示自定义数据库查询.虽然它有问题，但我不确定是什么.

In woocommerce, I'm trying to create a shortcode to put in my function.php document, that displays a custom database query on a page. There's something wrong with it though and i'm not sure what.

function sqlquery_shortcode($atts) {
    global $wpdb;
    $results = $wpdb->get_results("
        SELECT a.order_id, a.order_item_id, a.order_item_name, b.meta_key, b.meta_value
        FROM 7pW9iX3L_woocommerce_order_items a
        JOIN 7pW9iX3L_woocommerce_order_itemmeta  b 
            ON a.order_item_id = b.order_item_id
        JOIN 7pW9iX3L_posts c               
            ON a.order_id = c.ID
        JOIN 7pW9iX3L_postmeta d
            ON c.ID = d.post_id
        WHERE order_item_type = 'line_item'
    ");

    // Loop through each order post object
    foreach($results as $result) {
        $order_id = $result->ID; // The Order ID

        // Get an instance of the WC_Order Object
        $order = wc_get_order( $result->ID );
    }
}
add_shortcode("sqlquery_shortcode");

推荐答案

有很多小错误和错误.您的 SQL 查询不正确，因此我已进行更改以使其首先起作用.

There is many little errors and mistakes. Your SQL query was incorrect, so I have changed in a way to make it work first.

这是您重新访问的代码，用于从 html 表中的 SQL 查询获取订单行项目.这只是一个功能齐全且经过测试的示例代码，您应该根据需要对其进行更改:

Here is your revisited code to get order line items from your SQL query in an html table. It's just a functional and tested example code, that you should need to change for your needs:

add_shortcode('sqlquery_shortcode', 'sqlquery_shortcode');
function sqlquery_shortcode( $atts ) {
    global $wpdb;

    $results = $wpdb->get_results("
        SELECT a.order_id, a.order_item_id AS item_id, a.order_item_name AS item_name,
        b.meta_value AS product_id, c.meta_value AS quantity, d.meta_value AS subtotal
        FROM {$wpdb->prefix}woocommerce_order_items a
        JOIN {$wpdb->prefix}woocommerce_order_itemmeta  b ON a.order_item_id = b.order_item_id
        JOIN {$wpdb->prefix}woocommerce_order_itemmeta  c ON a.order_item_id = c.order_item_id
        JOIN {$wpdb->prefix}woocommerce_order_itemmeta  d ON a.order_item_id = d.order_item_id
        WHERE a.order_item_type = 'line_item'
        AND b.meta_key = '_product_id'
        AND c.meta_key = '_qty'
        AND d.meta_key = '_line_subtotal'
        ORDER BY a.order_id DESC
    ");

    // Start buffering
    ob_start();

    echo '<table><tr>';
    echo '<th>Order ID</th>';
    echo '<th>Item ID</th>';
    echo '<th>Product ID</th>';
    echo '<th>Item Name</th>';
    echo '<th>Quantity</th>';
    echo '<th>Subtotal</th>';
    echo '</tr>';
    // Loop through each order post object
    foreach( $results as $result ){

        // Html display
        echo '<tr>';
        echo '<td>' . $result->order_id . '</td>';
        echo '<td>' . $result->item_id . '</td>';
        echo '<td>' . $result->product_id . '</td>';
        echo '<td>' . $result->item_name . '</td>';
        echo '<td>' . $result->quantity . '</td>';
        echo '<td>' . $result->subtotal . '</td>';
        echo '</tr>';
    }

    echo '</table>';

    // Return output (render the buffered data)
    return ob_get_clean();
}

代码位于活动子主题(或活动主题)的 function.php 文件中.经测试有效.

Code goes in function.php file of your active child theme (or active theme). Tested and works.

用法:

您将将该短代码 [sqlquery_shortcode] 粘贴到页面或帖子的 Wordpress 文本编辑器中.

You will paste that shortcode [sqlquery_shortcode] in the Wordpress text editor of a page or a post.

或者您也可以将以下内容粘贴到任何 php 模板或文件中:

Or you can also paste the following in any php template or file:

echo do_shortcode( "[sqlquery_shortcode]" );

或在 html 标签之间:

or in between html tags:

<?php echo do_shortcode( "[sqlquery_shortcode]" ); ?>

你会得到类似的东西:

这篇关于从 Woocommerce 中显示为列表的 SQL 查询中获取订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！