Member Avatar for Stephen_10

Hello,
I am pretty new to cron jobs, as i am sure you can tell.
So.... Let me tell you a little about my project. I am trying to create an invoice reminder system. What i have so far is a form that you fill in and upload an invoice, on submittion it logs the date to the Mysql. What i want now is a email to auto send after 30 days of submition as like a reminder of payment.

If anyone could help me out with this would be greatly appreciated.

Edited by pritaeas because: Moved to linux.
  • 3 Contributors
  • 6 Replies
  • 294 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by ShouldAt3

Dani AI

Generated

A compact, reliable workflow that follows the direction given by (schedule a daily run) and the scheduling hints from :

  1. Track reminders in the database so the same invoice isn’t emailed repeatedly (add columns such as reminder_sent and reminder_sent_at or a reminders_count).
  2. Create a small CLI script that: selects invoices submitted 30+ days ago and not yet reminded, sends the message, and marks the row as reminded. Use prepared statements (PDO) and log both successes and failures.
  3. Schedule the script to run daily with the system scheduler (cron or systemd timer). Call the script by absolute path and capture stdout/stderr to a log for troubleshooting.

Example schema change (run once):

ALTER TABLE invoices
  ADD COLUMN reminder_sent TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN reminder_sent_at DATETIME NULL,
  ADD COLUMN reminders_count INT NOT NULL DEFAULT 0;

A minimal PHP CLI example (run with the system PHP binary). Replace connection and field names to match the actual schema; replace the simple mail() with a proper SMTP library in production for deliverability.

<?php
// invoice_reminder.php  (run via CLI)
date_default_timezone_set('UTC');

$pdo = new PDO('mysql:host=127.0.0.1;dbname=your_db;charset=utf8mb4','dbuser','dbpass',[
  PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
]);

$stmt = $pdo->prepare("
  SELECT id, invoice_number, customer_email, submitted_at
  FROM invoices
  WHERE submitted_at <= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    AND (reminder_sent = 0 OR reminder_sent IS NULL)
");
$stmt->execute();

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $inv) {
  $to = $inv['customer_email'];
  $subject = 'Reminder: invoice '.$inv['invoice_number'];
  $body = "Invoice {$inv['invoice_number']} (submitted {$inv['submitted_at']}) is 30 days old.\nPlease arrange payment.";
  $headers = "From: billing@example.com\r\nReply-To: billing@example.com\r\n";

  if (mail($to, $subject, $body, $headers)) {
    $upd = $pdo->prepare("UPDATE invoices SET reminder_sent = 1, reminder_sent_at = NOW() WHERE id = ?");
    $upd->execute([$inv['id']]);
  } else {
    error_log('Failed to send reminder for invoice '.$inv['id'].' to '.$to);
  }
}

Operational notes and troubleshooting:

  • Test manually by inserting a test invoice with an older date and running the script from the CLI; check the log and database updates.
  • Prefer an authenticated SMTP library (PHPMailer, Symfony Mailer, etc.) rather than raw mail() for production.
  • Ensure the scheduler uses absolute paths to the PHP binary and script, and that the scheduler’s user has appropriate DB and file permissions.
  • Add idempotency (transaction/row-lock or a status flag) to avoid duplicates if the script is invoked concurrently.

Recommended Answers

All 6 Replies

Member Avatar for hericles

Run a CRON job every day that reads the database for all invoices that have a submission date of 30 days ago. Email out only those emails.
So, your cron job will look like this:
30 1 * * * /call-your-script

That will run at 1:30 every day - you can change the time to suit what you need.

Member Avatar for Stephen_10

Aww thats great! Thank you in the call your script what do i need to put? Little confused with what i need to add there?

Any help would be much appreciated

Member Avatar for hericles

That would be the script that accesses the database. Say for example it was a php file called getInvoices.php then you would have the full path to the script. This can also be a web URL e.g http://

Member Avatar for Stephen_10

Right ok, this is where i get stuck then as i have no idea how to start that :/

Do you have any examples i could use as a starting point to referance?

Member Avatar for hericles

I don't mean to sound like I'm brushing you off but examples of database queries are pretty common online. You should be easily able to find something in your preferred language that you can then adapt or extend to what you need.
Once you have the basic outline you can post back on here about any particular problems you have.

Member Avatar for ShouldAt3

Hi, here you have a description and a couple of examples of how to schedule a job with cron. You will need to make the script executable with the chmod +x SCRIPTNAME command.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.