Blog

Where are WooCommerce Products Stored in the Database?

WooCommerce products are stored as custom post types. Each product will have 1 row in the wp_posts table. and the post type will be “product.”

The product details will be stored in the wp_postmeta table. This table is organized by post_id matching the ID of the post and multiple key/value pairs.

Any meta_keys that begin with an underscore “_” are meant to be hidden from view on product pages.

How to find products in the database

In WP Dashboard go to the Products menu then find the product you want to review. Hover your mouse over the product to find the ID.

Hover over the product to find the product ID.

Access the website database using phpMyAdmin or SQLite and run the following SQL command using the product ID.

Note: your WordPress installation might have a unique table prefix like “wp_wW32_”. Check your table names and update the sql accordingly.

In this example the ID is 264.

This sql statement will retrieve the product’s single row.

select * from wp_posts where ID = 264;

This sql statement will retrieve the product’s meta. Multiple rows will be returned.

select * from wp_postmeta where post_id = 264;

The

Can WooCommerce products be edited directly in the database?

Yes, products can be edited in the database, but some product meta might be tricky to change.

A single value like “_sku” is easy to edit because the meta_value stores the SKU as a string.

Other meta that is more complex might be stored as serialized data. WPEngine describes serialized data as “just a fancy way of saying ‘to list data in a specific order.”

Product attributes is stored in a meta_key of “_product_attributes” as serialized data. If this field is edited directly and one character is incorrect the entire meta might not unserialize correctly and will cause errors on the site.

Final thoughts

Knowing where WooCommerce stores products in the database is good to know when you’re troubleshooting issues, or just to know your store better.

If you have any questions or need help bulk refunding orders in WooCommerce Get in Touch, or ping Grow Development on Twitter!

How to Bulk Refund Orders in WooCommerce Programmatically

While running your WooCommerce store it might become necessary to refund a large number of customer orders.
Refunding an order is possible via the WordPress dashbord, but if the number of orders is more than 100 you might want to do this programmatically using a script and WP CLI.

Here are some examples of why you might need to refund large numbers of orders:

  • An online class is cancelled.
  • Vendor did not deliver stock.
  • Product stock was incorrect and items were oversold.
  • Product price has changed and partial refund will be given to customers.

For this how to you’re going to need shell or ssh access to the server you’ll be working on. In this post I’m going to share parts of a script we use to bulk refund large numbers of WooCommerce orders programmatically.

  1. Get input for the script
  2. WooCommerce Helper Functions
  3. Organizing the WP CLI script
  4. Programmatically refunding a WooCommerce order
  5. Check the script output

Now, let’s get started with the script!

Read More…

WooCommerce: Stop Adding Pixel Tracking Code to Thank You Page

A common task for ecommerce sites is needing to add a tracking pixel to the Order Completed, or Thank You page of WooCommerce. This pixel will help report data to an external service like Facebook Ads, Google Ads, Pinterest, or a CRM.

Adding a snippet of javascript to the WooCommerce template might seem easy, but it can quickly cause issues with your reporting.

In today’s article I’ll share why the practice of adding code to a template is an issue and how to fix it.

  1. What is Thank You page code
  2. Why adding code to the thank you template is a potential issue
  3. Two alternatives to adding code directly to Thank You page template

Let’s go!

Read More…

WooCommerce Subscriptions: How to Bulk Update Next Payment Date

If you run a WooCommerce Subscriptions site for long enough you will experience a situation where you need to change the Next Payment date for subscriptions. Knowing how to bulk update the next payment date in WooCommerce Subscriptions is a vital tool for developers and shop managers.

In this article I’ll explain how to change the Next Payment date on one or thousands of subscriptions at one time in a few steps:

  1. Why would I need to bulk update next payment date on a subscription?
  2. How to update Next Payment date on a WooCommerce subscription
  3. How to bulk update Next Payment Date in many WooCommerce subscriptions

Let’s get started!

Read More…

How To Test WooCommerce Webhooks

Webhooks is a term used for sending data to a URL when an event happens. An example is when someone subscribes to an email marketing list, the service that manages the list can send data like the subscriber’s name and email to another system like a Customer Relationship Manager.

In this article, you’ll learn how to add and update WooCommerce Webhooks with these

  1. Add WooCommerce Webhook
  2. Test WooCommerce Webhook
Read More…