wordpress 数据库中放置的 woocommerce 订单在哪里

where is a woocommerce order placed in wordpress database

提问by MAK

I have a very simple question. but its bothering me a lot.

我有一个非常简单的问题。但它困扰着我很多。

Can any one please tell me where does an order and every thing in it is stored in the database after it is placed from a WordPress woo-commerce website. To be more precise can anyone tell me 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 cant 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? I hope I am not confusing anyone.

任何人都可以告诉我从WordPress woo-commerce网站放置订单后，订单和其中的所有内容都存储在数据库中的位置。更准确地说，谁能告诉我如何在不使用 WC_Order 类等 woocommerce 类的情况下获取我的送货地址？我的意思是我需要通过我的自定义数据库查询手动获取这些数据，但我无法在我的数据库中找到订单以及与之相关的所有内容？我知道订单作为 wp-posts 表中的帖子存储在数据库中，但其余部分在哪里，即送货地址账单地址等？我希望我不会混淆任何人。

Best Regards,

此致，

MAK

麦

回答by Patrick McCormick

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.

订单是自定义帖子类型 (CPT)，因此它们存储在 wp_posts 表中。如果您在 post_type 字段中搜索“shop_order”，SQL 将检索所有订单。

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.

然后，您必须在 wp_postmeta 表中搜索 post_id 与订单帖子的 id 匹配的所有记录。然后，您将在 wp_postmeta 表中找到的字段中将包含完整的送货地址和账单地址。

回答by fuddin

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.

来源在这里。

回答by gunnerman

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.

此外，订单数据将存储在 woocommerce_order_items 和 woocommerce_order_itemmeta 表中（对于 WooCommerce > 2.5 我相信）这些表包含与客户购买的实际产品有关的内容。

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.

shop_order 帖子条目的 post_id 与 woocommerce_order_items 中的 order_id 匹配。woocommerce_order_items 中的 order_item_id 与 woocommerce.order_itemmeta 中的 order_item_id 匹配。

回答by Cristian Melendez

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.

我刚写的，可以看到邮箱、账单地址、姓名、购买金额等。我没有看到确切的项目和金额，可能需要额外查询。

回答by Nitesh Gour

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

我可以确认 woocommerce 订单是自定义帖子类型，因此它们存储在 wp_posts 中。

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

WooCommerce 订单是“自定义帖子”，它们存储在“post_type”->“”shop_order”下的“wp_posts”中

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

如果您想使用 sql 查询选择商店订单，您可以执行以下操作。

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