This was interesting and not something I use to get in my day to day job.
A friend of mine is setting up a start-up business to sell English books in The Netherlands. The enthusiastic me offered the help, only to realise that  later that it isn’t a very straight forward (kind of) job to set up a web shop for a real business. And that too when someone wants to go with minimal budget.
Anyhow cutting it short, the web shop uses WooCommerce plugin for WordPress for running the day to day business. At one point of time, this guy decided to offer sales on lot of books only to decide later that he also wanted to introduce coupons. And I was like, what’s that problem in that.
Well, it turned out that he did not want to give away coupon discount on sale items. Although, Woo Commerce supports this functionality to limit the use of coupons there was another hesitation this fellow had. He did not want to disappoint consumers and distract them with two discount i.e. sales and coupon. So, he decided to get rid of sale offer completely. The biggest problem was that there are quite many books with sale offer and he asked my help if I can look into how it can be turned off in one go.
I came forward saying BIG DEAL, open Google and used by search skills but was disappointed by the results. There was no one who had offered the solution and most of them were kind of hack which were hiding the sale price either in CSS or in the PHP hack.
Not something which I wanted to do. So, I asked for the MySQL database credentials and started looking for SQL Server Management Studio alternative for MySQL. Hey, it was my first encounter with MySQL. Wasn’t difficult, and there is something called MySQL WorkBench.
The WordPress database is quite simple in schema and wasn’t difficult to reverse engineer. The first query I executed was like this:
UPDATE MyDataBase.PREFIX_postmeta
SET meta_value = ''
WHERE meta_key = '_sale_price'
The result was partially ok as the sales price disappeared from the product details (Sales Price), but the Price was of the product was still shown which was entered as Sales Price earlier. Btw, the sales icon etc. also disappeared from user interface.
After digging more into the data table, I price is actually stored in three places.
- Price
- Regular Price
- Sales Price
And I had only removed the sales price while Price still contains the value of the old sales price. So, I wrote the following query to update the Price value based on the Regular Price.
UPDATE
MyDataBase.PREFIX_postmeta AS Price
INNER JOIN MyDataBase.PREFIX_postmeta AS RegularPrice ON
RegularPrice.post_Id = Price.Post_Id
SET
Price.meta_value = RegularPrice.meta_value
WHERE
RegularPrice.meta_key = '_regular_price'
AND Price.meta_key = '_price'
AND RegularPrice.meta_value != Price.meta_value
That’s it. All the products in the shop are displaying the regular price and do not have any sales price anymore. This is quite handy if you have lots and lots of products and want to get rid of them in one shot.
Note: I don’t take any guarantee of the consequences of this script which can have any side effect on your database. And it is highly recommended that you take a backup of database before running any script to manipulate with the data.
Be First to Comment