We help IT Professionals succeed at work.

Wordpress Woo-Commerce Query

I am using Woo-Commerce with Wordpress.  I am trying to write a query that pulls specific Meta_Key Values but I'm not 100% on how to write it.

I need the order ID to locate all of the meta data, that is no problem, then I need to query those fields with that ID and pull specific values from meta_keys.  The query below is just a start but will get me going so I can ask additional questions.
Right now I have four meta_key that I need to pull the value from;
1.First Name (meta_key value / First Name)
2. Middle Name (meta_key value / Middle Name)
3. Last Name (meta_key value / Last Name)
4. Company Name (meta_key value / Company Name)

<?php
 global $wpdb;
        $order_id = $_POST['order_number'];
  
        
         $order = $wpdb->get_results ( "SELECT order_item_id, order_item_name FROM woocommerce_order_items WHERE order_id = '$order_id'");

         foreach ($order as $page) {
            echo $page->order_item_id.'<br/>'.$page->order_item_name;
            $meta_id = $page->order_item_id;
            $entity = $page->order_item_name;

    }
        
     
$sql = $wpdb->get_results ( "SELECT
    CASE WHEN $entity = 'ABC' 
    THEN CONCAT(meta_key ='First Name', IF (CHAR_LENGTH(meta_key = 'Middle Name') > 0, CONCAT(meta_key = 'Middle Name')), meta_key ='Last Name')
    
    WHEN $entity = DEF' 
    THEN CONCAT(meta_key ='First Name', IF (CHAR_LENGTH(meta_key = 'Middle Name') > 0, CONCAT(meta_key = 'Middle Name')), meta_key ='Last Name')
    
ELSE meta_key ='LLCName'
    END 
    AS 'f1_02(0)'
    FROM
    woocommerce_order_itemmeta
    WHERE order_item_id = '$meta_id'");

       foreach ( $sql as $page )
{
   echo $page->f1_02(0);

}

Open in new window

Comment
Watch Question

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
If you run this query in your dbms, it will I believe return 4 rows, each row being a "key/value pair"
SELECT *
FROM  woocommerce_order_itemmeta meta
WHERE meta.meta_key  IN ('First Name', 'Middle Name','Last Name','Company Name')
AND meta.order_item_id = ?

Open in new window

But case expressions don't work "across rows" they work on a "per row" basis instead and thse concatenations won't work.
So you need to make a decision: "Will I do the post-processing of the this data in PHP or in SQL?"

While I'm not a PHP programmer personally it seems more logical to do it there

Author

Commented:
And then just run a "foreach" or WHILE statement after it?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
If using php gather the information as you would from any multi row query ...

If you really want the logic done in sql please supply a mockup of what the values are individually and what you want them to be as a single row.