Support » Plugin: EZ SQL Reports Shortcode Widget and DB Backup » No display although rows returned by query

  • Resolved mikerayjones

    (@mikerayjones)


    Hi Eli

    This SQL runs fine in phpMyAdmin and returns 4 rows of data in my test environment:

    select
        p.ID as order_id,
        p.post_date,
        max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
        max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
        max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
        max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
        max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
        max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
        max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
        max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
        max( CASE WHEN pm.meta_key = 'additional_signup' and p.ID = pm.post_id THEN pm.meta_value END ) as signup,
        max( CASE WHEN pm.meta_key = 'additional_photo' and p.ID = pm.post_id THEN pm.meta_value END ) as photo,
        max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
        max( CASE WHEN om.meta_key = '_qty' and oi.order_item_id = om.order_item_id THEN om.meta_value END ) as Qty,
        oi.order_item_name,
        max( CASE WHEN om.meta_key = '_line_total' and oi.order_item_id = om.order_item_id THEN om.meta_value END ) as lineTotal
    from
        wp_woocommerce_order_itemmeta as om,
        wp_posts p 
        join wp_postmeta pm on p.ID = pm.post_id
        join wp_woocommerce_order_items oi on p.ID = oi.order_id
    where
        post_type = 'shop_order' and
        order_item_type = 'line_item' and
        oi.order_item_id = om.order_item_id and
        post_date BETWEEN '2016-10-01' AND '2017-06-01' and
        post_status = 'wc-completed'
    group by
        p.ID
    

    … however in your plugin all I see is this:

    Query returned 4 rows in 0.0003 seconds.
    
        Query affected 4 rows!
    
    Array ( [0] =>
    
        [1] => 
        [2] => 
    
    ) 

    Can you see what I’m doing wrong?

    Thanks, Mike.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Eli

    (@scheeeli)

    This could be formatting issue. My plugin requires the first 7 characters to be: “select ” if you want the results to be displayed.

    Try starting the query like this (without the quotes):
    “select p.ID as order_id, …”
    NOTE: with a [space] (not a [CrLf]) after the word select, and no [space] characters before it.

    Let me know if that makes a difference, if not maybe a screenshot would help…

    Hi Eli

    Yes, that did it! I’m so used to ignoring whitespace that I would never have thought of that.

    Thanks.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘No display although rows returned by query’ is closed to new replies.