1. PHP
  2. How to Sum Column in MySQL using PHP/MySQLi

How to Sum Column in MySQL using PHP/MySQLi

Submitted by nurhodelta_17 on Monday, April 12, 2021 - 13:47.
Language
PHP

In this tutorial, I'm going to show you how to sum MySQL columns using PHP/MySQLi. I've also included in this tutorial the use of GROUP BY in MySQLi query and the 2 MySQLi methods which I have included in the comments. This tutorial does not include a good design but will give you an idea of the topic.

Getting Started

  1. Download and Install XAMPP or any equivalent to run PHP script.
  2. Open the XAMPP's Control Panel and start the "Apache" and "MySQL".
  3. Prepare a text editor such as notepad++ and sublime text editor for the coding stages.
  4. Create a new folder in your XAMPP's "htdocs" folder for this tutorial.

Creating our Database

First, we're going to create our database.

  1. Open PHPMyAdmin in a browser. i.e. http://localhost/phpmyadmin
  2. Click databases, create a database and name it as "sum".
  3. After creating a database, click the SQL and paste the below codes. See the image below for detailed instructions.
  1. CREATE TABLE `product` (
  2.   `productid` int(11) NOT NULL AUTO_INCREMENT,
  3.   `product_name` varchar(30) NOT NULL,
  4. PRIMARY KEY(`productid`)
  1. CREATE TABLE `sales` (
  2.   `salesid` int(11) NOT NULL AUTO_INCREMENT,
  3.   `productid` int(11) NOT NULL,
  4.   `sales_qty` double NOT NULL,
  5. PRIMARY KEY(`salesid`)
sum

Inserting Data into our Database

Next is to insert sample data into our database. I this tutorial, we're gonna insert sample products.

  1. Click the "sum" database that we have created.
  2. Click the SQL tab and paste the code below.
  1. INSERT INTO `product` (`product_name`) VALUES
  2. ('Apple'),
  3. ('Orange'),
  4. ('Strawberry');

Creating our Connection

The next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

  1. <?php
  2.  
  3. //MySQLi Procedural
  4. //$conn = mysqli_connect("localhost","root","","sum");
  5. //if (!$conn) {
  6. //      die("Connection failed: " . mysqli_connect_error());
  7. //}
  8.  
  9. //MySQLi OOP
  10. $conn = new mysqli("localhost","root","","sum");
  11. if ($conn->connect_error) {
  12.     die("Connection failed: " . $conn->connect_error);
  13. }
  14.  
  15. ?>

Creating Our Table and our Form

Next is to create our table and our add form. In this case, we will create a sample sales table and add a sale form. We name this "index.php".

  1. <?php include('conn.php'); session_start(); ?>
  2. <!DOCTYPE html>
  3. <html>
  4. <head>
  5. <title>How to Sum Column in MySQL using PHP/MySQLi</title>
  6. </head>
  7. <body>
  8.         <h3>Sales Table</h3>
  9.         <table border="1">
  10.                 <th>Product Name</th>
  11.                 <th>Quantity</th>
  12.         <?php
  13.                 $total_qty=0;
  14.                
  15.                 //MySQLi Procedural
  16.                 //$query=mysqli_query($conn,"select * from sales left join product on product.productid=sales.productid order by product.product_name asc");
  17.                 //while($row=mysqli_fetch_array($query)){
  18.                 /*      ?>
  19.                                 <tr>
  20.                                         <td><?php echo $row['product_name']; ?></td>
  21.                                         <td><?php echo $row['sales_qty']; ?></td>
  22.                                 </tr>
  23.                                
  24.                         <?php */
  25.                 //      $total_qty += $row['sales_qty'];
  26.                 //}
  27.                
  28.                 //MySQLi OOP
  29.                 $query=$conn->query("select * from sales left join product on product.productid=sales.productid order by product.product_name asc");
  30.                 while($row=$query->fetch_array()) {
  31.                         ?>
  32.                                 <tr>
  33.                                         <td><?php echo $row['product_name']; ?></td>
  34.                                         <td><?php echo $row['sales_qty']; ?></td>
  35.                                 </tr>
  36.                         <?php
  37.                         $total_qty += $row['sales_qty'];
  38.                 }
  39.         ?>
  40.         <tr>
  41.                 <td>TOTAL QTY:</td>
  42.                 <td><?php echo $total_qty; ?></td>
  43.        
  44.         </tr>
  45.         </table>
  46.         <div style="position:relative; left: 300px;">
  47.         <h3>Group By Product</h3>
  48.         <ul>
  49.         <?php
  50.                 //MySQLi Procedural
  51.                 //$a=mysqli_query($conn,"select *, sum(sales_qty) as total_sales from sales left join product on product.productid=sales.productid group by sales.productid");
  52.                 //while($arow=mysqli_fetch_array($a)){
  53.                 /*      ?>
  54.                         <li>Total <?php echo $arow['product_name'] ?>: <?php echo $arow['total_sales']; ?></li>
  55.                         <?php */
  56.                 //}
  57.                
  58.                 //MySQLi OOP
  59.                 $a=$conn->query("select *, sum(sales_qty) as total_sales from sales left join product on product.productid=sales.productid group by sales.productid");
  60.                 while($arow=$a->fetch_array()){
  61.                         ?>
  62.                         <li>Total <?php echo $arow['product_name'] ?>: <?php echo $arow['total_sales']; ?></li>
  63.                         <?php
  64.                 }
  65.         ?>
  66.        
  67.         </ul>
  68.         <h3>Insert New Sales</h3>
  69.         <form method="POST" action="add_sale.php">
  70.                 <select name="sales_product">
  71.                         <option value="0">Select Product</option>
  72.                         <?php
  73.                                 $p=$conn->query("select * from product");
  74.                                 while($prow=$p->fetch_array()){
  75.                                         ?>
  76.                                         <option value="<?php echo $prow['productid']; ?>"><?php echo $prow['product_name']; ?></option>
  77.                                         <?php
  78.                                 }
  79.                                
  80.                                 //$p=mysqli_query($conn,"select * from product");
  81.                                 //while($prow=mysqli_fetch_array($p)){
  82.                                 /*      ?>
  83.                                         <option value="<?php echo $prow['productid']; ?>"><?php echo $prow['product_name']; ?></option>
  84.                                         <?php */
  85.                                 //}
  86.                         ?>
  87.                 </select>
  88.                 Qty: <input type="text" name="sales_qty" required>
  89.                 <input type="submit" value="ADD">
  90.         </form>
  91.         <span>
  92.                 <?php
  93.                         if (isset($_SESSION['msg'])){
  94.                                 echo $_SESSION['msg'];
  95.                                 unset ($_SESSION['msg']);
  96.                         }
  97.                 ?>
  98.         </span>
  99.         </div>
  100. </body>
  101. </html>

Creating our Add Code

Lastly, we create our add sale code. We name this code "add_sale.php".

  1. <?php
  2.        
  3.         include('conn.php');
  4.         session_start();
  5.         $product=$_POST['sales_product'];
  6.         $qty=$_POST['sales_qty'];
  7.                 if ($product==0){
  8.                         $_SESSION['msg']="Please select a product";
  9.                         header('location:index.php');
  10.                 }
  11.                 else{
  12.                         //MySQLi Procedural
  13.                         //mysqli_query($conn,"insert into sales (productid,sales_qty) values ('$product','$qty')");
  14.                        
  15.                         //MySQLi OOP
  16.                         $conn->query("insert into sales (productid,sales_qty) values ('$product','$qty')");
  17.                         header('location:index.php');
  18.                 }
  19. ?>

Demo

That's it! You can now test the simple web application you have created. If you have encountered any errors, please review your code, maybe you have missed some steps.

That's the end of this tutorial. I hope you have learned something useful to adds up to your knowledge on developing a Web Application with the use of PHP programming language.

Explore more on this website for more tutorials and free source codes.

Happy Coding :)

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Tags
Sum Column
sum table column
sum row
sum total row
sum total column
php sum column
php sum table row
php sum row
php
MySQL Database
MySQLi
MySQLi Procedural
MySQLi OOP

Comments

Submitted by sarvar (not verified) on Mon, 04/16/2018 - 01:07

How to make when entered the value of the automatic input date and how to make the amount for the month and how many people ordered?

Submitted by salomon on Wed, 04/18/2018 - 02:21

how to make the amount for the month and how many people ordered?

To have the order of the month..simply add a new column for "sales" table and name it whatever you like for ex "sales_date". Then in the add code, add value to "sales_date" using NOW() ex. "INSERT INTO sales (productid,sales_qty,sales_date) VALUES ('$product','$qty',NOW())". Don't forget to set up your timezone using date_default_timezone_set() function. Then when getting the sum for the month simply add a WHERE clause in the query ex "WHERE month(sales_date) = '6'". Note: 6 represents JUNE.

Submitted by salomon on Sun, 04/22/2018 - 04:29

How can I contact you? Do you have mail or telegrams?

Submitted by Alfrecil (not verified) on Tue, 02/22/2022 - 14:22

how to contact you sir