Personal Finance Tracker JavaScript, PHP, MySQL

Okay, here's a basic example of a personal finance tracker using JavaScript (for the front-end), PHP (for the back-end), and MySQL (for the database).  I'll provide code snippets and explanations for each part.  This is a simplified example to illustrate the core concepts.  A real-world application would be more robust and secure.

**Conceptual Overview**

1.  **Database (MySQL):**
    *   Stores user data, transaction details, categories, etc.

2.  **Back-end (PHP):**
    *   Handles requests from the front-end (e.g., adding a transaction, retrieving data).
    *   Interacts with the database to read and write data.
    *   Provides an API (Application Programming Interface) that the front-end can communicate with.

3.  **Front-end (JavaScript, HTML, CSS):**
    *   Provides the user interface for interacting with the application.
    *   Sends requests to the PHP back-end.
    *   Displays the data received from the back-end.

**1. Database (MySQL)**

```sql
-- Database Name: finance_tracker

CREATE DATABASE IF NOT EXISTS finance_tracker;

USE finance_tracker;

-- Table: users
CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- Store hashed passwords!
    email VARCHAR(100) UNIQUE
);

-- Table: categories
CREATE TABLE IF NOT EXISTS categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category_name VARCHAR(50) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Table: transactions
CREATE TABLE IF NOT EXISTS transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category_id INT,
    transaction_date DATE NOT NULL,
    description VARCHAR(255),
    amount DECIMAL(10, 2) NOT NULL,  -- Up to 10 digits, 2 decimal places
    type ENUM('income', 'expense') NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL --category can be deleted and tranasction should be keeped
);
```

**Explanation:**

*   `finance_tracker`:  The name of the database.
*   `users`: Stores user information (username, hashed password, email).  **Important:**  Always hash passwords before storing them in the database.  Use functions like `password_hash()` in PHP.
*   `categories`: Stores transaction categories (e.g., "Food", "Rent", "Salary").  Links to a specific user.
*   `transactions`: Stores individual transaction details (date, description, amount, type - income/expense). Links to a specific user and category.
*   `FOREIGN KEY`:  Ensures data integrity by linking tables together.  For example, a transaction must belong to a valid user.
*   `ON DELETE CASCADE`: If a user is deleted, all their related categories and transactions are also deleted.
*   `ON DELETE SET NULL`: If a category is deleted, the `category_id` in associated transactions will be set to `NULL`.

**2. Back-end (PHP)**

Create a directory structure.  Let's assume:

```
finance_tracker/
??? api/
?   ??? db_connect.php
?   ??? add_transaction.php
?   ??? get_transactions.php
?   ??? ... other API endpoints ...
??? ... other files ...
```

**`api/db_connect.php` (Database Connection):**

```php
<?php
$servername = "localhost";  // Or your database host
$username = "your_db_username";
$password = "your_db_password";
$dbname = "finance_tracker";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$conn->set_charset("utf8"); //Set the character to utf8
?>
```

**Explanation:**

*   This file establishes a connection to your MySQL database.  **Replace** `"your_db_username"` and `"your_db_password"` with your actual database credentials.
*   `$conn`:  A variable to store the database connection object.
*   Error handling:  Checks if the connection was successful.
*   `$conn->set_charset("utf8")`: Set connection encoding to utf8 to ensure that all strings are properly encoded and no characters are lost.

**`api/add_transaction.php` (Add a Transaction):**

```php
<?php
header("Content-Type: application/json"); // Send JSON response

require_once 'db_connect.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $user_id = $_POST['user_id'] ?? null;   // Use null coalescing operator
    $category_id = $_POST['category_id'] ?? null; // Allow null category
    $transaction_date = $_POST['transaction_date'] ?? null;
    $description = $_POST['description'] ?? ''; // Empty string if not provided
    $amount = $_POST['amount'] ?? null;
    $type = $_POST['type'] ?? null;

    // Validate data (basic example)
    if (empty($user_id) || empty($transaction_date) || empty($amount) || empty($type)) {
        http_response_code(400); // Bad Request
        echo json_encode(array("message" => "Missing required fields."));
        exit;
    }

    $amount = floatval($amount); // Convert to float
    if (!in_array($type, ['income', 'expense'])) {
        http_response_code(400);
        echo json_encode(["message" => "Invalid transaction type."]);
        exit;
    }

    // Prepare and execute the SQL query
    $sql = "INSERT INTO transactions (user_id, category_id, transaction_date, description, amount, type)
            VALUES (?, ?, ?, ?, ?, ?)";

    $stmt = $conn->prepare($sql);

    if ($stmt === false) {
        http_response_code(500); // Internal Server Error
        echo json_encode(["message" => "Database error: " . $conn->error]);
        exit;
    }

    $stmt->bind_param("iisssd", $user_id, $category_id, $transaction_date, $description, $amount, $type);  // "iisssd" specifies data types

    if ($stmt->execute()) {
        http_response_code(201); // Created
        echo json_encode(array("message" => "Transaction added successfully."));
    } else {
        http_response_code(500);
        echo json_encode(array("message" => "Error adding transaction: " . $stmt->error));
    }

    $stmt->close();
    $conn->close();
} else {
    http_response_code(405); // Method Not Allowed
    echo json_encode(array("message" => "Method not allowed. Use POST."));
}
?>
```

