I do a lot of integration work between e-commerce cart software packages and third party systems. Many e-commerce carts (Sunshop, VirtueMart, ZenCart, etc.) maintain their own database tables for storing orders, product, categories and everything the cart needs for the store to operate. Accessing data from these tables is straightforward; the data is normalized for a cart application, with no meta representation of data. Translation: In a typical cart database, you’ll find an order table, a product table, a customer table, a product category table, etc. It’s not difficult to extract details about what takes place in the cart.
WooCommerce has become a very popular e-commerce cart, riding on the coattails of WordPress’ great success. I’ve implemented WooCommerce a number of times and I think it’s an excellent, light-duty cart. As a WordPress plugin, WooCommerce benefits from other, 3rd party plugins for expanding the cart’s functionality. But what happens when you have to deal with a one-of-a-kind integration between Woo Commerce and a system for which there is no plugin solution? We quickly find ourselves digging into the WordPress database architecture, since WooCommerce uses a blend of it’s own tables and WordPress post and taxonomy tables to store data.
Below are some basic MySQL queries for getting WooCommerce products, categories, orders, and order items from the database. All of the queries assume the default “wp_” prefix to the database tables, so you may need to modify those prefixes if your tables are named differently. I’ve also left some example product and order numbers in place to complete the syntax of the queries. I’ve highlighted those values in the examples. Change to suit your particular use.
These are raw MySQL queries. I use them in SQLyog, an excellent MySQL admin tool. If you’re writing a PHP script for your own application needs, you can adapt these queries into your code. Lots of example of how to do that at php.net.
Query #1: Return a list of product categories
SELECT wp_terms.* FROM wp_terms LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'product_cat'
Query #2: Get the category for a specific product
SELECT wp_term_relationships.*,wp_terms.* FROM wp_term_relationships LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id LEFT JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id WHERE post_type = 'product' AND taxonomy = 'product_cat' AND object_id = 167
In above example, we’re getting the category for product 167; substitute your desired product ID, which you can find in the WooCommerce Products admin menu area.
Query #3: Return a list of product attributes for a specific product
SELECT wp_posts.ID,wp_posts.post_title,wp_postmeta.* FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE post_type = 'product' AND wp_posts.ID = 167
In above example, we’re getting the attributes for product 167; substitute your desired product ID, which you can find in the WooCommerce Products admin menu area.
Query #4: Return a list of line item details for a specific order
SELECT wp_woocommerce_order_itemmeta.*,wp_woocommerce_order_items.* FROM wp_woocommerce_order_items JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id WHERE order_id = 7373 ORDER BY wp_woocommerce_order_itemmeta.meta_key
In above example, we’re getting the line item details for order 7373; substitute your desired order ID, which you can find in the WooCommerce Orders admin menu area.
Hi;
thanks for posting these nice queries.
How can I get the total sales for every month ?
thanks
Jeff-
Thanks for the question. Order total is stored in the wp_postmeta table, which makes for an easy, one table query:
SELECT post_id AS order_number, meta_value AS order_total FROM wp_postmeta WHERE meta_key = '_order_total' ORDER BY post_idThat will give you the order total for all orders.
If you want a single order, it would be:
SELECT post_id AS order_number, meta_value AS order_total FROM wp_postmeta WHERE meta_key = '_order_total' AND post_id = 1770In the above example, I would get the order total for order number 1770.
Sometime in the not too distant future, I’ll try to do a follow up article with some more comprehensive queries that show order details like the customer’s info, along with the order total.
Rich
How to get woo comers cart total by MySQL quarry.it must be similar to the result of the function.WC()->cart->get_total()
Kasun, not sure if you are asking a question, but yes, the function returns the same value.
However, there are situations where someone isn’t working in a WordPress/Woo/PHP environment and needs to get the same data.
Connecting directly to MySQL from another environment allows access to the data without the need for operating within the WooCommerce framework.
HI
Thanks for your code snippets, I need a query to view all the pricing of all my products on the website. so list every product and the price
I found this but keep on getting a zero result.
https://wordpress.org/support/topic/where-is-the-mysql-table-that-stores-prices-of-products
Hennie, pricing fields are in the wp_postmeta table. This is the basic query, which involves doing a join for each product field we need from wp_postameta:
SELECT
pm1.meta_value AS sku,
pm2.meta_value AS regular_price,
pm3.meta_value AS sale_price,
pm4.meta_value AS price
FROM
wp_posts p
LEFT JOIN wp_postmeta pm1 ON (pm1.post_id = p.ID AND pm1.meta_key = “_sku”)
LEFT JOIN wp_postmeta pm2 ON (pm2.post_id = p.ID AND pm2.meta_key = “_regular_price”)
LEFT JOIN wp_postmeta pm3 ON (pm3.post_id = p.ID AND pm3.meta_key = “_sale_price”)
LEFT JOIN wp_postmeta pm4 ON (pm4.post_id = p.ID AND pm4.meta_key = “_price”)
WHERE post_type = ‘product’
i am getting error on this {SELECT
pm1.meta_value AS sku,
pm2.meta_value AS regular_price,
pm3.meta_value AS sale_price,
pm4.meta_value AS price
FROM
wp_posts p
LEFT JOIN wp_postmeta pm1 ON (pm1.post_id = p.ID AND pm1.meta_key = “_sku”)
LEFT JOIN wp_postmeta pm2 ON (pm2.post_id = p.ID AND pm2.meta_key = “_regular_price”)
LEFT JOIN wp_postmeta pm3 ON (pm3.post_id = p.ID AND pm3.meta_key = “_sale_price”)
LEFT JOIN wp_postmeta pm4 ON (pm4.post_id = p.ID AND pm4.meta_key = “_price”)
WHERE post_type = ‘product’}
asir, your query is structurally correct. You just have a syntax error. Use the single quote instead of the double quotes you have.
This is the corrected version:
SELECT
pm1.meta_value AS sku,
pm2.meta_value AS regular_price,
pm3.meta_value AS sale_price,
pm4.meta_value AS price
FROM wp_posts p
LEFT JOIN wp_postmeta pm1 ON (pm1.post_id = p.ID AND pm1.meta_key = ‘_sku’)
LEFT JOIN wp_postmeta pm2 ON (pm2.post_id = p.ID AND pm2.meta_key = ‘_regular_price’)
LEFT JOIN wp_postmeta pm3 ON (pm3.post_id = p.ID AND pm3.meta_key = ‘_sale_price’)
LEFT JOIN wp_postmeta pm4 ON (pm4.post_id = p.ID AND pm4.meta_key = ‘_price’) WHERE post_type = ‘product’
How can get list of products by specific category ID?
Tomas-
A variation of Query #2. Instead of testing the records by object_id, we use wp_term_taxonomy.term_id in the where clause, and check that it’s equal to the category number we’re interested in.
That should return the list of products in the category. See below. I use what would be category ID 18 from my test cart.
SELECT wp_posts.* FROM wp_term_relationships
LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
LEFT JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
WHERE post_type = ‘product’ AND taxonomy = ‘product_cat’
AND wp_term_taxonomy.term_taxonomy_id = 18
Great info! My question is a little different, I have Gravity Forms and Woo Commerce installed on a new site. Part of the process when a customer wants to buy a subscription is to enter a special (unique) serial number. I need to compare that field to a database (somewhere) of “valid” numbers we sold them previously. If it’s not our valid serial number, YOU SHALL NOT PASS! LOL
Is that possible? I would need a way to upload to a DB, and then have the “Add to Cart” process check the DB before allowing the product thru checkout.
Bryan, need a clarification: Is the customer entering the unique number? Like they’ve received a unique, “one use” promotional code, or something along those lines? And is Gravity forms being used for that data entry, before the Woocommerce “add to cart”? Would need to understand the context better to recommend an approach.
Great question. And this evolved since I posted the comment. Decided to NOT use gravity forms at all, it makes the site bloated and I believe this can be done in Woo Commerce with a few tweaks.
To answer your question, yes, when the customers buys our product, we ship it to them in 2-3 days. They come back to the site and purchase a monthly plan (woo subscriptions plugin). When they are viewing the monthly plan I want to add a required field, 18 digits serial number printed not he product we shipped them.
When we ship the product out the door (pick – pack – ship) my team could add the serial number in the database. When the customer goes to activate, they will punch in that serial number, thats when we would validate the custom field. If they don’t have one, they shall not pass to cart.
Otherwise they are buying a subscription before having the physical product = credit card charge backs! No bueno.
Possible? And I would need to use SQL query to run reports later on which serial numbers are in use “active status”.
Yep, absolutely doable and you don’t need Gravity.
The implementation details are more than I can provide here, but the basics are:
– Add a custom field to the product(s) in Woocommerce. That can be done using the built-in Woocommerce customization functions (see the Woocommerce documentation at https://docs.woothemes.com/wc-apidocs/).
– I’m not sure if there’s a built in Woo API function for forcing the field to be required before adding to cart, but it would be easy enough to add a hook for that in your theme’s functions.php file.
– When the order is placed, the field’s value would be stored in the wp_woocommerce_order_itemmeta table, with the meta key being the custom field name you chose. The reporting query would involved joining the wp_woocommerce_order_itemmeta table with the wp_woocommerce_order_items table, and possibly the wp_posts table, depending what you want on the report.
Great info! I realize you can’t write a full spec here, but most of this makes sense.
How about the validation part when the user punches the serial number in the custom field? What I’m confused on is how can we (the store admin) add the serial numbers upon shipment, and, how (from coding perspective) do we validate that field against the previously entered serial number?
That part is important.
You can either add a custom field to orders, or use the order notes field to store the serial number associated with the order, via the admin interface.
The validation function that checks for the serial custom field when the user adds to cart can do a database query to find the serial number in the wp_postmeta table. You could even go so far as requiring the user to log back in so you know their order history, before they add the item, but that might not be ideal from an UX perspective. Depends on the nature of your business and the customer’s expectations.
If the validation fails, you don’t add the product to the cart, and you have the routine post an error message, similar to the default Woo error messages for trying to add something to the cart that’s higher than available inventory, or other issues relative to cart adds.
Ok that makes sense. The user creates the account when they initially order the physical product, then we would go into that same order and update with the serial number we are shipping to them during the pick pack and ship.
When they return to the site they are taken to the shopping cart to choose 1 of 4 subscriptions (it’s a mobile phone service). Thats when we would require serial number field, pass it to the cart, and checkout. They are prompted to login on the checkout page if it recognizes the user email info.
What about the duplication? In other words, if we add the serial number manually when shipping the order, and, they validate it during checkout, do we not post what they entered a 2nd time? I assume not, but just wondering as it would normally post to the order meta.
The validation query would be written to distinguish between the serial number with the product purchase (order meta), and one with the subscription (order item meta).
So it’s stored in two places essentially?
Assuming you implement as I’ve described, yes, as follows:
1) The order instance that was entered by your fulfillment crew in the admin interface when the order ships; stored in wp_postmeta (data field associated with an order).
2) The subscription product order item instance. Entered by the return customer when buying the subscription; stored in wp_order_itemmeta (data field associated with an item in an order).
1) do you have a tip jar? this is great info!
2) how would I change a SKU (the customer upgraded to a more expensive product) if we had $order_id 12345 (and the SKU $sku = ‘DS-B-1P-01’ will only be in the order once) change it to ‘DS-B-1P-02’
Hi Eric. No tip jar, but thanks for asking. 🙂
Unfortunately, in the situation you describe, it’s more complicated than just changing the SKU. In addition to storing the reference id of the ordered product in the wp_woocommerce_order_item table, Woocommerce stores additional details about the item, in the context of that particular order, in numerous records in the wp_woocommerce_order_itemmeta table. The other item details include things like line item price, line item tax, line item total, additional shipping charges, among other things. You’d also have to update all of the order subtotal, tax, and shipping totals fields, if they were impacted. Those are in the wp_postmeta table. It would be messy.
It’s cleaner, in my opinion, to either edit the order (if not yet paid), or create a new order with the right item, make notes about the circumstance in the order notes field, mark the order as paid (assuming the customer already paid), and delete the original order.
yes was just looking at the meta in the wp_woocommerce_order_itemmeta – looks like I’d need to change everything you mentioned.
It’s a one-click upsell, and we’ve already charged them for the original order, and now we have returned from stripe with a successful second charge…. going to have to play around with it to see if it makes sense to edit all of that meta or to create a second order. We are using Taxjar’s api to calc the tax so that we know what to charge, and fortunately the weight does not change, so shipping at least we can leave alone.