SQL Archives - Freelance Woocommerce Developer in Bromley, London

This post is just a place to store handy SQL queries for Woocommerce.

Get Order and all its information

select
    p.ID as order_id,
    p.post_date,
    pm.*
from
    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
    p.ID = 14223

select

p.ID as order_id,

p.post_date,

pm.*

from

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

p.ID = 14223

Get Users and billing and shipping addresses in Woocommerce

SELECT u.id, u.user_login, u.user_email, 

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END   ) as billing_email,

    max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,
    max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,
    max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,
    max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,
    max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,
    max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,
    max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,
    max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,
    max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,
    max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,
    max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,
    max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,
    max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,
    max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u
LEFT JOIN wp_usermeta m
ON  u.ID = m.user_id
group by u.ID

SELECT u.id, u.user_login, u.user_email,

max( CASE WHEN m.meta_key = 'billing_email' and u.ID = m.user_id THEN m.meta_value END ) as billing_email,

max( CASE WHEN m.meta_key = 'billing_first_name' and u.id = m.user_id THEN m.meta_value END ) as billing_first_name,

max( CASE WHEN m.meta_key = 'billing_last_name' and u.id = m.user_id THEN m.meta_value END ) as billing_last_name,

max( CASE WHEN m.meta_key = 'billing_address_1' and u.id = m.user_id THEN m.meta_value END ) as billing_address_1,

max( CASE WHEN m.meta_key = 'billing_address_2' and u.id = m.user_id THEN m.meta_value END ) as billing_address_2,

max( CASE WHEN m.meta_key = 'billing_city' and u.id = m.user_id THEN m.meta_value END ) as billing_city,

max( CASE WHEN m.meta_key = 'billing_state' and u.id = m.user_id THEN m.meta_value END ) as billing_state,

max( CASE WHEN m.meta_key = 'billing_postcode' and u.id = m.user_id THEN m.meta_value END ) as billing_postcode,

max( CASE WHEN m.meta_key = 'shipping_first_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_first_name,

max( CASE WHEN m.meta_key = 'shipping_last_name' and u.id = m.user_id THEN m.meta_value END ) as shipping_last_name,

max( CASE WHEN m.meta_key = 'shipping_address_1' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_1,

max( CASE WHEN m.meta_key = 'shipping_address_2' and u.id = m.user_id THEN m.meta_value END ) as shipping_address_2,

max( CASE WHEN m.meta_key = 'shipping_city' and u.id = m.user_id THEN m.meta_value END ) as shipping_city,

max( CASE WHEN m.meta_key = 'shipping_state' and u.id = m.user_id THEN m.meta_value END ) as _shipping_state,

max( CASE WHEN m.meta_key = 'shipping_postcode' and u.id = m.user_id THEN m.meta_value END ) as _shipping_postcode

FROM wp_users u

LEFT JOIN wp_usermeta m

ON u.ID = m.user_id

group by u.ID

this bit of sql is based on this post at http://codecharismatic.com/

Get Order Objects (not strictly sql, but seems a good place to drop it)

    global $woocommerce;						
    $args = array(  'post_type'=> 'shop_order', 
                    'post_status' => 'publish', 
                    'posts_per_page' => -1, 
                    'tax_query' => array(  
                                            array(  'taxonomy' => 'shop_order_status',
                                                    'field' => 'slug',
                                                    'terms' => array('processing')
                                                )
                                        )
                  );	
    
    $orders = array();
    

    $loop = new WP_Query( $args );						
    while ( $loop->have_posts() ) : $loop->the_post(); 

        	$order_id = $loop->post->ID;								
                $order = new WC_Order($order_id);
                
                
                echo "id order = ".$order_id;
                
                $orders[] = $order;
    
    
    endwhile;
    
    
    
    return $orders;

global $woocommerce;

$args = array( 'post_type'=> 'shop_order',

'post_status' => 'publish',

'posts_per_page' => -1,

'tax_query' => array(

array( 'taxonomy' => 'shop_order_status',

'field' => 'slug',

'terms' => array('processing')

)

);

$orders = array();

$loop = new WP_Query( $args );

while ( $loop->have_posts() ) : $loop->the_post();

$order_id = $loop->post->ID;

$order = new WC_Order($order_id);

echo "id order = ".$order_id;

$orders[] = $order;

endwhile;

return $orders;

Importing a MAMP mysql database on mac (OS X Yosemite)

Handy Woocommerce SQL queries

Get Order and all its information

Get Users and billing and shipping addresses in Woocommerce

Get Order Objects (not strictly sql, but seems a good place to drop it)