How to Create an Expenses and Incomes Tracker In PHP With MySQL Databse
In this PHP tutorial we will see how to create an Expense and Income Tracker web project with MySQL Database.
It allows users to add, view, and delete transactions (expenses and incomes) and displays a summary of total expenses, total incomes, and the net balance.
The application uses a MySQL database to store transaction data, and the user interface is managed with HTML, CSS, and JavaScript for dynamic interactions.
What We Are Gonna Use In This Project:
- PHP Programming Language.- HTML, CSS, JavaScript.
- MySQL Database.
- Font-Awesome.
- VS Code Editor.
- VS Code Editor.
Project Source Code:
DatabaseConnection Class
<?php
// Define a class for managing database connections
class DatabaseConnection{
// Private properties to store connection details
private $host;
private $username;
private $password;
private $database;
private $charset;
private $pdo; // PDO object for handling database connections
// Constructor method to initialize connection details and establish connection
public function __construct($host, $username, $password, $database,
$charset = "utf8")
{
// Assign connection details to object properties
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->database = $database;
$this->charset = $charset;
$this->connect();
}
// Private method to establish database connection
public function connect(){
// Construct DSN (Data Source Name) for PDO connection
$dsn = "mysql:host={$this->host}; dbname={$this->database};
charset={$this->charset}";
try{
// Create a new PDO object and set attributes
$this->pdo = new PDO($dsn, $this->username, $this->password);
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
}
catch(PDOException $e){
// If connection fails, output error message and terminate script
die("Connection failed: " . $e->getMessage());
}
}
// Method to execute a query with optional parameters
public function query($sql, $params = []){
// Prepare and execute the SQL statement with provided parameters
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
// Return the PDO statement object
return $stmt;
}
// Method to fetch all rows from a query result with optional parameters
public function fetchAll($sql, $params = []){
// Execute the query and return all fetched rows
$stmt = $this->query($sql, $params);
return $stmt->fetchAll();
}
}
?>
Transaction Class
<?php
class Transaction{
private $id;
private $type;
private $description;
private $amount;
public function __construct($id, $type, $description, $amount){
$this->id = $id;
$this->type = $type;
$this->description = $description;
$this->amount = $amount;
}
public function getId(){ return $this->id; }
public function getType(){ return $this->type; }
public function getDescription(){ return $this->description; }
public function getAmount(){ return $this->amount; }
}
?>
TransactionDAO Class
<?php
// Require the DatabaseConnection class file
require_once 'DatabaseConnection.php';
class TransactionDAO{
public function __construct(){
// Initialize a new DatabaseConnection object
$this->db = new DatabaseConnection("localhost", "root", "",
"php_expense_income_db");
}
// Method to retrieve all transactions from the database
public function getAllTransactions(){
$sql = "SELECT * FROM `transaction_table`";
return $this->db->fetchAll($sql);
}
// Method to add a new transaction to the database
public function addTransaction(Transaction $transaction){
// Define SQL query to insert a new row into the transaction_table
$sql = "INSERT INTO `transaction_table`(`transaction_type`, `description`,
`amount`) VALUES (?, ?, ?)";
// Define parameters for the SQL query
$params = [$transaction->getType(), $transaction->getDescription(),
$transaction->getAmount()];
// Execute the SQL query using query method of DatabaseConnection
$this->db->query($sql, $params);
}
// Method to delete a transaction from the database by its ID
public function deleteTransaction($id){
$sql = "DELETE FROM `transaction_table` WHERE id = ?";
$this->db->query($sql, [$id]);
}
}
?>
process_transaction.php File
<?php
// Include necessary classes
require_once 'Transaction.php';
require_once 'TransactionDAO.php';
// Retrieve JSON data from the client
$jsonData = file_get_contents('php://input');
$data = json_decode($jsonData, true);
// Create a new Transaction object
$newTransaction = new Transaction(null, $data['type'], $data['description'],
$data['amount']);
// Create a new TransactionDAO
$transactionDAO = new TransactionDAO();
// Add the new transaction to the database
$transactionDAO->addTransaction($newTransaction);
// Send a response back to the client
echo json_encode(['status'=>'succes', 'message'=>'Transaction added successfully']);
?>
remove_transaction.php File
<?php
require_once 'TransactionDAO.php';
// Retrieve JSON data from the client
$jsonData = file_get_contents('php://input');
$data = json_decode($jsonData, true);
// Create a new Transaction object
$transactionDao = new TransactionDAO();
// Remove the transaction from the database
$transactionDao->deleteTransaction($data['transactionId']);
// Send a response back to the client
echo json_encode(['status'=>'succes', 'message'=>'Transaction removed successfully']);
?>
tracker_dashboard.php File
<?php
require_once 'TransactionDAO.php';
require_once 'TransactionValuesCalculation.php';
$trsDAO = new TransactionDAO();
// Retrieve all transactions
$transactions = $trsDAO->getAllTransactions();
$expenses = TransactionValuesCalculation::getTotalExpenses($transactions);
$incomes = TransactionValuesCalculation::getTotalIncomes($transactions);
$total = $incomes - $expenses;
if($total < 0){
$formattedTotal = '-$'.number_format(abs($total), 2);
}
else{
$formattedTotal = '+$'.number_format(abs($total), 2);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Expense And Income Tracker</title>
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.2/css/all.min.css">
<link rel="stylesheet" href="style.css">
</head>
<body>
<div id="header">
<span id="title">
Expense and Income Tracker
</span>
</div>
<div id="dashboard">
<div class="data-panel" id="expense-panel"><i class="fas fa-shopping-cart"></i>
<div>Expense: <?php echo '$'.number_format(abs($expenses), 2); ?></div>
</div>
<div class="data-panel" id="income-panel"><i class="fas fa-money-bill-wave">
</i><div>Income: <?php echo '$'.number_format(abs($incomes), 2); ?></div>
</div>
<div class="data-panel" id="total-panel"><i class="fas fa-chart-pie"></i>
<div>Total: <?php echo $formattedTotal; ?></div>
</div>
<div id="buttons">
<button class="button" id="add-transaction"><i class="fas fa-plus"></i>
Add Transaction</button>
</div>
<table id="transaction-table">
<thead>
<tr>
<th>ID</th>
<th>Type</th>
<th>Description</th>
<th id="amount-header">Amount<i class="fas fa-sort"></i></th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
foreach($transactions as $transaction){
// Define a class and style based on transaction type
$typeClass = ($transaction['transaction_type'] == 'Expense')
? 'expense-type' : 'income-type';
echo'<tr>'.
'<td>'.$transaction['id'].'</td>'.
'<td><span class="'.$typeClass.'">'.
$transaction['transaction_type'].'</span></td>'.
'<td>'.$transaction['description'].'</td>'.
'<td>$'.$transaction['amount'].'</td>'.
'<td class="remove-icon"><button class="delete-button"
data-transaction-id="'.$transaction['id'].'">
<i class="fas fa-trash"></i>Delete</button></td>'.
'</tr>';
}
?>
</tbody>
</div>
<div id="add-transaction-dialog">
<div id="dialog-title">Add Transaction</div>
<div class="input-group">
<label for="transaction-type">Type:</label>
<select id="transaction-type">
<option value="Expense">Expense</option>
<option value="Income">Income</option>
</select>
</div>
<div class="input-group">
<label for="transaction-description">Description:</label>
<input type="text" id="transaction-description">
</div>
<div class="input-group">
<label for="transaction-amount">Amount:</label>
<input type="text" id="transaction-amount">
</div>
<div class="button-group">
<button class="button" id="add-transaction-button">Add</button>
<button class="button cancel" id="cancel-transaction-button">
Cancel</button>
</div>
</div>
</body>
<script>
// show dialog
document.getElementById('add-transaction').addEventListener('click', function(){
document.getElementById('add-transaction-dialog').style.display = 'block';
});
// hide dialog
document.getElementById('cancel-transaction-button').addEventListener('click',
function(){
document.getElementById('add-transaction-dialog').style.display = 'none';
});
</script>
<script>
// add transaction
document.getElementById('add-transaction-button').addEventListener('click',
function(){
// Retrieve input values
var type = document.getElementById('transaction-type').value;
var description = document.getElementById('transaction-description').value;
var amount = document.getElementById('transaction-amount').value;
// Create a new Transaction object
var newTransaction = { type:type, description:description, amount:amount };
// Send the data to the server using AJAX
var xhr = new XMLHttpRequest();
xhr.open('POST', 'process_transaction.php', true);
xhr.setRequestHeader('Content-Type', 'application/json');
xhr.onreadystatechange = function(){
if(xhr.readyState == 4 && xhr.status == 200){
location.reload();
//console.log(xhr.responseText);
}
}
// Convert the transaction object to JSON
var jsonData = JSON.stringify(newTransaction);
// Send the JSON data to the server
xhr.send(jsonData);
});
</script>
<script>
document.getElementById('amount-header').addEventListener('click', function(){
// Get all table rows
var tableRows = document.querySelectorAll('#transaction-table tbody tr');
// Convert NodeList to an array for easier manipulation
var rowsArray = Array.from(tableRows);
// Check if the rows are already sorted in ascending order
var ascending = this.classList.contains('asc');
// Sort the rows based on the amount column
rowsArray.sort(function(a,b){
// Extract the amount values from the rows and convert them to numbers
var amountA = parseFloat(a.children[3].textContent.replace('$',''));
var amountB = parseFloat(b.children[3].textContent.replace('$',''));
// Determine whether to sort in ascending or descending order
if (ascending) { return amountA - amountB; /*Sort in ascending order*/ }
else{ return amountB - amountA; /* Sort in descending order*/}
});
// Update the table with sorted rows
var tbody= document.querySelector('#transaction-table tbody');
// Clear the existing table body content
tbody.innerHTML = '';
rowsArray.forEach(function(row){
// Append each row to the table body in the sorted order
tbody.appendChild(row);
})
// Toggle sorting direction class
this.classList.toggle('asc');
});
</script>
<script>
// Add event listener for the "Delete" button
document.querySelectorAll('.delete-button').forEach(function(button){
button.addEventListener('click', function(){
// Get the transaction id
var transactionId = this.getAttribute('data-transaction-id');
// Confirm deletion (optional)
var confirmDeletion = confirm(
'Are you sure you want to delete this transaction?');
if(confirmDeletion){
// Make AJAX request to remove the transaction
var xhr = new XMLHttpRequest();
xhr.open('POST', 'remove_transaction.php', true);
xhr.setRequestHeader('Content-Type', 'application/json');
xhr.onreadystatechange = function(){
if(xhr.readyState == 4 && xhr.status == 200){
location.reload();
//console.log(xhr.responseText);
}
}
// Convert the transaction object to JSON
var jsonData = JSON.stringify({transactionId:transactionId});
// Send the JSON data to the server
xhr.send(jsonData);
}
});
});
</script>
</html>
The Final Result:
if you want the source code click on the download button below
