Hi,

So I'd like to use wpdatatables using PHP Serialized Array. Before using my own query, I decided to just copy and paste the sample code in the documentation and tweaked it a little bit to see if it's really working. I followed the instructions in the video and it showed the result. But when I added the URL and hit save, it says 'No data.' What could be the problem?

Your prompt response is highly appreciated.

Sincerely,

Eli

Hi Bogdan,

Adding 

$data = trim( $data );

did the trick.

So while waiting for your response, I tried using the MySQL query. (Please see the attached file for the query.) However, it returns "No data." What could be the problem?

Thanks,

Eli

Hi Bogdan,

Yes, I did try.

SELECT ID, post_date, GROUP_CONCAT(order_items separator '\n') AS order_items, post_status, shipping_flight_number, letter_code, terminal, order_total, full_name
FROM (
   SELECT 
      p.ID, 
      date(p.post_date) AS post_date,
      p.post_status AS post_status,
      concat(max( CASE 
                     WHEN o.meta_key = '_qty' and 
                          i.order_item_id = o.order_item_id 
                        THEN o.meta_value 
                  END ), ' ', i.order_item_name) as order_items,
      max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_flight_number,
      substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1) as letter_code,
      ( SELECT terminal_id FROM wp_shipping_airlines WHERE letter_code = substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1)) as terminal,
      max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
      concat(max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ), ' ', max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END )) as full_name
FROM wp_posts AS p
LEFT JOIN wp_postmeta pm on p.ID = pm.post_id
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.post_type = 'shop_order' AND i.order_item_type = 'line_item' AND p.post_status != 'trash'
GROUP BY i.order_item_name, p.ID) AS t
GROUP BY ID, post_date, shipping_flight_number, letter_code, terminal, order_total, full_name

It's working well except when I add the third select:

( SELECT terminal_id FROM wp_shipping_airlines WHERE letter_code = substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1)) as terminal

I'm not sure what's causing the issue.

Thanks,

Eli

Hi Bogdan,

I tried using the wpDataTables constructor, but it's limited and my query is a bit complex.

My query is working when I run it in PHPMyAdmin but it's not when I convert it to View Table. If it will help you better analyze my problem, I can give you access to my database.

I also attached a screenshot so you can see that there's data showing in my query.

Let me know,

-Eli