Get order items data values in Woocommerce

Question

In Woocommerce, Currently I have a form where the customer inputs an order number, their name and their post number. The code goes through this code and if everything is correct, the customer is shown the items he has ordered, which leads to my problem:

In the WooCommerce tables, both the price, and the quantity of the are in the same column, and I can't seem to get the values individually.

I have tried different ways to do my current query, but this is the latest one I have been closest to success:

$sql2 = "SELECT *
FROM wp_woocommerce_order_items
LEFT JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
WHERE wp_woocommerce_order_items.order_id = $ordernumber
AND (meta_key IN ('_line_total', '_qty'))";

Also I'm printing the results with a foreach loop: (Also sorry for the Finnish bits in the picture and the code)

 foreach ($res2 as $row) {
   echo "<input type='checkbox' name='productinfo[]'><label> Tuotenimi: " . $row['order_item_name'] . "<br /> Kappalehinta: " . $row['meta_value'] ." €"
          . "<br /><br />";
        }

However, the problem with this code is, that adding the "_qty" column to the AND clause causes the results to come out as double as seen here (Also sorry for the mostly Finnish website):

I'd like to be able to print the quantity and price as individual elements/variables and to be able to additions with the prices, to get the total price of all of the products.

Please ask me anything if the code or the question is confusing!

UPDATE

So I was adviced by Dipmala to use group by which helped me to get the right amount of results. However, I still don't know how to print the quantity as separate with the price. /Puppe

there is easier way for to get the order details using `wc_get_order` and there is already answer explaining everything you need to know to get the data you want : https://stackoverflow.com/questions/39401393/how-to-get-woocommerce-order-details — kashalo, Sep 19 '18 at 11:55
Possible duplicate of [How to get WooCommerce order details](https://stackoverflow.com/questions/39401393/how-to-get-woocommerce-order-details) — kashalo, Sep 19 '18 at 11:55
@dipmala Sorry for the super late answer, I had to get off for a while but, is there any specific value I should order them by? Since I am not seeing any difference. — Puppe, Sep 20 '18 at 08:07
@LoicTheAztec Tried using your comment you posted in kashalo's comments article as a reference, but honestly I am not sure how to use the wc_ functions, could you give me an example with either them or the SELECT DISTINCT method by any chance? Thank you very much. — Puppe, Sep 20 '18 at 08:09

LoicTheAztec · Accepted Answer · 2018-09-20T10:31:13.747

1

Instead using a SQL query try this (where $order_id is the post ID (or the order number)):

$order = wc_get_order( $order_id );

echo '<p>'. __('Order total: ') . $order->get_total() . '</p>';

foreach ( $order->get_items() as $item ){
    echo '<p>';
    echo __('Product name: ') . $item->get_name() . '<br>';
    echo __('Quantity: ') . $item->get_quantity() . '<br>';
    echo __('Line total: ') . wc_price($item->get_total()) . '</p>';
}

answered Sep 20 '18 at 08:56

LoicTheAztec

229,944
23
356
399

almost forgot to ask swell, how would printing out the total price for the order go? Is there a function for that or would I need to go for additioning the item prices together? – Puppe Sep 20 '18 at 09:27
That makes a lot of sense, I feel a bit dumb now. Thank you very much! – Puppe Sep 20 '18 at 10:37

Get order items data values in Woocommerce

1 Answers1