where is a woocommerce order placed in wordpress database

Question

Where do order and everything in it is stored in the database after it is placed from a WordPress woo-commerce website? To be more precise, how can I get my shipping address without using woocommerce classes like WC_Order class? I mean I need to get that data manually through my custom database queries, but I can't find the order and everything associated with it in my database? I know order is stored in the database as post in the wp-posts table but where is the rest of it i.e shipping address billing address etc etc?

Transfusion

Check this link wordpress.org/support/topic/… – Piston

Also better idea is to install the plugin locally and dig in to WP database. – Celestinecelestite

from @Amiya's link"WooCommerce 2.x, orders are stored in the post table, postmeta table, woocommerce_order_items, and woocommerce_order_itemmeta tables. Various parts of an order are stored in different tables" – Shift

Answer 1

Orders are a Custom Post Type (CPT), so they are stored in the wp_posts table. If you search the post_type field for 'shop_order', SQL will retrieve all orders.

Then, you must search the wp_postmeta table for all the records with post_id matching the id of the order post. Among the fields you will then find in the wp_postmeta table will be the entire shipping and billing addresses.

France

Answer 2

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 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
    post_date BETWEEN '2015-01-01' AND '2015-07-08' and
    post_status = 'wc-completed' and
    oi.order_item_name = 'Product Name'
group by
    p.ID

Source here.

Angelicangelica

Answer 3

Also the order data will be stored in the woocommerce_order_items and woocommerce_order_itemmeta tables (for WooCommerce > 2.5 I believe) These tables contain things pertaining to the actual product the customer bought.

The shop_order post entries have the post_id which matches order_id in woocommerce_order_items. The order_item_id in woocommerce_order_items matches the order_item_id in woocommerce.order_itemmeta.

Operate

Answer 4

I can confirm that woocommerce orders are a custom post type so they are stored in wp_posts.

WooCommerce orders are "custom post" they are stored in "wp_posts" under "post_type" -> ""shop_order"

if you want to select shop orders with sql query you can do something like below.

global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE post_type = 'shop_order'", ARRAY_A );

Jonajonah

Answer 5

This query should be able to help you. You just need to change the DB prefix for your own DB:

SELECT * 
FROM 
  adolfoma_comoconq_wp470.wpkn_postmeta 
  INNER JOIN 
  adolfoma_comoconq_wp470.wpkn_posts 
  ON adolfoma_comoconq_wp470.wpkn_posts.ID=adolfoma_comoconq_wp470.wpkn_postmeta.post_id 
where adolfoma_comoconq_wp470.wpkn_posts.post_type ="shop_order";

I just wrote it and I can see email, billing address, name, purchase amount, etc. I didn't see the exact items and the amount, maybe additional querying is necessary.

Lura

Answer 6

Asslam-O-Alikum! , Hi In My Case I updated the code to see exact product names related to orders.

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_phone' AND p.ID = pm.post_id THEN pm.meta_value END) AS billing_phone,
    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 = '_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 = '_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,
    MAX(CASE WHEN p.ID = wptr.object_id THEN wptr.term_taxonomy_id END) AS status,
    GROUP_CONCAT(DISTINCT CASE WHEN oi.order_item_name != 'Free Shipping' THEN oi.order_item_name ELSE NULL END SEPARATOR ', ') AS product_names
FROM
    wp_posts AS p
    LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
    LEFT JOIN wp_term_relationships AS wptr ON p.ID = wptr.object_id
    LEFT JOIN wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE
    p.post_type = 'shop_order' AND
    p.post_date BETWEEN '2024-04-10' AND '2024-04-18'
GROUP BY
    p.ID

If you only want to see only order id and products names realated to it then you can use this code as mentioned by this blog

/* get everything from wp_woocommerce_order_items table */
SELECT * FROM `wp_woocommerce_order_items`;

If you want to see order quantity as well with products

    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_phone' AND p.ID = pm.post_id THEN pm.meta_value END) AS billing_phone,
    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 = '_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 = '_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,
    MAX(CASE WHEN p.ID = wptr.object_id THEN wptr.term_taxonomy_id END) AS status,
    GROUP_CONCAT(DISTINCT CASE WHEN oi_meta.meta_key = '_qty' AND oi_meta.order_item_id = oi.order_item_id AND oi.order_item_name != 'Free Shipping' THEN CONCAT(oi.order_item_name, ' x', oi_meta.meta_value) ELSE NULL END SEPARATOR ', ') AS product_quantities
FROM
    wp_posts AS p
    LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
    LEFT JOIN wp_term_relationships AS wptr ON p.ID = wptr.object_id
    LEFT JOIN wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
    LEFT JOIN wp_woocommerce_order_itemmeta AS oi_meta ON oi.order_item_id = oi_meta.order_item_id
WHERE
    p.post_type = 'shop_order' AND
    p.post_date BETWEEN '2024-04-10' AND '2024-04-18'
GROUP BY
    p.ID

Hope this going to help anyone in future!

Grandaunt