[SOLUTION] Wordpress Woo-Commerce Query

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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 52

PortletPaulEE Topic AdvisorCommented: 2017-11-07

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 = ?

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 9

Robert GranlundAuthor Commented: 2017-11-08

And then just run a "foreach" or WHILE statement after it?

PortletPaulEE Topic AdvisorCommented: 2017-11-08

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.

Wordpress Woo-Commerce Query

Who is Participating?