**Explanation:**

*   `header("Content-Type: application/json")`: Sets the response content type to JSON.
*   `require_once 'db_connect.php'`:  Includes the database connection file.
*   `$_SERVER["REQUEST_METHOD"] == "POST"`:  Checks if the request method is POST.
*   `$_POST['...'] ?? null`: Retrieves data from the POST request. The `?? null` is the null coalescing operator: If the POST value is not set, it defaults to `null`.  This handles cases where optional fields are missing.
*   **Data Validation:**  Basic validation is performed to ensure required fields are present and the transaction type is valid.  **Crucially, you need more robust validation in a real application.**  Sanitize the data to prevent SQL injection and cross-site scripting (XSS) attacks.
*   **Prepared Statements:**  Uses prepared statements (`$conn->prepare()`, `$stmt->bind_param()`, `$stmt->execute()`) to prevent SQL injection.  This is a **must** for security.
*   `$stmt->bind_param("iisssd", ...)`: Binds parameters to the prepared statement. The string "iisssd" specifies the data types:
    *   `i`: integer
    *   `s`: string
    *   `d`: double (decimal)
*   Error handling:  Includes checks for database errors and returns appropriate HTTP status codes and JSON error messages.
*   HTTP Status Codes:
    *   `201 Created`: Transaction added successfully.
    *   `400 Bad Request`: Missing or invalid data.
    *   `405 Method Not Allowed`: Used wrong request method (e.g., GET instead of POST).
    *   `500 Internal Server Error`: Database error.

**`api/get_transactions.php` (Get Transactions):**

```php
<?php
header("Content-Type: application/json");

require_once 'db_connect.php';

if ($_SERVER["REQUEST_METHOD"] == "GET") {
    $user_id = $_GET['user_id'] ?? null;

    if (empty($user_id)) {
        http_response_code(400);
        echo json_encode(array("message" => "Missing user_id."));
        exit;
    }

    $sql = "SELECT transaction_id, category_id, transaction_date, description, amount, type
            FROM transactions
            WHERE user_id = ?";

    $stmt = $conn->prepare($sql);

    if ($stmt === false) {
        http_response_code(500);
        echo json_encode(["message" => "Database error: " . $conn->error]);
        exit;
    }

    $stmt->bind_param("i", $user_id);
    $stmt->execute();

    $result = $stmt->get_result();

    $transactions = array();
    while ($row = $result->fetch_assoc()) {
        $transactions[] = $row;
    }

    echo json_encode($transactions);

    $stmt->close();
    $conn->close();

} else {
    http_response_code(405);
    echo json_encode(array("message" => "Method not allowed. Use GET."));
}
?>
```

**Explanation:**

*   Similar to `add_transaction.php`, but retrieves transactions from the database.
*   Uses a `GET` request.
*   Requires a `user_id` in the query string (e.g., `get_transactions.php?user_id=1`).
*   Fetches the results into an array `$transactions` and encodes it as JSON.

**Example of other API endpoints:**

*   `get_categories.php`: Retrieve categories for a user.
*   `add_category.php`: Add a new category.
*   `update_transaction.php`: Update an existing transaction.
*   `delete_transaction.php`: Delete a transaction.
*   `register.php`: Register a new user.
*   `login.php`: Authenticate a user.

**3. Front-end (JavaScript, HTML, CSS)**

Create the following directory structure.

```
finance_tracker/
??? api/
?   ??? db_connect.php
?   ??? add_transaction.php
?   ??? get_transactions.php
?   ??? ... other API endpoints ...
??? public/
    ??? index.html
    ??? style.css
    ??? script.js
??? ... other files ...
```

**`public/index.html` (HTML Structure):**

```html
<!DOCTYPE html>
<html>
<head>
    <title>Personal Finance Tracker</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <h1>Personal Finance Tracker</h1>

    <div id="transaction-form">
        <h2>Add Transaction</h2>
        <form id="add-transaction-form">
            <label for="user_id">User ID:</label>
            <input type="number" id="user_id" name="user_id" required><br><br>

            <label for="category_id">Category ID:</label>
            <input type="number" id="category_id" name="category_id"><br><br>

            <label for="transaction_date">Date:</label>
            <input type="date" id="transaction_date" name="transaction_date" required><br><br>

            <label for="description">Description:</label>
            <input type="text" id="description" name="description"><br><br>

            <label for="amount">Amount:</label>
            <input type="number" step="0.01" id="amount" name="amount" required><br><br>

            <label for="type">Type:</label>
            <select id="type" name="type" required>
                <option value="income">Income</option>
                <option value="expense">Expense</option>
            </select><br><br>

            <button type="submit">Add Transaction</button>
        </form>
    </div>

    <div id="transaction-list">
        <h2>Transactions</h2>
        <ul id="transactions">
            <!-- Transactions will be displayed here -->
        </ul>
    </div>

    <script src="script.js"></script>
</body>
</html>
```

