How to Sum Column in MySQL using PHP/MySQLi

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

Language

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

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

Creating our Database

First, we're going to create our database.

Open PHPMyAdmin in a browser. i.e. http://localhost/phpmyadmin
Click databases, create a database and name it as "sum".
After creating a database, click the SQL and paste the below codes. See the image below for detailed instructions.

CREATE TABLE `product` (
  `productid` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(30) NOT NULL,
PRIMARY KEY(`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sales` (
  `salesid` int(11) NOT NULL AUTO_INCREMENT,
  `productid` int(11) NOT NULL,
  `sales_qty` double NOT NULL,
PRIMARY KEY(`salesid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inserting Data into our Database

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

Click the "sum" database that we have created.
Click the SQL tab and paste the code below.

INSERT INTO `product` (`product_name`) VALUES
('Apple'),
('Orange'),
('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.

<?php
 
//MySQLi Procedural
//$conn = mysqli_connect("localhost","root","","sum");
//if (!$conn) {
//      die("Connection failed: " . mysqli_connect_error());
//}
 
//MySQLi OOP
$conn = new mysqli("localhost","root","","sum");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
?>

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".

<?php include('conn.php'); session_start(); ?>
<!DOCTYPE html>
<html>
<head>
<title>How to Sum Column in MySQL using PHP/MySQLi</title>
</head>
<body>
        <h3>Sales Table</h3>
        <table border="1">
                <th>Product Name</th>
                <th>Quantity</th>
        <?php
                $total_qty=0;
                
                //MySQLi Procedural
                //$query=mysqli_query($conn,"select * from sales left join product on product.productid=sales.productid order by product.product_name asc");
                //while($row=mysqli_fetch_array($query)){
                /*      ?>
                                <tr>
                                        <td><?php echo $row['product_name']; ?></td>
                                        <td><?php echo $row['sales_qty']; ?></td>
                                </tr>
                                
                        <?php */
                //      $total_qty += $row['sales_qty'];
                //}
                
                //MySQLi OOP
                $query=$conn->query("select * from sales left join product on product.productid=sales.productid order by product.product_name asc");
                while($row=$query->fetch_array()) {
                        ?>
                                <tr>
                                        <td><?php echo $row['product_name']; ?></td>
                                        <td><?php echo $row['sales_qty']; ?></td>
                                </tr>
                        <?php 
                        $total_qty += $row['sales_qty'];
                }
        ?>
        <tr>
                <td>TOTAL QTY:</td>
                <td><?php echo $total_qty; ?></td>
        
        </tr>
        </table>
        <div style="position:relative; left: 300px;">
        <h3>Group By Product</h3>
        <ul>
        <?php 
                //MySQLi Procedural
                //$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");
                //while($arow=mysqli_fetch_array($a)){
                /*      ?>
                        <li>Total <?php echo $arow['product_name'] ?>: <?php echo $arow['total_sales']; ?></li>
                        <?php */
                //}
                
                //MySQLi OOP
                $a=$conn->query("select *, sum(sales_qty) as total_sales from sales left join product on product.productid=sales.productid group by sales.productid");
                while($arow=$a->fetch_array()){
                        ?>
                        <li>Total <?php echo $arow['product_name'] ?>: <?php echo $arow['total_sales']; ?></li>
                        <?php 
                }
        ?>
        
        </ul>
        <h3>Insert New Sales</h3>
        <form method="POST" action="add_sale.php">
                <select name="sales_product">
                        <option value="0">Select Product</option>
                        <?php
                                $p=$conn->query("select * from product");
                                while($prow=$p->fetch_array()){
                                        ?>
                                        <option value="<?php echo $prow['productid']; ?>"><?php echo $prow['product_name']; ?></option>
                                        <?php
                                }
                                
                                //$p=mysqli_query($conn,"select * from product");
                                //while($prow=mysqli_fetch_array($p)){
                                /*      ?>
                                        <option value="<?php echo $prow['productid']; ?>"><?php echo $prow['product_name']; ?></option>
                                        <?php */
                                //}
                        ?>
                </select>
                Qty: <input type="text" name="sales_qty" required>
                <input type="submit" value="ADD">
        </form>
        <span>
                <?php
                        if (isset($_SESSION['msg'])){
                                echo $_SESSION['msg'];
                                unset ($_SESSION['msg']);
                        }
                ?>
        </span>
        </div>
</body>
</html>

Creating our Add Code

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

<?php
        
        include('conn.php');
        session_start();
        $product=$_POST['sales_product'];
        $qty=$_POST['sales_qty'];
                if ($product==0){
                        $_SESSION['msg']="Please select a product";
                        header('location:index.php');
                }
                else{
                        //MySQLi Procedural
                        //mysqli_query($conn,"insert into sales (productid,sales_qty) values ('$product','$qty')");
                        
                        //MySQLi OOP
                        $conn->query("insert into sales (productid,sales_qty) values ('$product','$qty')");
                        header('location:index.php');
                }
?>

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.

Comments

help

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?

help

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

Order of the month

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.