PHP and MYSQL Month wise total summary

A simple design given below for month wise total amount fetch data using bootstrap 4.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">

<div class="col-md-6 mx-auto">
<div class="jumbotron p-2 mt-4">
    <h3>PHP & MySql Tutorial</h3>
    <p>
      Month Wise Total Amount Summary
    </p>
</div>

<table class="table table-striped">
<tr>
   <th>Month</th>
   <th>Total</th>
</tr>

<tr>
   <td>October</td>
   <td>500</td>
</tr>
</table>

</div>

</div>
</div>
</body>
</html>

Output :


Now for fetch data we need a MySql query.

SELECT 
MONTHNAME(created) as mname, 
sum(amount) as total
FROM transactions
GROUP BY MONTH(created)

Here MONTHNAME mysql function will return month name for created field. Now we can write simple code in php to execute this query in PHP.

<?php 
//database connection
$conn = new mysqli("localhost","root","","xeasy_accounting");
//sql 
$sql = "SELECT 
MONTHNAME(created) as mname, 
sum(amount) as total
FROM transactions
GROUP BY MONTH(created)";
//execute sql
$result = $conn->query($sql);
?>

Now we will fetch data using simple while loop.

<?php while ($row = $result->fetch_object()): ?> 

<tr>
    <td><?php echo $row->mname; ?></td>
    <td><?php echo $row->total; ?></td>
</tr>

<?php endwhile; ?>

That’s it !!

One Reply to “PHP and MYSQL Month wise total summary”

  1. I am very happy to read this. This is the type of manual that needs to be given and not the accidental misinformation that’s at the other blogs. Appreciate your sharing this greatest doc.

Leave a Reply to Free Stuff Cancel reply

Your email address will not be published. Required fields are marked *