Generate scheduled email reports from a MySQL database.
If like me you were looking for a very simple script for generating MySQL Reports for clients or even for use within a larger system then you’ve found an easy, well documented script to implement. I searched around and couldn’t find any decent scripts out there for this purpose so I wrote this simple framework for generating and emailing MySQL reports such that customers can have easily scheduled reports run from CRON jobs. If you’ve no idea what I’m talking about but fairly sure this is just the thing you are after then please do read on. This script is easy to understand and well tested to boot.
The script will let you easily do the following :-
Use any MySQL Query
Generate a report table showing the fields of your choice
Customise the From Email Address
Customise the From Name
Customise the Email Subject (based on report name)
Customise the Report Name
Supports ISO-8859-1 Charset
Send to Multiple Recipients
MySQL Report Generator
The framework is comprised of 3 files….
config.php – Configure MySQL database settings, from address and from name.
sqlreporter.php – No need to touch this, contains all the code required to generate the report and email it.
report.php – Can be renamed and used ‘per report’. This file contains the MySQL query, the report name and the target recipient(s). I recommend renaming the file to represent the name of the report you are running, for example, user_access_report.php.
instructions.pdf – A document explaining how to setup your script and schedule your jobs using CRON.
With even a rudimentary understanding of PHP you’ll be up and running with this script in no time. The two keyfunctions required are…
generateReport( $reportName, $query, $fieldArray, $note);
//generateReport create a report based on...
//$reportName - The name of your report
//$query - a SQL query
//$fieldArray - the headers / titles for your report
//$note - your report footer or note
html_email( $recipient, $reportName, $report);
//html_email generates an HTML based email containing
// your report and sends it to the recipient
That’s it, it really is that simple and full examples are provided. I’m also on hand to help implement or answer any questions you may have about using the script.
Schedule your MySQL Report Email
This file can then be scheduled to run in almost any hosting package. Most Linux or Windows hosting packages give you an option to setup a CRON job in the control panel. Once the files are uploaded you simply create a CRON job and point the job at the report.php script (or whatever you decide to call it). This allows you to schedule the report to run at whatever time of the day, week, month or year suits.
I found a few expensive, over the top examples for this functionality. I designed this simple framework to provide developers with something robust to work with which can be manipulated to suit your purposes with some easy tweaks if need be.
Anyone encountering any issues using the script please comment below and I’ll respond within 48 hours at the most (usually the same day). I have thoroughly tested the script under PHP5 and MYSQL 5.