Styjun

I’m trying to use SQL to retrieve Woocommerce order item meta keys and values so that I can summarise the information in my own report, but I am really struggling to extract meta keys and values as columns. Could anyone provide any SQL examples? Is there a Wordpress API I can use for metadata?

I have tried creating the following SQL as a basis for extending the query for product information. It returns the right kind of data but only a single row:

SELECT O.order_item_id, O.order_item_name,
 MAX(IF(OIM.meta_key = 'class', OIM.meta_value, NULL)) AS class
FROM wp_woocommerce_order_items AS O
LEFT JOIN wp_woocommerce_order_itemmeta AS OIM on O.order_item_id = 
OIM.order_item_id
WHERE O.order_item_name='Smiths Night School';

Thanks

I'm glad that you got it! This is the best way to learn things. To help you a bit, I'll show you the correct way to do this in WordPress (PHP):

<?php

//DB
global $wpdb; 

//Get results from the database
$results = $wpdb->get_results( "SELECT O.order_item_id, O.order_item_name, MAX(CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value END) AS 'class', MAX(CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value END) AS 'fees' FROM wp_woocommerce_order_items O INNER JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id WHERE O.order_item_name='Smiths Night School' GROUP BY O.order_item_id, O.order_item_name" );

foreach ( $results as $result ) 
 echo $result->your_field_from_the_table;

WordPress defines a class called wpdb, which contains a set of functions used to interact with a database. It's a good way using this class, because it's simple and save.

You also don't need to define your password and user of the database within any PHP file (What you need to do if you use the PHP database functionality). If you use wpdb, this information comes directly out of the wp-config.php file which is a bit more safe.

The following code works well. This query returns class and fee-type information for every order_item_name of "Smiths Night School".

SELECT 
 O.order_item_id, O.order_item_name,
 MAX(CASE WHEN OIM.meta_key = 'class' THEN OIM.meta_value END) AS 'class',
 MAX(CASE WHEN OIM.meta_key = 'fee-type' THEN OIM.meta_value END) AS 'fees'
FROM wp_woocommerce_order_items O
INNER JOIN wp_woocommerce_order_itemmeta OIM ON O.order_item_id = OIM.order_item_id
WHERE O.order_item_name='Smiths Night School'
GROUP BY O.order_item_id, O.order_item_name