How to Perform Arithmetic Operations Across Columns of a MySQL Table Using PHP



PHP







In this article, we show how to perform arithmetic across columns of a MySQL table using PHP.

This can be useful for all kinds of applications, such as a retail operations.

Let's say you have a column on a table of a customer who has an item in his shopping cart and the customer has set the quantity of the item to 7. So in one column we have the price of the item and in another column we have the quantity. So we multiple the item price by the quantity to get the subtotal for that item.

This is directly useful for retail operations.

To do this in MySQL, you use the MySQL Concat() function along with the arimetic operator of the arithmetic operation we want to perform.



So the above is the MySQL code to multiply the itemprice by the quantity.

We'll now show how to tie this in with PHP.

PHP Code

The PHP code to multiply the itemprice by the quantity is shown below.



So the following code gets the data required to make a connection to a database and actually selects a database.

We then create a variable named $result that selects the table, orders_table, and selects and concatenates the 2 columns, itemprice and quantity. We simply mulitiply the itemprice column with the quantity column to get the total price of this item (or at least the subtotal before tax if tax applies).

Once we multiply these 2 columns together, we save the result as subtotal. This subtotal value can be seen as a new row representing the product of itemprice and quantity.

And we take this value from the table named order_table.

Example

So now we show an actual example of a MySQL table and an arithmetic operation we do between columns of the table.

Below is a MySQL table representing a customer order.

MySQL Orders table

I use the exact PHP code shown above. The result is shown below.

Actual PHP Output


700.00




MySQL Arithmetic Operators

So we've shown the multiplication operator.

Below is a list of the most common MySQL arithmetic operators.

MySQL Arithmetic Operator Mathematical Operation
+ Addition
- Subtraction
* Multiplication
/ Division
%  (or MOD) Modulus (finds remainder of division operation)



Related Resources

How to Get the Sum of All Rows of a Column of a MySQL Table Using PHP

How to Get the Average of All Rows of a Column of a MySQL Table Using PHP

How to Get the Minimum Value of a Column of a MySQL Table Using PHP

How to Get the Maximum Value of a Column of a MySQL Table Using PHP

HTML Comment Box is loading comments...