How to Perform Arithmetic Operations Across Columns of a MySQL Table Using 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.

SELECT Concat(itemprice * quantity) AS totalprice FROM Customers_Table ");

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.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $result= mysql_query("SELECT Concat(itemprice * quantity) AS subtotal FROM orders_table"); while($row = mysql_fetch_assoc($result)) { $sentence= $row['fulldetail'] . "<br>"; echo $sentence; } ?>

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.

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

Actual PHP Output