How to Sum Column in MySQL using PHP/MySQLi

Submitted by: 
nurhodelta_17
Monday, September 4, 2017 - 16:05
Language: 
PHP
Visitors have accessed this post 3289 times.

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.

  1. CREATE TABLE `product` (
  2.   `productid` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `product_name` VARCHAR(30) NOT NULL,
  4. PRIMARY KEY(`productid`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  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`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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 SQL and paste the code below.

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

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

  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; top: -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 as "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. ?>
Tags: 
Sum Column
sum table column
sum row
sum total row
sum total column
php sum column
php sum table row
php sum row
nurhodelta_17's picture

Article by nurhodelta_17

nurhodelta_17 has submitted 272 source code / articles.

If you like this post, you can follow SourceCodester on Twitter.

Subscribe to SourceCodester feed via RSS or EMAIL to receive instant updates.

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

sarvar (not verified)

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?

salomon

Wed, 04/18/2018 - 02:21

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

nurhodelta_17

Wed, 04/18/2018 - 14:50

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

How can I contact you?

salomon

Sun, 04/22/2018 - 04:29

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

Add new comment

More information about text formats

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.