Get orders from a SQL query displayed on as a list in Woocommerce

Question

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");

Answer 1

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

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();
}

Code goes in function.php file of your active child theme (or html" target="_blank">active theme).
Tested and works.

USAGE:

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

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

echo do_shortcode( "[sqlquery_shortcode]" );

or in between html tags:

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

You will get something like: