AnswerBun.com

How can I combine one field using wpdb and group by?

WordPress Development Asked by user81828 on August 10, 2020

I have the following code in my functions.php where I’m trying to group the quantity sold from woocommerce orders into the variation selected on the product. My query is:

$uvuorderposts = $wpdb->get_results( 
    "
    SELECT p.ID, p.post_title, p.post_date, wcom.order_item_name, wcomm.meta_key, wcomm.meta_value, wcomm2.meta_key AS meta_key_qty, wcomm2.meta_value AS meta_value_qty, pm2.meta_value AS meta_value_location
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->woocommerce_order_items AS wcom ON p.ID = wcom.order_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm ON wcom.order_item_id = wcomm.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm2 ON wcom.order_item_id = wcomm2.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm3 ON wcom.order_item_id = wcomm3.order_item_id
    INNER JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = wcomm3.meta_value
    WHERE (p.post_type = 'shop_order')
    AND (pm.meta_key = '_wc_authorize_net_aim_charge_captured')
    AND (pm.meta_value = 'yes')
    AND (wcomm.meta_key = '_product_id')
    AND (wcomm.meta_value = '4034')
    AND (wcomm2.meta_key = '_qty')
    AND (wcomm3.meta_key = '_variation_id')
    AND (pm2.meta_key = 'attribute_pa_seminar-location')
    ORDER BY p.post_date
    "
);

echo "<table>";
foreach ( $uvuorderposts as $uvuorderpost ) 
{
    echo "<tr bgcolor='#eaeaea'><td colspan='2'>" . $uvuorderpost->meta_key_qty . " : " .  $uvuorderpost->meta_value_qty . "</td></tr>";
    echo "<tr bgcolor='#eaeaea'><td colspan='2'>" . strtoupper($uvuorderpost->meta_value_location) . "</td></tr>";      
}
echo "</table>";

The output gives me:

_qty : 1
WY
_qty : 1
WV
_qty : 1
GA
_qty : 1
GA

I need it to actually be:

_qty : 1
WY
_qty : 1
WV
_qty : 2
GA

I added GROUP BY pm2.meta_value and it took away the second GA one, but did not combine the _qty value. Please help with how to combine the _qty value between returned records?

One Answer

Got it working with

$uvusql = $wpdb->prepare( 
    "
    SELECT p.ID, p.post_title, p.post_date, wcom.order_item_name, wcomm.meta_key, wcomm.meta_value, wcomm2.meta_key AS meta_key_qty, wcomm2.meta_value AS meta_value_qty, pm2.meta_value AS meta_value_location
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->woocommerce_order_items AS wcom ON p.ID = wcom.order_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm ON wcom.order_item_id = wcomm.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm2 ON wcom.order_item_id = wcomm2.order_item_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm3 ON wcom.order_item_id = wcomm3.order_item_id
    INNER JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = wcomm3.meta_value
    WHERE (p.post_type = 'shop_order')
    AND (pm.meta_key = '_wc_authorize_net_aim_charge_captured')
    AND (pm.meta_value = 'yes')
    AND (wcomm.meta_key = '_product_id')
    AND (wcomm.meta_value = '4034')
    AND (wcomm2.meta_key = '_qty')
    AND (wcomm3.meta_key = '_variation_id')
    AND (pm2.meta_key = 'attribute_pa_seminar-location')
    ORDER BY p.post_date
    "
);

$uvuorderposts = $wpdb->get_results( $uvusql, ARRAY_A );

 $uvugroups = array();
    $uvukey = "";
    $uvukeyc = 0;
    foreach ($uvuorderposts as $item) {
        $uvukey = $item['meta_value_location'];
        if (!array_key_exists($uvukey, $uvugroups)) {
            $uvugroups[$uvukey] = array(
                'id' => $item['meta_value_location'],
                'qty' => $item['meta_value_qty'],
            );
        } else {
            $uvugroups[$uvukey]['qty'] = $uvugroups[$uvukey]['qty'] + $item['meta_value_qty'];
        }
        $uvukeyc++;                       
    }

    print "<pre>";
    print_r($uvugroups);
    print "</pre>";

Answered by user81836 on August 10, 2020

Add your own answers!

Related Questions

How can I noindex my archive pages?

1  Asked on November 26, 2021 by summer-pratt

     

admin-ajax error 400

0  Asked on November 26, 2021 by daniel-nebert

   

Bulk update custom post types

1  Asked on November 26, 2021 by samm-foolt

     

Upload avatar for post comment

1  Asked on November 26, 2021 by divyang

     

Non-blocking file_put_contents in function.php

2  Asked on November 26, 2021 by mustafa

   

Image problem – Multisite image upload broken

1  Asked on November 23, 2021 by prakhar-monga

   

Where is the htaccess in wordpress.com hosting?

1  Asked on November 23, 2021 by user2007920

   

How do you approach block structured content?

1  Asked on November 23, 2021 by kevin-all

     

Google Fonts not loading correctly

1  Asked on November 23, 2021 by octavialo

   

wp_redirect() function is not working

8  Asked on November 21, 2021 by sans780730

   

How to search only by post title and category?

1  Asked on November 21, 2021 by carl-jue-nier

     

Custom form validation

1  Asked on November 21, 2021 by alexander-holsgrove

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP