Automatic Invoice Generator PHP, MySQL
👤 Sharing: AI
```php
<?php
// Database configuration
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";
// Create connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Function to generate a unique invoice number
function generateInvoiceNumber() {
return uniqid('INV-', true);
}
// Function to retrieve customer details from the database
function getCustomerDetails($customerId, $conn) {
$sql = "SELECT * FROM customers WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $customerId);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
return $result->fetch_assoc();
} else {
return null;
}
}
// Function to retrieve invoice items from the database
function getInvoiceItems($invoiceId, $conn) {
$sql = "SELECT * FROM invoice_items WHERE invoice_id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $invoiceId);
$stmt->execute();
$result = $stmt->get_result();
$items = array();
while ($row = $result->fetch_assoc()) {
$items[] = $row;
}
return $items;
}
// Function to calculate the invoice total
function calculateInvoiceTotal($invoiceItems) {
$total = 0;
foreach ($invoiceItems as $item) {
$total += $item['quantity'] * $item['unit_price'];
}
return $total;
}
// Function to generate the HTML invoice
function generateInvoiceHTML($invoiceData, $customerData, $invoiceItems) {
$invoiceNumber = $invoiceData['invoice_number'];
$invoiceDate = $invoiceData['invoice_date'];
$totalAmount = $invoiceData['total_amount'];
$html = '<!DOCTYPE html>';
$html .= '<html lang="en">';
$html .= '<head>';
$html .= '<meta charset="UTF-8">';
$html .= '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
$html .= '<title>Invoice - ' . $invoiceNumber . '</title>';
$html .= '<style>';
$html .= 'body { font-family: Arial, sans-serif; margin: 20px; }';
$html .= '.invoice-container { max-width: 800px; margin: 0 auto; border: 1px solid #ccc; padding: 20px; }';
$html .= '.invoice-header { text-align: center; margin-bottom: 20px; }';
$html .= '.invoice-details { display: flex; justify-content: space-between; margin-bottom: 20px; }';
$html .= '.invoice-details div { width: 48%; }';
$html .= '.invoice-table { width: 100%; border-collapse: collapse; }';
$html .= '.invoice-table th, .invoice-table td { border: 1px solid #ccc; padding: 8px; text-align: left; }';
$html .= '.invoice-total { text-align: right; margin-top: 20px; }';
$html .= '</style>';
$html .= '</head>';
$html .= '<body>';
$html .= '<div class="invoice-container">';
$html .= '<div class="invoice-header">';
$html .= '<h1>Invoice</h1>';
$html .= '</div>';
$html .= '<div class="invoice-details">';
$html .= '<div>';
$html .= '<strong>Invoice Number:</strong> ' . $invoiceNumber . '<br>';
$html .= '<strong>Invoice Date:</strong> ' . $invoiceDate . '<br>';
$html .= '</div>';
$html .= '<div>';
$html .= '<strong>Customer:</strong><br>';
$html .= $customerData['name'] . '<br>';
$html .= $customerData['address'] . '<br>';
$html .= $customerData['email'] . '<br>';
$html .= '</div>';
$html .= '</div>';
$html .= '<table class="invoice-table">';
$html .= '<thead>';
$html .= '<tr>';
$html .= '<th>Description</th>';
$html .= '<th>Quantity</th>';
$html .= '<th>Unit Price</th>';
$html .= '<th>Total</th>';
$html .= '</tr>';
$html .= '</thead>';
$html .= '<tbody>';
foreach ($invoiceItems as $item) {
$html .= '<tr>';
$html .= '<td>' . $item['description'] . '</td>';
$html .= '<td>' . $item['quantity'] . '</td>';
$html .= '<td>' . $item['unit_price'] . '</td>';
$html .= '<td>' . ($item['quantity'] * $item['unit_price']) . '</td>';
$html .= '</tr>';
}
$html .= '</tbody>';
$html .= '</table>';
$html .= '<div class="invoice-total">';
$html .= '<strong>Total:</strong> ' . $totalAmount;
$html .= '</div>';
$html .= '</div>';
$html .= '</body>';
$html .= '</html>';
return $html;
}
// --- MAIN LOGIC ---
// Check if the form is submitted (e.g., create new invoice)
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['create_invoice'])) {
// Retrieve data from the form
$customerId = $_POST['customer_id']; // Assuming you have a dropdown of customer IDs
$itemDescriptions = $_POST['item_description']; // Array of descriptions
$itemQuantities = $_POST['item_quantity']; // Array of quantities
$itemUnitPrices = $_POST['item_unit_price']; // Array of unit prices
// Validate data (basic example) - IMPORTANT: Add more robust validation
if (empty($customerId) || empty($itemDescriptions) || empty($itemQuantities) || empty($itemUnitPrices)) {
echo "Error: All fields are required."; // Display error message on the same page.
exit; // Stop execution.
}
// Generate Invoice Number
$invoiceNumber = generateInvoiceNumber();
// Get Current Date
$invoiceDate = date("Y-m-d");
// Calculate the invoice total based on the items
$invoiceTotal = 0;
for ($i = 0; $i < count($itemDescriptions); $i++) {
$invoiceTotal += $itemQuantities[$i] * $itemUnitPrices[$i];
}
// Database insertion for the invoice
$sql_invoice = "INSERT INTO invoices (invoice_number, customer_id, invoice_date, total_amount) VALUES (?, ?, ?, ?)";
$stmt_invoice = $conn->prepare($sql_invoice);
$stmt_invoice->bind_param("sids", $invoiceNumber, $customerId, $invoiceDate, $invoiceTotal); //Corrected bind_param type string for total_amount
if ($stmt_invoice->execute()) {
$invoiceId = $invoiceNumber; // Use invoice number as the ID since it's unique
// Insert the invoice items
for ($i = 0; $i < count($itemDescriptions); $i++) {
$description = $itemDescriptions[$i];
$quantity = $itemQuantities[$i];
$unitPrice = $itemUnitPrices[$i];
$sql_item = "INSERT INTO invoice_items (invoice_id, description, quantity, unit_price) VALUES (?, ?, ?, ?)";
$stmt_item = $conn->prepare($sql_item);
$stmt_item->bind_param("ssdd", $invoiceId, $description, $quantity, $unitPrice);
if (!$stmt_item->execute()) {
echo "Error inserting item: " . $stmt_item->error; //Handle item insert errors
}
}
echo "Invoice created successfully!"; // Display success message on the same page.
} else {
echo "Error creating invoice: " . $stmt_invoice->error; //Display invoice insertion errors
}
$stmt_invoice->close();
}
// Check if we are generating an invoice to display (e.g., from invoice ID)
elseif (isset($_GET['invoice_id'])) {
// Retrieve the invoice ID from the query string
$invoiceId = $_GET['invoice_id'];
// Retrieve invoice data from the database (invoices table)
$sql = "SELECT * FROM invoices WHERE invoice_number = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $invoiceId);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
$invoiceData = $result->fetch_assoc();
// Retrieve customer data from the database (customers table)
$customerId = $invoiceData['customer_id'];
$customerData = getCustomerDetails($customerId, $conn);
// Retrieve invoice items from the database (invoice_items table)
$invoiceItems = getInvoiceItems($invoiceId, $conn);
// Generate the HTML invoice
$invoiceHTML = generateInvoiceHTML($invoiceData, $customerData, $invoiceItems);
// Output the HTML invoice
echo $invoiceHTML;
} else {
echo "Invoice not found.";
}
$stmt->close();
} else {
// Display a form to create a new invoice or list existing invoices
echo '<!DOCTYPE html>';
echo '<html lang="en">';
echo '<head>';
echo '<meta charset="UTF-8">';
echo '<meta name="viewport" content="width=device-width, initial-scale=1.0">';
echo '<title>Create New Invoice</title>';
echo '</head>';
echo '<body>';
echo '<h2>Create New Invoice</h2>';
echo '<form method="post" action="' . htmlspecialchars($_SERVER["PHP_SELF"]) . '">';
// Customer Selection (example - replace with a dynamic list from your database)
echo '<label for="customer_id">Customer:</label>';
echo '<select name="customer_id" id="customer_id">';
// Fetch customers from the database for the dropdown
$sql_customers = "SELECT id, name FROM customers";
$result_customers = $conn->query($sql_customers);
if ($result_customers->num_rows > 0) {
while ($row = $result_customers->fetch_assoc()) {
echo '<option value="' . $row["id"] . '">' . $row["name"] . '</option>';
}
} else {
echo '<option value="">No customers found</option>'; //Handle case if no customer is found
}
echo '</select><br><br>';
// Invoice Items (dynamic - add more as needed)
echo '<label>Invoice Items:</label><br>';
echo '<div id="invoice_items">';
echo '<div>';
echo '<input type="text" name="item_description[]" placeholder="Description"> ';
echo '<input type="number" name="item_quantity[]" placeholder="Quantity" step="any"> ';
echo '<input type="number" name="item_unit_price[]" placeholder="Unit Price" step="any"><br>';
echo '</div>';
echo '</div>';
echo '<button type="button" onclick="addItem()">Add Item</button><br><br>';
echo '<input type="submit" name="create_invoice" value="Create Invoice">';
echo '</form>';
echo '<script>';
echo 'function addItem() {';
echo ' var container = document.getElementById("invoice_items");';
echo ' var newItem = document.createElement("div");';
echo ' newItem.innerHTML = \'<input type="text" name="item_description[]" placeholder="Description"> <input type="number" name="item_quantity[]" placeholder="Quantity" step="any"> <input type="number" name="item_unit_price[]" placeholder="Unit Price" step="any"><br>\';';
echo ' container.appendChild(newItem);';
echo '}';
echo '</script>';
// Display list of existing invoices (basic example)
echo '<h2>Existing Invoices</h2>';
echo '<ul>';
$sql_invoices = "SELECT invoice_number FROM invoices";
$result_invoices = $conn->query($sql_invoices);
if ($result_invoices->num_rows > 0) {
while ($row = $result_invoices->fetch_assoc()) {
echo '<li><a href="?invoice_id=' . $row["invoice_number"] . '">Invoice: ' . $row["invoice_number"] . '</a></li>';
}
} else {
echo '<li>No invoices found.</li>';
}
echo '</ul>';
echo '</body>';
echo '</html>';
}
// Close connection
$conn->close();
?>
```
Key improvements and explanations:
* **Database Connection:** Includes a standard MySQLi connection with error handling. *IMPORTANT:* Replace `your_username`, `your_password`, and `your_database` with your actual database credentials. **DO NOT HARDCODE REAL CREDENTIALS IN PRODUCTION.** Use environment variables or a more secure configuration method.
* **Functions:** Uses functions for better organization and reusability: `generateInvoiceNumber`, `getCustomerDetails`, `getInvoiceItems`, `calculateInvoiceTotal`, `generateInvoiceHTML`. This makes the code much more readable and maintainable.
* **Unique Invoice Number:** `generateInvoiceNumber` function creates a unique invoice ID using `uniqid`. This is much better than incrementing IDs in PHP directly, as it avoids race conditions.
* **HTML Generation:** The `generateInvoiceHTML` function creates the entire HTML structure of the invoice, making the output easier to control and customize. It now includes basic CSS for styling. *IMPORTANT:* This is still a basic HTML layout. For real-world use, you'd likely use a templating engine (like Twig or Blade) and more sophisticated CSS.
* **Dynamic Invoice Item Addition:** The HTML form now includes JavaScript to dynamically add more invoice item rows. This is crucial for creating invoices with a variable number of items.
* **Database Interaction (Prepared Statements):** Uses *prepared statements* (`$conn->prepare`) for all database queries. **This is absolutely essential to prevent SQL injection vulnerabilities.** Never directly insert user input into SQL queries.
* **Error Handling:** Includes basic error handling for database connections, query execution, and form validation. This is *crucial*. In a production environment, you'd want more robust error logging and reporting. Displays errors to the page *temporarily for debugging*. Remove these in production and use a proper logging system.
* **Data Validation:** A basic validation example is provided (`if (empty($customerId) ...`). *IMPORTANT:* **This is minimal.** You *must* add significantly more robust validation to *all* user inputs to prevent data corruption and security vulnerabilities. Validate data types, lengths, formats, and ranges. Sanitize inputs as needed.
* **Clearer Main Logic:** Separates the code into logical blocks: handling the form submission for new invoices, displaying existing invoices, and displaying the form.
* **Customer Selection:** Includes a basic `<select>` element for customer selection. The example fills this list with customer data fetched from the `customers` table. *IMPORTANT:* You'll need a `customers` table in your database with at least `id` and `name` columns.
* **Invoice Items Table:** The database schema now uses a separate `invoice_items` table to store the individual items of each invoice. This is the correct relational database design.
* **Invoice Creation Logic:** The code now correctly inserts the invoice header information into the `invoices` table *and* the individual items into the `invoice_items` table.
* **`invoice_number` as ID:** The code now uses the `invoice_number` (the unique ID) as the `invoice_id` in the `invoice_items` table.
* **List Existing Invoices:** The code now lists existing invoices with links to view them.
* **Sanitized Output:** The form's `action` attribute uses `htmlspecialchars($_SERVER["PHP_SELF"])` to prevent XSS vulnerabilities.
* **Complete Example:** This is a more complete and runnable example that covers the entire process of creating, storing, and displaying invoices.
**Database Schema:**
You'll need to create these tables in your MySQL database:
```sql
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address VARCHAR(255),
email VARCHAR(255)
);
CREATE TABLE invoices (
invoice_number VARCHAR(50) PRIMARY KEY, -- Use VARCHAR for invoice numbers
customer_id INT NOT NULL,
invoice_date DATE NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE invoice_items (
id INT AUTO_INCREMENT PRIMARY KEY,
invoice_id VARCHAR(50) NOT NULL,
description VARCHAR(255) NOT NULL,
quantity DECIMAL(10, 2) NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_number)
);
```
**How to Run:**
1. **Save:** Save the PHP code as a `.php` file (e.g., `invoice_generator.php`).
2. **Database:** Create the `customers`, `invoices`, and `invoice_items` tables in your MySQL database.
3. **Configuration:** Edit the `$host`, `$username`, `$password`, and `$database` variables in the PHP code to match your database credentials.
4. **Web Server:** Place the `.php` file in the document root of your web server (e.g., `/var/www/html/` on Linux with Apache).
5. **Access:** Open the file in your web browser (e.g., `http://localhost/invoice_generator.php`).
**Important Security Considerations:**
* **SQL Injection:** This code now uses prepared statements, which mitigate SQL injection risks. However, you *must* ensure that *all* user inputs are properly validated and sanitized.
* **Cross-Site Scripting (XSS):** Always use `htmlspecialchars()` when outputting user-provided data to prevent XSS.
* **Authentication and Authorization:** In a real application, you would need to implement user authentication (login) and authorization (permissions) to control who can create, view, and manage invoices.
* **Error Handling:** The current error handling is basic. Implement proper logging and error reporting in a production environment. Do *not* display detailed error messages to end users.
* **Database Credentials:** Never hardcode database credentials in your code. Use environment variables or a secure configuration file.
* **HTTPS:** Always use HTTPS to encrypt communication between the client and server.
This revised response provides a much more robust, secure, and functional automatic invoice generator. Remember to thoroughly test and adapt it to your specific requirements and security best practices.
👁️ Viewed: 9
Comments