How to Sum Column in MySQL using PHP/MySQLi

Sneus · Thursday at 12:09 AM

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.

Download

You must upgrade your account or reply in the thread to view the hidden content.

Advertise Here

Advertise Here

Advertise Here

How to Sum Column in MySQL using PHP/MySQLi

More options

Sneus

452,496

344,676

344,684