How to Add Automated Monthly Billing to Your ISP Management System in PHP and MySQL
The ISP Management System from Codezips already handles customers and plans. This tutorial adds the one feature every real ISP system needs: automated monthly bill generation that runs for all active customers at once, prevents duplicate bills, and optionally sends email notifications — all in plain PHP and MySQL, no framework required.
If you downloaded the ISP Management System from Codezips, you already have the customer records, internet plan assignments, and a billing table. What you probably do not have is a way to generate bills for all customers automatically at the start of every month — without manually creating one bill at a time.
This tutorial builds that feature. By the end, an admin clicks one button and the system generates bills for every active customer based on their current plan price, skipping anyone who already has a bill for that month. The whole thing is a single PHP function you drop into your existing project.
How the Auto Billing Flow Works
Step 1 — Verify Your Database Tables
The billing feature needs two things from your existing ISP database: a customers table with a plan_id and status column, and a bills table to store generated bills. If your downloaded project does not have a bills table yet, run this SQL to create it:
SQL Run in phpMyAdmin if your project doesn’t have a bills table yet
-- Bills table for ISP Management System auto billing
-- Adjust column names to match your existing customers / internet_plans tables
CREATE TABLE IF NOT EXISTS `bills` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`customer_id` INT UNSIGNED NOT NULL,
`bill_month` DATE NOT NULL, -- First day of the billed month: 2026-05-01
`amount` DECIMAL(10,2) NOT NULL,
`due_date` DATE NOT NULL,
`status` ENUM('unpaid','paid','overdue') DEFAULT 'unpaid',
`ref` VARCHAR(20) NOT NULL UNIQUE,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- Composite UNIQUE prevents billing same customer twice in same month
UNIQUE KEY uq_customer_month (`customer_id`, `bill_month`),
CONSTRAINT fk_bill_cust FOREIGN KEY (`customer_id`)
REFERENCES `customers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Check your customers table has these columns (adapt names if different):
-- id, name, email, plan_id, status ('active'/'inactive'/'suspended')
-- Check your internet_plans table has: id, plan_name, priceStep 2 — The Core Auto-Billing Function
Create a new file billing/auto_bill.php. This function loops through every active customer, checks if they already have a bill for the current month, and inserts one if they do not.
PHP billing/auto_bill.php — the complete bulk billing function
<?php
require_once '../config/dbconnection.php';
/**
* Generate monthly bills for all active customers.
* Safe to run multiple times — skips customers who already have
* a bill for the target month (enforced by PHP check AND DB unique key).
*
* @param string $target_month Optional: 'Y-m-01' format. Defaults to current month.
* @return array ['created'=>int, 'skipped'=>int, 'errors'=>array]
*/
function generateMonthlyBills($target_month = null) {
global $conn;
// Default to the first day of the current month
if (!$target_month) {
$target_month = date('Y-m-01');
}
$due_date = date('Y-m-d', strtotime($target_month . ' +14 days')); // 14-day payment window
$created = 0; $skipped = 0; $errors = [];
// ── Fetch all ACTIVE customers with their plan price ───────────
$stmt = mysqli_prepare($conn,
"SELECT c.id, c.name, c.email, p.plan_name, p.price
FROM customers c
JOIN internet_plans p ON c.plan_id = p.id
WHERE c.status = 'active'
ORDER BY c.id ASC");
mysqli_stmt_execute($stmt);
$customers = mysqli_fetch_all(mysqli_stmt_get_result($stmt), MYSQLI_ASSOC);
foreach ($customers as $customer) {
// ── PHP-level duplicate check (fast, before hitting the DB with INSERT)
$chk = mysqli_prepare($conn,
"SELECT id FROM bills
WHERE customer_id = ? AND bill_month = ? LIMIT 1");
mysqli_stmt_bind_param($chk, 'is', $customer['id'], $target_month);
mysqli_stmt_execute($chk);
mysqli_stmt_store_result($chk);
if (mysqli_stmt_num_rows($chk) > 0) {
$skipped++; continue; // Already billed this month — skip
}
// ── Generate a unique bill reference: ISP-2026-05-0042
$ref = 'ISP-' . date('Y-m', strtotime($target_month))
. '-' . str_pad($customer['id'], 4, '0', STR_PAD_LEFT);
// ── Insert the bill record ─────────────────────────────────────
$ins = mysqli_prepare($conn,
"INSERT INTO bills (customer_id, bill_month, amount, due_date, status, ref)
VALUES (?, ?, ?, ?, 'unpaid', ?)");
mysqli_stmt_bind_param($ins, 'isdss',
$customer['id'], $target_month,
$customer['price'], $due_date, $ref);
if (mysqli_stmt_execute($ins)) {
$created++;
// Optional: send email notification here (see Step 5)
// sendBillingEmail($customer['email'], $customer['name'], $customer['price'], $due_date, $ref);
} else {
$errors[] = 'Failed for customer ID ' . $customer['id']
. ': ' . mysqli_error($conn);
}
}
return ['created' => $created, 'skipped' => $skipped, 'errors' => $errors];
}
?>Step 3 — The Admin “Generate Bills” Page
Create billing/generate_bills.php. This is the page the admin visits (or the button they click) to trigger billing. It shows a summary of what was created, skipped, and any errors.
PHP billing/generate_bills.php — admin trigger page
<?php
require_once '../auth/auth_check.php'; // Must be admin — protect this page
require_once 'auto_bill.php';
$result = null;
$confirmMonth = date('F Y'); // e.g. "May 2026"
if (isset($_POST['generate'])) {
// Optional: allow admin to bill a different month (e.g. regenerate last month)
$month = isset($_POST['bill_month'])
&& preg_match('/^\d{4}-\d{2}-01$/', $_POST['bill_month'])
? $_POST['bill_month'] : null;
$result = generateMonthlyBills($month);
$confirmMonth = $month ? date('F Y', strtotime($month)) : date('F Y');
}
?>
<!DOCTYPE html><html lang="en"><head>
<meta charset="UTF-8">
<title>Generate Monthly Bills</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
</head><body class="bg-light">
<div class="container mt-4">
<h4 class="mb-3">💰 Generate Monthly Bills</h4>
<?php if ($result): ?>
<!-- Results summary -->
<div class="alert alert-<?= empty($result['errors']) ? 'success' : 'warning' ?>">
<strong>Billing run complete for <?= htmlspecialchars($confirmMonth) ?>:</strong><br>
✅ <?= $result['created'] ?> bills created |
⏭ <?= $result['skipped'] ?> already billed (skipped)
<?php if ($result['errors']): ?>
<hr><strong>Errors:</strong>
<ul class="mb-0">
<?php foreach ($result['errors'] as $e): ?>
<li><?= htmlspecialchars($e) ?></li>
<?php endforeach ?>
</ul>
<?php endif ?>
</div>
<?php endif ?>
<div class="card shadow-sm">
<div class="card-body">
<p>This will generate bills for <strong>all active customers</strong> based
on their current plan price. Customers who already have a bill for the
selected month will be automatically skipped.</p>
<form method="POST">
<div class="mb-3">
<label class="form-label">Bill Month (defaults to current month)</label>
<input type="month" name="bill_month" class="form-control"
style="max-width:200px"
value="<?= date('Y-m') ?>">
<!-- Browser sends YYYY-MM; PHP converts to YYYY-MM-01 via regex -->
</div>
<button type="submit" name="generate" class="btn btn-primary"
onclick="return confirm('Generate bills for all active customers?')">
⚡ Generate Bills Now
</button>
<a href="../admin/billing.php" class="btn btn-outline-secondary ms-2">
View All Bills
</a>
</form>
</div>
</div>
</div></body></html>Step 4 — Duplicate Bill Prevention — Try the Simulator
The duplicate check is the most important part of this feature. Without it, running the billing twice in a month creates double charges for every customer. Enter a customer ID and month below to see exactly how the check logic works:
🔒 Duplicate Check Simulator — See How the Logic Works
Simulates: does a bill already exist for this customer in this month?
The PHP logic that produces this check:
PHP The duplicate check — runs before every INSERT
// Convert browser's "YYYY-MM" input to "YYYY-MM-01" for the DATE column
$target_month = $_POST['bill_month'] . '-01'; // "2026-05" → "2026-05-01"
// Check: does a bill already exist for this customer in this month?
$chk = mysqli_prepare($conn,
"SELECT id FROM bills
WHERE customer_id = ? AND bill_month = ? LIMIT 1");
mysqli_stmt_bind_param($chk, 'is', $customer_id, $target_month);
mysqli_stmt_execute($chk);
mysqli_stmt_store_result($chk);
if (mysqli_stmt_num_rows($chk) > 0) {
// Bill already exists — skip this customer, do NOT insert again
$skipped++;
continue; // Move to next customer in the loop
}
// No bill found — safe to insertStep 5 — Add Email Notification When Bills Are Generated
Uncomment the email line in auto_bill.php and add the PHPMailer function. This sends each customer a notification the moment their bill is created. See the PHPMailer tutorial in this series for the full sendBillingEmail() function setup.
PHP Add inside the foreach loop in auto_bill.php — after the INSERT succeeds
if (mysqli_stmt_execute($ins)) {
$created++;
// Send email notification (requires PHPMailer setup — see PHPMailer tutorial)
if ($customer['email']) {
$emailResult = sendBillingEmail(
$customer['email'],
$customer['name'],
$customer['price'],
$due_date,
$ref
);
if ($emailResult !== true) {
// Log email failure but don't treat as billing failure
error_log('Bill created but email failed for '
. $customer['email'] . ': ' . $emailResult);
}
}
// Optional: add small pause to stay within Gmail SMTP rate limits
usleep(150000); // 0.15 second pause per customer
}Step 6 — Schedule It Automatically with a Cron Job (Linux Hosting)
If your project runs on a Linux hosting server, you can schedule the billing to run automatically on the 1st of every month without any admin action at all.
First create a CLI-safe version of the billing script:
PHP billing/cron_generate_bills.php — runs via cron, not the browser
<?php
// This file is called by cron on the 1st of every month
// It is NOT accessible via browser (check SAPI to prevent web access)
if (php_sapi_name() !== 'cli') {
die('Access denied — run from command line only.');
}
require_once dirname(__FILE__) . '/../config/dbconnection.php';
require_once dirname(__FILE__) . '/auto_bill.php';
$result = generateMonthlyBills();
echo date('Y-m-d H:i:s') . ' | Bills created: ' . $result['created']
. ' | Skipped: ' . $result['skipped']
. ' | Errors: ' . count($result['errors']) . PHP_EOL;Then add this cron entry (run crontab -e on your server):
Cron job — runs on the 1st of every month at 6:00 AM
# Minute Hour Day Month Weekday Command
0 6 1 * * /usr/bin/php /var/www/html/your-project/billing/cron_generate_bills.php >> /var/log/isp_billing.log 2>&1/usr/bin/php.
Which Projects Can Use This Same Pattern?
Error Troubleshooter
Fix: Run
ALTER TABLE bills ADD UNIQUE KEY uq_customer_month (customer_id, bill_month); in phpMyAdmin. This prevents any duplicate insertion at the database level regardless of PHP logic. Also verify the bill_month column stores DATE type (not VARCHAR) and you are inserting ‘Y-m-01’ format — comparing ‘2026-05’ to ‘2026-05-01’ will not match.WHERE c.status = 1.Second cause: The plan_id in your customers table does not match any id in internet_plans — the JOIN returns no rows. Check with:
SELECT c.id, c.plan_id, p.id FROM customers c LEFT JOIN internet_plans p ON c.plan_id = p.id WHERE p.id IS NULL;php /path/to/cron_generate_bills.php in your server terminal. If it errors, fix the PHP issue before worrying about cron.Step 2: Check that the PHP binary path in cron matches what your server uses:
which php in terminal gives the correct path.Step 3: Check the log file you specified in the cron command — it captures any output including errors. If the log file is not being created, the cron is not running at all — verify with
crontab -l that the entry was saved correctly.Frequently Asked Questions
What if a customer changes their plan mid-month — do they get billed at the old or new rate?
With this implementation, they are billed at the rate of their current plan at the time the billing function runs. If billing runs on the 1st and they changed plan on the 15th, they will be billed at the new rate next month. To bill at the rate that was active at the start of the month, you would need a plan_history table that records plan changes with timestamps — this is how professional billing systems work but is beyond beginner scope. For student projects, the simple approach (current plan at billing time) is completely acceptable.
Can I run the billing function from the dashboard homepage instead of a separate page?
Yes — add a card to your dashboard with a quick “Generate Bills” button that posts to the same page. Use a POST form rather than a GET link so the action does not trigger accidentally when someone shares a link or the browser auto-refreshes. The function is the same — just include auto_bill.php at the top of your dashboard and call generateMonthlyBills() when the form is submitted.
How do I add this to my portfolio and explain it in a job interview?
Say: “I extended an open-source ISP management system by building an automated billing module. On clicking a button, the system queries all active customers, joins their plan prices, checks for existing bills to prevent duplicates using both a PHP EXISTS check and a MySQL composite unique key, and inserts new bill records for any customer not yet billed that month. I also added an optional email notification via PHPMailer that fires after each successful insert.” Then point to the GitHub repo and the live demo. This is a complete, specific, technically sound explanation that demonstrates JOIN queries, duplicate prevention, and a real-world business logic concern — all things interviewers want to see.
The base project this tutorial extends
Set up the billing email notifications (Step 5)
Add PDF invoice download to each generated bill
Visualise billing revenue on your admin dashboard
Last updated April 2026. Tested on PHP 8.2 / MySQL 8.0 / XAMPP. Duplicate prevention logic verified against UNIQUE KEY constraint behaviour in MySQL InnoDB.
