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.
Update 14/11/2023 – The script is a bit long in the tooth at the moment and could well do with an update, if anyone finds the time to do this, please feel free to let me know and I can update this version. Add your credits to the zip.
Download
You can download the zip here. Last updated 2017.
Purpose
This script was created for the purposes of….
- Generating An Automated Report from a MySQL Query
- Creating A Simple Email Containing The Report
- Scheduling The Email and Report
Functionality
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
Requirements
Requirements
- PHP 5+
- MySQL
- Tested with Gmail, Outlook, Exchange Server, Apache & IIS
- Tested on many hosted web platforms
- Works with PHP Mail Function or SMTP
- Limitations
- Can generate a single PDF/CSV per email/query
- Tested with standard ISO-8859-1 Charset
- 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.
Installation
Upload the contents of the zip into a folder off your site’s root (for security you may want to later move this folder a directory above your public_html so it is not externally accessible).
For the purpose of these instructions I’m going to assume you have uploaded the contents of the zip file to a folder called reports off your sites root public html folder.
You can then access the report.php file by accessing… http://www.yourdomain.com/reports/report.php
You can now move onto configuration!
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.
Basic Usage
With even a rudimentary understanding of PHP you’ll be up and running with this script in no time.
Configure your Report Colour and Email Subject//Setup Your Report Email Subject
$subject = 'My Report';
//Color is the color report table, it can be set to be grey, green, blue or compatibility
$color = 'blue';
Set Your Query//The Database Query to Run - You can even use a stored procedure
$query = 'SELECT firstName AS "First Name", lastName AS "Last Name" FROM userinfo ORDER BY lastName DESC';
Brand Your Email With An Email Header and Footer
//Add a message to the header of the report, you can link images here too!
$header = 'Introduction. This is an optional report introduction';
//Add a message to the footer of the report, you can use plain text or html.
$footer = 'Report Generated by Jellyhound';
Generate Your Report//Generate the report - this is where the magic happens
$report = generateReport($query, $header, $footer, $color);
Select Your Recipient(s)//Setup One or Multiple Email Recipients
$recipient1 = '[email protected]';
And Send//Send the report
html_email($recipient1,$subject,$report);
Configuration
Setting up the script is a piece of cake! Just open up the config file and…
Set Your Report To Test Mode//TEST MODES
define('TEST_MODE', true); //Set to true when configuring, false when you want to go live
define('SMTP_TEST_MODE', false); // set to true to debug your smtp (only works when TEST_MODE is false and USE_SMTP is true)
Enter Your Database Details//DATABASE CONNECTION
define('DB_TYPE', 'mysql'); //database type
define('DB_SERVER', 'localhost'); //database server, normally 'localhost'
define('DB_USER', 'root'); ////database login name, enter the username
define('DB_PASS', ''); //database login password, enter the password
define('DB_DATABASE', ''); //enter the name of the database you want to connect to
Use SMTP or PHP’s inbuilt MAIL function//EMAIL CONNECTION
define('USE_SMTP', false); //set to true if you want to use manual SMTP or leave as is to use the PHP Mail() function
Configure Your Email Preferences//EMAIL PREFERENCES
define('FROM_EMAIL', '[email protected]'); //Define your standard from address for emails, can be [email protected] for example
define('FROM_NAME', 'SQL Report Generator'); //Define the 'from name' in the email
define('SEND_EMAIL_IF_NO_RESULTS', true); //If true, the report will repress sending an email if your query returns no results
define('NO_RESULTS_MESSAGE', 'There were no results today'); //If your report generates no results, what should your email say? Include HTML if you like!
Combining Reports
You can concatenate multiple HTML reports (multiple queries) into a single email (but not with PDF’s or CSV files)
//Generate multiple reports from four mysql queries with individual headers and the same footer and colour
$report1 = generateReport($query1, $header1, $footer1, $color);
$report2 = generateReport($query2, $header2, $footer1, $color);
$report3 = generateReport($query3, $header3, $footer1, $color);
$report4 = generateReport($query4, $header4, $footer1, $color);
//join the four reports into one final report
$finalReport = $report1.$report2.$report3.$report4;
//Send all the reports
html_email($recipient,$subject,$finalReport);
Troubleshooting
If you are experiencing problems, here are a few common things to check…
My script will not run
The most common reason for this is unescaped apostrophes and syntax errors in your report sql query, headers, footers or subject..Did you introduce any extra apostrophes or speech-marks? For example, if you set the subject of your report as follows, this will cause a problem…
$subject = 'Customer’s favourite purchases';
Why? Because the full text should be encompassed by the apostrophes (‘), by introducing the word “customer’s” you add an unexpected apostrophe and this can cause a problem. You can escape an apostrophe as follows to make this work.
$subject = 'Customer’s favourite purchases';
or use single apostrophes inside double speech-marks
$subject = "Customer’s favourite purchases";
The same goes for all the options you have changed both in the report and the config file. Double check your apostrophes! If all else fails, start again and test your script with each and every change you make to find the problem.
My script still doesn’t run / My script is missing
If you are uploading files to a remote host, have you actually uploaded your modified files? And are you trying to open the report in the correct location?
How do I test the script without sending an email?
Whilst in test mode no emails will be sent. You can enable test mode by setting TEST_MODE to true in config.php
My emails are not being sent
Make sure TEST_MODE is set to false in your config.php
There are two methods for mail, one is to use your servers email configuration (if it has one). This generally works fine from shared hosting, just ensure USE_SMTP is set to false in your config.php.
Debugging SMTP
Ensure TEST_MODE is set to false and USE_SMTP is set to true in your config.php, then set SMTP_TEST_MODE to true and run your report from a browser. The browser will output SMTP errors to your screen so you can see what is going wrong. Google those errors!
The database connection fails
Double check your database settings in config.php: are you 100% sure they are correct? Test using a MySQL GUI like HeidiSQL.
I do not get back any results in my report
Check your SQL query: is it valid? Try running it from the SQL command line or using a GUI like PHPMyAdmin or HeidiSQL to see if it returns the result you expect.
My report email contains rubbish or weird characters
I’ve seen this most in Outlook combined with Exchange Server which is far fussier than Gmail and other online email clients. You need to firstly ensure that the report output from your SQL query does not contain odd or invalid characters. Also remember the script is designed to support the standard ISO-8859-1 Charset. I’ve not tested it with other character sets or in different languages although I’ve been told by many that they have had no problems. Remember that you get six months support and a 14 day money back guarantee!