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 = 'firstname.lastname@example.org';
//Send the report html_email($recipient1,$subject,$report);
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@example.com'); //Define your standard from address for emails, can be firstname.lastname@example.org 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!
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);
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.
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!