WooCommerce prices location in DB

Question

I'm looking for the location of a product's prices according to the option which the user selects in the product price. For example, this WooCommerce shop has a select which prices change according to the user selection.

I want to know where are prices for different options stored in DB.

Thanks

Answer 1

All the data like different prices of a product custom post type are store (for each product) in postmeta table.

To find the post id of all products you have to use this query on posts table:

SELECT *  FROM 'posts' WHERE 'post_type' = 'product'

For each product id (post_id), you can retrieve all related data with this query on postmeta table:

SELECT * FROM 'postmeta' WHERE 'post_id' = nnnn

(nnnn is the number id (post_id) of a product)

You will get the list of all product properties metakey and metavalues.
For related price meta_key(s) you have, for example:
- _regular_price
- _sale_price
- _price
- …

To get a particular value of a product meta_key, you can use the wordpress function:

get_post_meta($post_id, '$meta_key');

Answer 2

As LoicTheAztec told, the product data lives in the posts and postmeta tables. Here is a query, I used to get the prices in my environment (WC Role Based Price installed)

SELECT
  wpp.ID,
  wppm.meta_key AS FIELD,
  wppm.meta_value AS VALUE,
  wppm.*
FROM wp_posts AS wpp
  LEFT JOIN wp_postmeta AS wppm
    ON wpp.ID = wppm.post_id
WHERE wpp.post_type = 'product'
      AND (wppm.meta_key = '_regular_price'
      OR wppm.meta_key = '_sale_price'
      OR wppm.meta_key = '_price'    
      OR wppm.meta_key = '_product_attributes')
ORDER BY wpp.ID ASC, FIELD ASC, wppm.meta_id DESC;

Perhaps this is helpful.

Answer 3

Ηere are three meta_keys in postmeta table.

[ _sale_price, _regular_price, _price ]

First array contains value and second array contains WHERE condition. You can add more conditions.

You can update values using the following code.

//update _price
$wpdb->update( 
    $wpdb->postmeta, 
    array( 'meta_value' => $default_product_price ), 
    array( 'meta_key' => '_price' )
);
//update _regular_price
$wpdb->update( 
    $wpdb->postmeta, 
    array( 'meta_value' => $default_product_price ), 
    array( 'meta_key' => '_regular_price' )
);
//update _price
$wpdb->update( 
    $wpdb->postmeta, 
    array( 'meta_value' => $default_sale_price ), 
    array( 'meta_key' => '_sale_price' )
);

Answer 4

This prints product title and it's price.

 SELECT distinct p.id, p.post_title,pm.meta_key, pm.meta_value 
 FROM {db_prefix}_postmeta pm 
inner join {db_prefix}_posts p on p.id= pm.post_id
WHERE pm.meta_key = '_sale_price'