**`public/script.js` (JavaScript):**

```javascript
document.addEventListener('DOMContentLoaded', function() {
    const transactionForm = document.getElementById('add-transaction-form');
    const transactionsList = document.getElementById('transactions');

    // Function to fetch and display transactions
    function fetchTransactions(userId) {
        fetch(`api/get_transactions.php?user_id=${userId}`)  // Replace with your API endpoint
            .then(response => response.json())
            .then(data => {
                transactionsList.innerHTML = ''; // Clear existing list
                data.forEach(transaction => {
                    const listItem = document.createElement('li');
                    listItem.textContent = `${transaction.transaction_date} - ${transaction.description} - ${transaction.amount} (${transaction.type})`;
                    transactionsList.appendChild(listItem);
                });
            })
            .catch(error => console.error('Error fetching transactions:', error));
    }

    // Handle form submission
    transactionForm.addEventListener('submit', function(event) {
        event.preventDefault(); // Prevent default form submission

        const formData = new FormData(transactionForm); // Create FormData object

        fetch('api/add_transaction.php', { // Replace with your API endpoint
            method: 'POST',
            body: formData
        })
        .then(response => response.json())
        .then(data => {
            alert(data.message); // Show success/error message
            const userId = document.getElementById('user_id').value;
            fetchTransactions(userId); // Refresh transaction list
            transactionForm.reset(); // Clear the form
        })
        .catch(error => console.error('Error adding transaction:', error));
    });

    // Initial load of transactions (example: for user ID 1)
    fetchTransactions(1); // Load transactions for user 1 initially
});
```

**Explanation:**

*   `DOMContentLoaded`:  Ensures the script runs after the HTML is fully loaded.
*   `fetch()`:  Used to make HTTP requests to the PHP back-end.
*   `FormData`:  A convenient way to collect form data for sending in a request.
*   `response.json()`: Parses the JSON response from the server.
*   `transactionsList.innerHTML = '';`: Clears the existing transaction list before displaying new data.
*   The code iterates through the received transaction data and creates list items (`<li>`) to display each transaction.
*   Event Listener: The form has an event listener. On submit, fetch is called with 'api/add_transaction.php' and method: 'POST'.
*   `transactionForm.reset();`: After submitting, the form is cleared

**`public/style.css` (CSS Styling - Optional):**

```css
body {
    font-family: sans-serif;
    margin: 20px;
}

#transaction-form {
    margin-bottom: 20px;
    border: 1px solid #ccc;
    padding: 10px;
}

#transaction-list ul {
    list-style-type: none;
    padding: 0;
}

#transaction-list li {
    border-bottom: 1px solid #eee;
    padding: 5px 0;
}
```

**Important Considerations and Next Steps:**

*   **Security:** This is a very basic example and lacks proper security measures.  **You must implement robust security practices in a real-world application:**
    *   **Password Hashing:** Use `password_hash()` in PHP to securely store passwords. Never store plain text passwords.
    *   **Input Validation and Sanitization:**  Thoroughly validate and sanitize all user input to prevent SQL injection, XSS attacks, and other vulnerabilities.  Use functions like `htmlspecialchars()`, `filter_var()`, and prepared statements.
    *   **Authentication and Authorization:** Implement a proper authentication system to verify user identities and authorization to control access to resources.  Use sessions or JWT (JSON Web Tokens).
    *   **CSRF Protection:** Protect against Cross-Site Request Forgery (CSRF) attacks.
*   **Error Handling:** Improve error handling on both the front-end and back-end.  Log errors for debugging.
*   **User Interface (UI):**  This example has a very basic UI.  Use CSS to style the application and make it more user-friendly.  Consider using a JavaScript framework like React, Angular, or Vue.js for a more complex UI.
*   **Database Design:** As your application grows, you might need to normalize your database schema further.
*   **API Design:**  Design your API endpoints in a RESTful manner.
*   **Deployment:**  Consider how you will deploy your application to a web server.
*   **Testing:** Write unit tests and integration tests to ensure the code works correctly.
*   **Session management**: You should implement a login functionality using PHP sessions so the application can store user data while using it.

**How to Run This Example:**

1.  **Set up a web server (e.g., Apache or Nginx) with PHP and MySQL.**
2.  **Create the `finance_tracker` database in MySQL.**
3.  **Create the tables using the SQL script.**
4.  **Configure the `db_connect.php` file with your database credentials.**
5.  **Place the PHP files in a directory accessible by your web server (e.g., `/var/www/html/finance_tracker/api`).**
6.  **Place the HTML, CSS, and JavaScript files in a publicly accessible directory (e.g., `/var/www/html/finance_tracker/public`).**
7.  **Access the `index.html` file in your web browser (e.g., `http://localhost/finance_tracker/public/index.html`).**

This example provides a basic foundation for building a personal finance tracker. Remember to prioritize security and follow best practices as you develop the application further.