FREE MySQL EMAIL REPORT GENERATOR
Generate gorgeous HTML Emails with tables from MySQL Queries in PHP
with PDF and CSV attachments
A Simple MySQL Email Reporting Script
If you need to report on MYSQL data and are looking for a flexible low cost solution, this is it!
Generate emails from MySQL queries using this PHP script, a couple of simple functions that can be used to generate an email based off any mySQL query. These can then be scheduled using CRON or Windows Scheduler to run at a certain time of day. Perfect for sales reports, daily, weekly or monthly management reports and plenty more!
“Excellent customer service! Very responsive. I purchased the mysql php email report generator and it is fantastic. It has saved me so much time and freed me up to concentrate on other aspects of my project. Thank you.”
“I setup the script and followed the instructions to tee and voila the script was in my inbox and my clients inbox and 8 AM this morning.”
“I just have to say that this plugin was painless to set up, works wonderfully, and is worth far more to my organization than what I paid.”
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 = 'person1@somedomain.com';
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', 'no-reply@mydomain.com'); //Define your standard from address for emails, can be no-reply@yourdomain.com 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!