Hi friends, in this tutorial you will learn how to calculate balance from credit and debit using PHP MySQL and also get the direct code that you can execute in order to calculate running balance from the ledger. If you are wondering how to accomplish this task then you have come to the right place.
This is very important and often required in the case of accounting software or applications. However, if you are going to create any applications that need to display the running balance from the existing debit and credit that resides in the MySQL database then this tutorial will help you to accomplish this. On the other hand, you can get the balance according to individual users in the database if users exist.
Also read, Export Data From MySQL to Excel Using PHP
Steps to calculate balance from credit and debit using PHP MySQL
Step 1:- Establish the connection with the MySQL database as shown below.
dbconnect.php
<?php
$servername='localhost';
$username="root";
$password="";
try
{
$con=new PDO("mysql:host=$servername;dbname=myproject_db",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//echo 'connected';
}
catch(PDOException $e)
{
echo '<br>'.$e->getMessage();
}
?>
Step 2:- Create a table in your MySQL database as shown below
DDL Information of the table
CREATE TABLE ledger ( id int(10) NOT NULL AUTO_INCREMENT, transaction_date datetime NOT NULL, credit varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, debit varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Step 3:- Insert credit and debit in the database with transaction dates as shown below.
Step 4:- Now you can display the data with running balance as shown below.
Complete Code:-
<?php
include('dbconnect.php');
$sql = "SELECT id, transaction_date, credit, debit, COALESCE(((SELECT SUM(credit) FROM ledger b WHERE b.id <= a.id) - (SELECT SUM(debit) FROM ledger b WHERE b.id <= a.id)), 0) as balance FROM ledger a ORDER BY id DESC";
$stmt = $con->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Calculate Balance From Credit and Debit Using PHP MySQL</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<div class="container mt-3" style="width:60%;">
<h3>Calculate Balance From Credit and Debit Using PHP MySQL</h3>
<br>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Credit</th>
<th>Debit</th>
<th>Balance</th>
<th>Transaction Date</th>
</tr>
</thead>
<tbody>
<?php
if(count($data)>0)
{
foreach($data as $key=>$value)
{
?>
<tr>
<td><?php echo $key+1; ?></td>
<td><?php echo $value['credit'];?></td>
<td><?php echo $value['debit'];?></td>
<td><?php echo $value['balance'];?></td>
<td><?php echo $value['transaction_date'];?></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</div>
</body>
</html>