How do you work with database meta key and values?

Question

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

There is a cool search console named Google which returns you an array with > 440.000 entries when you search for this topic. ;) — Mr. Jo, Mar 28 '19 at 10:11
Thanks for that incredibly unhelpful response. I have Googled the topic extensively and asked for help on forums without any answer. — John Smith, Mar 28 '19 at 10:17
Have you checked WooCommerce's documentation already? There must be a function for this already. — cabrerahector, Mar 28 '19 at 10:21
Because these are basics of WordPress development mate. For example when I search for: WordPress select from database: https://codex.wordpress.org/Class_Reference/wpdb. So this is the basic thing. WooCommerce implemented this for you so you can use functions to get order item values by keys. (see the documentation or search on SO): https://stackoverflow.com/questions/39401393/how-to-get-woocommerce-order-details — Mr. Jo, Mar 28 '19 at 10:23
Possible duplicate of [How to get WooCommerce order details](https://stackoverflow.com/questions/39401393/how-to-get-woocommerce-order-details) — Mr. Jo, Mar 28 '19 at 10:24
You've posted your select. Can you please also post the way you trying to execute the select? — Mr. Jo, Mar 28 '19 at 10:25
Thanks for your response. At the moment, I'm trying to execute the select just from the phpmyadmin console to make sure it returns the correct results before I execute it from code. — John Smith, Mar 28 '19 at 10:31
It has many items. I've posted the answer but thanks for your time. — John Smith, Mar 28 '19 at 10:52

score 3 · Accepted Answer · answered Mar 28 '19 at 12:32

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.

score 1 · Answer 2 · answered Mar 28 '19 at 10:51

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

If you want to do this now in WordPress, you can check my answer. — Mr. Jo, Mar 28 '19 at 12:26

How do you work with database meta key and values?

2 Answers2