How to get and update WooCommerce Regular and Sale prices with or without a post ID
If you simply just want to get to the commands, that's cool here are the links to take you right there. If you prefer to read what the commands do, keep reading...
Display Regular and Sale prices on the Product page
Display Regular and Sale prices within the Functions.php file or plugin
Updating WooCommerce Price Tables
Let's say you need to get Regular and Sale prices from a WooCommerce product from within a product page - therefore you know the product ID - or from somewhere external to a product page, like your functions.php file or maybe a plugin you are working on.
Below I will show you how you can find out if the product has a Sale price and the quickest/easiest way to get the data you need depending on either of the situations above. These commands are written in PHP as per most of WordPress.
I like to explain things using examples, it just helps to understand the code. Let's start off by displaying the product Sale or Regular price somewhere on a Product page.
Display Regular & Sale Prices on the Product page
Now, most WooCommerce themes should have this already coded in so in the real world you probably wouldn't need to add code to display the Sale price, but, I will use it just to explain how to do it just in case you need to display the price somewhere else on the page.
For this example, let's say you would like to display the Sale price next to the stock level on the WooCommerce product page.
Now the nice people at WordPress have created some simple commands to use which makes it easy as peas! No need for any SQL SELECT commands and all that fluff here!
First, you need to make sure your file - I will be using stock.php within WooCommerce - has the Global command to get the product data (it most likely has, but check anyway).
I have added two versions of the same command, one to get the value into a variable and the other just echo it out the screen.
//Add the global command to get the product data
global $product;
//Add the two field strings to a variable
$price_regular = $product->regular_price;
$price_sale = $product->sale_price;
//Display the two field strings to the screen
echo $product->regular_price;
echo $product->sale_price;Display Regular & Sale Prices from Functions.php or Plugin
If you wish to use a function/class from within the Functions.php file or maybe you are creating a plugin and need to get this data, this is how you do it.
Once again WordPress have made it nice and easy to get this information. Below are the commands you need.
First off you need to get the whole post data using the wc_get_product command, if you display the output of this command you will see all the elements to it (I have included the command to display them below).
Once you have this data all you need to do is choose which element you wish to display, in this example get_regular_price and get_sale_price.
//Get the whole post/product data
$product = wc_get_product( $av->post_id );
//Display the elements from this command
echo "<pre>", print_r( $product ), "</pre>";
//Add the two field strings to a variable
$reg_price = $product->get_regular_price();
$sale_price = $product->get_sale_price();
//Display the two field strings to the screen
echo $product->get_regular_price();
echo $product->get_sale_price();Updating WooCommerce Price Tables
There are times when you might want to update the product price directly in the database tables. Maybe you are creating a plugin that updates pricing.
The prices are stored in the postmeta table, this includes both _regular_price and _sale_price.
There is also another price entry in the table called _price. This entry holds the "live" price from the other two.
For example, if you do NOT have a Sale price _price will hold the _regular_price, however, if there IS a Sale price then the _price will hold the Sale price. It's the price you can buy the product at.
Below are examples of PHP you can use to UPDATE the price using PHP and SQL commands.
It is important to refresh the cache after you make the updates using the commands below.
//Updating the postmeta table
global $wpdb;
$wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}postmeta SET meta_value = '$var1' WHERE post_id = '$post_id' AND meta_key = '$var2' " ) );
//Refresh cache so variable ranges are updated
$wpdb->get_var( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}options WHERE option_name = %s ", '_transient_wc_var_prices_' . $post_id ) );
$wpdb->get_var( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}options WHERE option_name = %s ", '_transient_timeout_wc_var_prices_' . $post_id ) );
And that is it, very simple to do.
Happy coding!