This article is aimed at developers and provides information about how the options in WooCommerce Product Options are stored in the WordPress MySQL database.

Please note that the plugin has a user-friendly import/export tool for moving product options between sites, so you will only need the information in this article if you need to retrieve product options for other purposes.

The plugin stores two types of information in the database:

• The configuration of the Options and Option Groups created at Products → Product Options.
• The data stored for each order item when customers purchase products with extra options.

Options and option groups

Your options and groups are stored in two custom tables which the plugin creates when you first install it: wp_wpo_groups and wp_wpo_options.

wp_wpo_groups table

The wp_wpo_groups table contains the settings for the option groups and their options. Its content is organized in the following columns:

• id - the ID of the group (a primary key, unique in the table).
• name - the name of the group.
• display_name - whether the name of the group gets displayed on the front end (either 0 or 1)
• visibility - the type of visibility for the group (either global or specific).
• menu_order - the position of the group (groups can be rearranged by dragging them in the list of the groups) relative to the whole collection of groups.
• products - a JSON-formatted array of product IDs on which the option group will be displayed (empty if visibility is set to specific).
• exclude_products - a JSON-formatted array of product IDs that will be excluded from showing the option group.
• categories - a JSON-formatted array of category IDs on which the option group will be displayed (empty if visibility is set to specific).
• exclude_categories - a JSON-formatted array of category IDs that will be excluded from showing the option group.

wp_wpo_options table

The wp_wpo_options table contains the settings of each option collected by the groups. The content of the table is organized in the following columns:

• id - the ID of the option (a primary key, unique in the table).
• group_id - the ID of the group to which the option belongs
• menu_order - the position of the option in the group (options can be rearranged by dragging them in the list of the options).
• name - the name of the option.
• description - an optional description displayed on the front end under the option.
• display_name - whether the name of the option gets displayed on the front end.
• required - whether the option is required or not (either 0 or 1).
• type - the type of option (possible values: checkbox, radio, dropdown, text, textarea, number, file_upload, images, color_swatches, text_labels, customer_price, date_picker, price_formula and product).
• choices - a JSON-formatted array of objects defining the choices configured for the option (null if the option type doesn’t support choices).
• settings - a JSON-formatted object with all the settings for the option, including the optional advanced settings.
• conditional_logic - a JSON-formatted object with the conditional logic configuration (null if no conditional logic is configured).

JSON-formatted fields

WordPress traditionally relies on the serialization of arrays, which makes it impossible to run complex queries directly in the database. Since modern database engines allow efficiently performing queries that involve properties of JSON fields, WooCommerce Product Options stores all its data structures in JSON format.

If we exclude the JSON-formatted arrays stored in the wp_wpo_groups table – as they are all non-associative arrays of integers representing the IDs of products or categories - then the three fields that require explaining how their structure is defined are all in the wp_wpo_options table: choices, settings, and conditional_logic.

Options attached to individual orders

When customers place an order containing products with options, all the data collected for each product gets stored in the wp_woocommerce_order_itemmeta table. This is in line with how WooCommerce itself works.

For example, when a product variation is ordered, the item meta of the purchased item includes not only the variation ID but also each separate attribute connected to that variation. This allows the quick retrieval of the product attribute information without needing additional database queries. The following image shows how this data is stored in the table:

WooCommerce Product Options stores all the values of its options in the wp_woocommerce_order_itemmeta table in the same way:

The meta keys which WooCommerce Product Options uses to store the data are the names assigned to each option, exactly as they are written in the name field at Products → Product Options (including any spaces):

Accessing those meta values is the simplest way to retrieve the values which customers selected for each option.

The _wpo_options metadata

In addition to the separate attributes for each option being selected when purchasing a product, WooCommerce Product Options also stores an associative array of all the values in a single entry of the order item metadata table, which WPO identifies with the _wpo_options meta key.

The array stored with the _wpo_options meta key has the following structure:

array(
    $option_id => array (
        'name' => $option_name,
        'type' => $option_type,
        'option_id' => $option_id,
        'group_id' => $group_id,
        'value' => $selected_value,
        'choice_data' => array (
            // This array has two possible structures:
            // 1. all selected choices for multiple-choice options
            // 2. The input value for single-choice options
            // In both cases, the values are stored with the ‘label’ key
        ),
    ),
)

For example, a number option where customers input the value ‘100’ would be stored as follows:

array(
    1234 => array (
        'name' => 'Width',
        'type' => 'number',
        'option_id' => 1234,
        'group_id' => 1001,
        'value' => '100',
        'choice_data' => array(
            array(
                'label' => '100',
            ),
        ),
    ),
);

On the other hand, a checkbox option with four possible choices A, B, C and D where customers selected A and C would be stored as follows:

array(
    1234 => array (
        'name' => 'Sides',
        'type' => 'checkbox',
        'option_id' => 1234,
        'group_id' => 1001,
        'value' => 'A | C',
        'choice_data' => array(
            array(
                'label' => 'A',
            ),
            array(
                'label' => 'C',
            ),
        ),
    ),
)

If both options are present in an order item, the array would be:

array(
    1234 => array (
        'name' => 'Width',
        'type' => 'number',
        'option_id' => 1234,
        'group_id' => 1001,
        'value' => '100',
        'choice_data' => array(
            array(
                'label' => '100',
            ),
        ),
    ),
    1235 => array (
        'name' => 'Sides',
        'type' => 'checkbox',
        'option_id' => 1235,
        'group_id' => 1001,
        'value' => 'A | C',
        'choice_data' => array(
            array(
                'label' => 'A',
            ),
            array(
                'label' => 'C',
            ),
        ),
    ),
);

It is worth noting that all the options in the _wpo_options meta value may be coming from different group. That is why each option in the array also has a group_id that helps identify the group to which the option belongs.

Recommended import/export plugins

Once you understand how product options are stored in the WordPress database, there are plenty of import and export plugins to choose from. We recommend the following:

• WP All Import (which also includes export tools) - use this for importing and exporting your product options and their settings.
• WooCommerce Customer / Order / Coupon Export - use this for exporting data about which options customers have selected when they placed an order.

Exporting product options data from orders programmatically

If you prefer to create a custom script that gets the order item programmatically, you can customize to your needs the following code snippet, which refers to the example shown in the previous screenshots:

$order = wc_get_order( 9876 ); // 9876 is the ID of the order
$items = $order->get_items();
$meta = [];

foreach ( $order->get_items() as $item_id => $item ) {
    $width = wc_get_order_item_meta( $item_id, 'Width', true );
    $height = wc_get_order_item_meta( $item_id, 'Height', true );
    $meta[ $item_id ] = [
        'width' => $width,
        'height' => $height,
    ];
};

do_something_with_meta( $meta );

Some option types, such as checkboxes, image buttons, and text labels, allow selecting multiple values. In that case, the value stored in the database is a string where each value is separated by a pipe character “|”.

For example, if choices “Front” and “Back” were selected for a checkbox option, the meta value stored in the database would be “Front | Back”. Developers can easily parse that string to get the separate values (e.g. with the explode function).

Related Articles

• How to add custom code snippets to your website
• How to only display variation names in the 'Products' option type
• How does the plugin work with the REST API?
• Can I export product add-ons to my CRM or mailing list?
• How can I change or translate the error messages in WooCommerce Product Options?
• How to add custom CSS to your site