How to Sum Column in MySQL using PHP/MySQLi
In this tutorial, I'm going to show you how to sum MySQL column 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 idea on the topic.
Creating our Database
First, we're going to create our database.
1. Open 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 image below for detailed instruction.
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.
1. Click the "sum" database that we have created.
2. Click SQL and paste the code below.
INSERT INTO `product` (`product_name`) VALUES ('Apple'), ('Orange'), ('Strawberry');
Creating our Connection
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) { } ?>
Creating Our Table and our Form
Next is to create out table and our add form. In this case, we will create a sample sales table and add sale form. We name this as "index.php".
<!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; top: -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 echo $_SESSION['msg']; } ?> </span> </div> </body> </html>
Creating our Add Code
Lastly, we create our add sale code. We name this code as "add_sale.php".
<?php include('conn.php'); $product=$_POST['sales_product']; $qty=$_POST['sales_qty']; if ($product==0){ $_SESSION['msg']="Please select a product"; } 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')"); } ?>
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
salomon
Sun, 04/22/2018 - 04:29
Permalink
Contact
How can I contact you? Do you have mail or telegrams?
nurhodelta_17
Wed, 04/18/2018 - 14:50
Permalink
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.
https://www.facebook.com/nurhodelta17
salomon
Sat, 04/21/2018 - 19:03
Permalink
contact
How can I contact you?
salomon
Wed, 04/18/2018 - 02:21
Permalink
help
how to make the amount for the month and how many people ordered?
sarvar (not verified)
Mon, 04/16/2018 - 01:07
Permalink
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?
Add new comment