Title: woocommerce sql query
Last modified: August 31, 2016

---

# woocommerce sql query

 *  [cajsoft](https://wordpress.org/support/users/cajsoft/)
 * (@cajsoft)
 * [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/)
 * hi, my client has no longer got access to wordpress on his server so is trying
   to query the database to pull customer and order information..
    we are struggling
   to get the customer id, invoice no. and invoice dates from the tables.. can anyone
   help.. so far we used the following query.
 * [code]
    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 = '_shipping_first_name' and p.ID = pm.post_id THEN
   pm.meta_value END ) as _shipping_first_name, max( CASE WHEN pm.meta_key = '_shipping_last_name'
   and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name, max( CASE
   WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value
   END ) as _shipping_address_1, max( CASE WHEN pm.meta_key = '_shipping_address_2'
   and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2, max( CASE
   WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value 
   END ) as _shipping_city, max( CASE WHEN pm.meta_key = '_shipping_state' and p.
   ID = pm.post_id THEN pm.meta_value END ) as _shipping_state, max( CASE WHEN pm.
   meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END )
   as _shipping_postcode, 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 pm.meta_key
   = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax, max(
   CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value
   END ) as paid_date, ( select group_concat( order_item_name separator '|' ) from
   wp_woocommerce_order_items where order_id = p.ID ) as order_items from wp_posts
   as p, wp_postmeta as pm where post_type = 'shop_order' and p.ID = pm.post_id 
   and p.id = 2810 /* post_date BETWEEN '2015-01-01' AND '2015-07-08'*/ and post_status
   = 'wc-completed' group by p.ID [/code]
 * Hope someone can help!
 * Craig.
 * [https://wordpress.org/plugins/woocommerce/](https://wordpress.org/plugins/woocommerce/)

Viewing 5 replies - 1 through 5 (of 5 total)

 *  Plugin Contributor [Mike Jolley](https://wordpress.org/support/users/mikejolley/)
 * (@mikejolley)
 * [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/#post-7320358)
 * Rather than struggle with all of those joins, can you not use your database dump
   and import it into a new local install? [https://deliciousbrains.com/wp-migrate-db-pro/](https://deliciousbrains.com/wp-migrate-db-pro/)
 * Then you can pull your data via the UI.
 *  Thread Starter [cajsoft](https://wordpress.org/support/users/cajsoft/)
 * (@cajsoft)
 * [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/#post-7320359)
 * the client doesnt want me to take a dump of the data
 *  Plugin Contributor [Mike Jolley](https://wordpress.org/support/users/mikejolley/)
 * (@mikejolley)
 * [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/#post-7320369)
 * You can see it anyway…sounds like they are putting you through some unnecessary
   hardship 🙂
 * Ok well for the bare minimum,
 * customer id is stored in postmeta, as _customer_user
    invoice number I assume
   you mean the post ID, which is in the posts table and the order date would be
   the post_date, again in posts table
 *  Thread Starter [cajsoft](https://wordpress.org/support/users/cajsoft/)
 * (@cajsoft)
 * [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/#post-7320370)
 * no, _invoice_no is stored in meta_value in postmeta table but it is not using
   a key that matches to order which is really confusing.
 *  Plugin Contributor [Mike Jolley](https://wordpress.org/support/users/mikejolley/)
 * (@mikejolley)
 * [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/#post-7320385)
 * If you’re using invoice plugins then that may be the case, but I can only advise
   on WooCommerce core, not 3rd party code.

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘woocommerce sql query’ is closed to new replies.

 * ![](https://ps.w.org/woocommerce/assets/icon.svg?rev=3234504)
 * [WooCommerce](https://wordpress.org/plugins/woocommerce/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/woocommerce/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/woocommerce/)
 * [Active Topics](https://wordpress.org/support/plugin/woocommerce/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/woocommerce/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/woocommerce/reviews/)

 * 5 replies
 * 2 participants
 * Last reply from: [Mike Jolley](https://wordpress.org/support/users/mikejolley/)
 * Last activity: [10 years, 1 month ago](https://wordpress.org/support/topic/woocommerce-sql-query/#post-7320385)
 * Status: not resolved