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.

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>