When using e-commerce programs, it is inevitable that you will need to change prices from time to time. In some cases, however, you may need to increase all prices up or down by a specific amount or percentage. If you have a large number of products this will be extremely time consuming. Though osCommerce does not have the ability to make price changes to all products at once, you can still do it via a short SQL query via your phpMyAdmin. Follow along below as we guide you in how to perform a mass price change in osCommerce using MySQL.
How to perform an osCommerce bulk price change in phpMyAdmin
- Log into your cPanel dashboard.
- Locate the Databases catageory and click on the phpMyadmin tool icon.
- Now that you are in the phpMyAdmin are, the database name for your osCommerce in the left side panel. Click on the name of the database to open its information in the right panel.
- A list of all the tables will appear in the right panel along with a row of tabs across the top of the panel. Click on the SQL tab to move to the next step.
- This is the SQL Query construction screen where you will construct the query that will add the price change to the database. Look below for a few code samples.
Sample code to increase all prices by fifteen cents (.10).
UPDATE products SET products_price = products_price+0.10
Sample code to decrease all prices by ten percent (10%).
UPDATE products SET products_price = products_price*.90
Sample code to increase all prices by ten percent (15%).
UPDATE products SET products_price = products_price*1.15
- After entering the query code, click on the Go button in the lower right hand corner to send the command to the MySQL database to perform the price change. For our example, we will lower all prices by 15% (perhaps for a store-wide sale).
- Once the query runs, a success message will appear at the top displaying the number of rows that were changed. Below is a before and after example of some prices using this method.