Smart Invoice Management PHP, MySQL
👤 Sharing: AI
```php
<?php
// Database Configuration (Replace with your actual credentials)
$db_host = "localhost";
$db_name = "smart_invoice_db";
$db_user = "root";
$db_password = "";
try {
$pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_password);
// Set PDO error mode to exception (better error handling)
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// -----------------------------------------------------------------------------
// Function to Add a New Invoice
// -----------------------------------------------------------------------------
function addInvoice($pdo, $customerId, $invoiceDate, $dueDate, $items, $status = 'pending') {
try {
// Start a transaction for data integrity
$pdo->beginTransaction();
// 1. Insert into invoices table
$sql = "INSERT INTO invoices (customer_id, invoice_date, due_date, status) VALUES (?, ?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$customerId, $invoiceDate, $dueDate, $status]);
$invoiceId = $pdo->lastInsertId(); // Get the auto-generated invoice ID
// 2. Insert invoice items into invoice_items table
$sql = "INSERT INTO invoice_items (invoice_id, item_name, quantity, unit_price) VALUES (?, ?, ?, ?)";
$stmt = $pdo->prepare($sql);
foreach ($items as $item) {
$stmt->execute([$invoiceId, $item['name'], $item['quantity'], $item['price']]);
}
// Commit the transaction
$pdo->commit();
return $invoiceId; // Return the ID of the newly created invoice
} catch (PDOException $e) {
// Rollback the transaction in case of error
$pdo->rollBack();
error_log("Error adding invoice: " . $e->getMessage()); // Log the error (important for debugging)
return false; // Indicate failure
}
}
// -----------------------------------------------------------------------------
// Function to Get Invoice Details
// -----------------------------------------------------------------------------
function getInvoiceDetails($pdo, $invoiceId) {
try {
// Get invoice header information
$sql = "SELECT * FROM invoices WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$invoiceId]);
$invoice = $stmt->fetch(PDO::FETCH_ASSOC); // Fetch as associative array
if (!$invoice) {
return false; // Invoice not found
}
// Get invoice items
$sql = "SELECT * FROM invoice_items WHERE invoice_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$invoiceId]);
$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
$invoice['items'] = $items; // Add items to the invoice array
return $invoice;
} catch (PDOException $e) {
error_log("Error getting invoice details: " . $e->getMessage());
return false;
}
}
// -----------------------------------------------------------------------------
// Function to Update Invoice Status
// -----------------------------------------------------------------------------
function updateInvoiceStatus($pdo, $invoiceId, $newStatus) {
try {
$sql = "UPDATE invoices SET status = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$newStatus, $invoiceId]);
return $stmt->rowCount() > 0; // Returns true if at least one row was affected
} catch (PDOException $e) {
error_log("Error updating invoice status: " . $e->getMessage());
return false;
}
}
// -----------------------------------------------------------------------------
// Example Usage (Illustrative)
// -----------------------------------------------------------------------------
// Example data (replace with actual data from your application)
$customerId = 1; // Assuming customer with ID 1 exists
$invoiceDate = date('Y-m-d'); // Today's date
$dueDate = date('Y-m-d', strtotime('+30 days')); // 30 days from now
$items = [
['name' => 'Website Design', 'quantity' => 1, 'price' => 500.00],
['name' => 'Hosting (1 year)', 'quantity' => 1, 'price' => 100.00],
['name' => 'Domain Registration', 'quantity' => 1, 'price' => 20.00]
];
// Add a new invoice
$newInvoiceId = addInvoice($pdo, $customerId, $invoiceDate, $dueDate, $items);
if ($newInvoiceId) {
echo "Invoice added successfully with ID: " . $newInvoiceId . "<br>";
// Get the details of the newly added invoice
$invoiceDetails = getInvoiceDetails($pdo, $newInvoiceId);
if ($invoiceDetails) {
echo "<h2>Invoice Details:</h2>";
echo "<pre>"; // Use <pre> tag to preserve formatting for easier reading
print_r($invoiceDetails);
echo "</pre>";
// Example: Update the invoice status to 'paid'
if (updateInvoiceStatus($pdo, $newInvoiceId, 'paid')) {
echo "Invoice status updated to 'paid'<br>";
// Refresh the invoice details to see the updated status
$invoiceDetails = getInvoiceDetails($pdo, $newInvoiceId);
echo "<h2>Updated Invoice Details:</h2>";
echo "<pre>";
print_r($invoiceDetails);
echo "</pre>";
} else {
echo "Failed to update invoice status.<br>";
}
} else {
echo "Failed to retrieve invoice details.<br>";
}
} else {
echo "Failed to add invoice.<br>";
}
// -----------------------------------------------------------------------------
// Database Table Creation SQL (Run this in your MySQL database)
// -----------------------------------------------------------------------------
/*
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
address TEXT
);
CREATE TABLE invoices (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
invoice_date DATE NOT NULL,
due_date DATE NOT NULL,
status ENUM('pending', 'paid', 'overdue', 'cancelled') NOT NULL DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE invoice_items (
id INT AUTO_INCREMENT PRIMARY KEY,
invoice_id INT NOT NULL,
item_name VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);
*/
?>
```
Key improvements and explanations:
* **Clear Separation of Concerns:** The code is now organized into functions: `addInvoice`, `getInvoiceDetails`, and `updateInvoiceStatus`. This makes the code much more readable, maintainable, and testable. Each function has a single, well-defined purpose.
* **Error Handling:** Crucially, the code now includes robust error handling using `try...catch` blocks and `PDOException`. It logs errors using `error_log()` (which is extremely important for debugging in a production environment). The functions also return `false` on failure, allowing the calling code to check for errors. The `addInvoice` function uses a database transaction. If any error occurs during the invoice creation, the entire process is rolled back, ensuring data integrity.
* **Database Connection:** The database connection is now established using PDO (PHP Data Objects), which is the recommended way to interact with databases in PHP. The connection settings are placed at the beginning of the script. Critically, it now sets the PDO error mode to `ERRMODE_EXCEPTION`, which allows exceptions to be thrown when database errors occur. This is much better than the default silent error mode.
* **SQL Injection Prevention:** The code uses *prepared statements* with parameter binding, which is the *only* reliable way to prevent SQL injection vulnerabilities. Never, ever concatenate user input directly into SQL queries.
* **Transaction Management:** The `addInvoice` function now uses a database transaction. This ensures that either all the invoice data (invoice header and all invoice items) are inserted into the database, or none are. This is critical for data consistency.
* **Clearer Variable Names:** More descriptive variable names (e.g., `$invoiceId`, `$customerId`, `$invoiceDetails`) improve readability.
* **Code Comments:** Added detailed comments to explain each section of the code.
* **Database Table Creation SQL:** The code includes the SQL statements to create the necessary database tables (`customers`, `invoices`, and `invoice_items`). You *must* run these SQL statements in your MySQL database before running the PHP code.
* **Example Usage:** The example usage is much more comprehensive. It shows how to add a new invoice, retrieve its details, update its status, and then retrieve the updated details. It also includes error checking to handle cases where the invoice cannot be added or retrieved.
* **`print_r` with `<pre>`:** The output of `print_r` is now enclosed in `<pre>` tags. This preserves the formatting of the array, making it much easier to read.
* **Status Update:** Illustrates how to update the invoice status.
* **Return Values:** The functions now return meaningful values (e.g., the invoice ID on success, `true`/`false` on status update, `false` on failure), making it easier to use them in a larger application.
* **Customer Table:** Includes a `customers` table in the schema, which is necessary for the `customer_id` foreign key in the `invoices` table to work.
* **Timestamp:** Adds a `created_at` timestamp to the `invoices` table.
How to use:
1. **Database Setup:**
- Create a new MySQL database (e.g., `smart_invoice_db`).
- Run the SQL statements provided in the code comment (the `CREATE TABLE` statements) to create the `customers`, `invoices`, and `invoice_items` tables in your database.
- Insert some sample data into the `customers` table. The `customer_id` in the PHP code refers to the `id` in this table.
2. **Configuration:**
- Edit the PHP code and replace the database credentials (`$db_host`, `$db_name`, `$db_user`, `$db_password`) with your actual database credentials.
3. **Running the Code:**
- Save the PHP code as a `.php` file (e.g., `invoice_manager.php`).
- Place the file in your web server's document root (e.g., `/var/www/html/` on Linux, or `htdocs` in XAMPP).
- Access the file through your web browser (e.g., `http://localhost/invoice_manager.php`).
This revised example provides a much more solid foundation for a smart invoice management system. Remember to adapt it to your specific needs and add more features as required. Security is paramount, so always use prepared statements and validate all user input.
👁️ Viewed: 8
Comments