Calculate Balance From Credit and Debit Using PHP MySQL

Calculate Balance From Credit and Debit Using PHP MySQL

by

Hi friends, in this tutorial, you will learn how to calculate balance from credit and debit using PHP MySQL and get the direct code you can use to calculate running balances 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.

Calculate Balance From Credit and Debit Using PHP MySQL

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>

Conclusion:- I hope this tutorial will surely resolve your problem. If there is any doubt then please leave a comment below.


Leave a Comment