+44 (0)131 2081800
info@julian-young.com
info@julian-young.com
Independent Consultant for Online Design, Development and Marketing in Edinburgh, Scotland
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.
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
Demo
Visit the MySQL PHP Email Reporter mini page to demo the 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
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.
Basic Usage
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.
Visit the MySQL PHP Email Reporter mini page to try or buy.
76 Responses to PHP MySQL Email Report Generator
Leave a Reply
My Favourite Resources
I’ve had a number of emails asking how much PHP knowledge is required to implement this. The answer is very little.
I’m also happy to provide guidance either by email or Skype if you get stuck.
Alternatively I will fully implement the script for you (provided you give me the SQL and target email addresses) for £30 / $50.
I agree, very little knowledge is needed. I had it up and running in minutes. A nice added benefit for my clients, saved me from having to manually do their reports. I need more things like this!
Excellent script followed by excellent assistance. Very pleased, saved me hours! I FULLY recommend Julian’s work.
Thanks Ian!
Just Purchased the script but I have not received any emails indicating how to download it. Hmmmmm I hope to get an email soon.
Sorry Julian.. I was to quick to comment.. :-). It was a delay with Paypal and not Julian! :-)
Look forward to using this tool. Thanks for you QUICK response to the original comment.
Cheers,
RR
So I thought i would follow up with a comment on the script.
First off this is a steal!! Get it while you can at this price! :-)
I implemented this last night as I need to get out a daily report that my client needed desperately and the needed every morning.. So 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 did have a question for Julian about the cron setup but he had that answered in under 25 minutes from me asking! WOW!
Can’t really ask for much more than that.
Thanks Julian as you really saved my bacon with my client as I over promised and couldn’t deliver until i found your website.. The world works in strange ways sometimes!! :-)
Cheers,
RR
Thanks so much for the follow up Ryan! :) I was in a similar position when I wrote this script!
This is actually better than I expected, Julian sent me the latest version with multiple colours and a simpler set of parameters. The script saved me a couple of days of coding.
Thanks James, glad you like the latest version. Will be producing some PDF documentation for it soon and updating this page to reflect the revised product. Cheers!
I’m looking for just this thing, but want to email a CSV file attachment of the data instead of the table in the page. Is this possible?
Hi Richard,
Absolutely but not with the script as it currently stands. I’d have to extend it to match this functionality. Tell you what, buy the script and I’ll have it done by Sunday or your money back. How does that sound?
If you are up for that then please drop me an email with some more information about how you’d like the CSV file to work.
How many lines would your resulting output be on average? I ask because there are two ways to do this. The most compatible would be to create the CSV in memory and then attach it to an email. The quickest would be to create the CSV as a file and attach it to the email, this overcomes issues with size to some extent but it can be an issue for clients whose hosting package does not support certain functionality.
Naturally we’d also want an option for what character to use for field termination and field enclosure.
Finally, what is the intended target reader? Are you hoping to open it in excel or some other spreadsheet package?
Outstanding script! Very clean code…works perfectly. Going to hire out some of my php work to Julian for sure.
Thanks so much George :)
This looks fantastic. How hard would it be to get it to include multiple reports in one email?
Hi Eric,
That would be no problem at all.
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. Looking forward to booking Julian someday for some custom development.
Appreciate it Eric :)
Brilliant. Did exactly what I needed, took less than 10 minutes to get up and running and saved hours worth of coding. Thanks!
Thanks Simon, glad it helped!
Hi Julian,
I have a requirement for generating daily & weekly reports from mysql database.. and has to convert the report into PDFs and to email them to different users daily & weekly..
can this be possible with your script..
Regards,
Kishore
Good morning Kishore,
There is no PDF conversion at the moment, I would need to add this and attach it to the email. I would estimate up to two hours of development time. I charge £30/ph but am happy to negotiate if that is too expensive for you. Feel free to contact me if you are interested in discussing this further.
Hi,
Is this a standalone application or a web application..
Hi Madhov, this is a PHP script that sits on a PHP enabled web server.
Julian,
Can it schedule multiple reports at a time?
Hi Madhov, yes, you need to ensure you have a website or server with access to CRON, you can then schedule reports to run whenever you like. Daily, Weekly, on a day of the week, 1st of the month, whenever :)
Hi, I am using xampp for windows for my dev and was wondering if you know of any CRON like setup that will work with with your software, would like to give it a bash but am not sure if xampp can do cron type setups.
Thanks in advance.
Hi Len,
Check out http://drupal.org/node/37277 and http://cronw.sourceforge.net/
There’s a few options around but I can’t say I have much experience in using them or I’d be able to help further.
I needed a script to send reports daily to the business owner and thought I’d have to write it all. When I found this script I was stoked – it saved me hours of work and looks and works fantastically. It took under an hour to get it setup and running the exact report I needed without a hitch,
Thanks Julian, a great bit of work!!!
Thanks Bob, I’m glad it served purpose well for you :)
Dear Julian,
I purchased your PHP MySQL Email Report Generator about an hour and a half ago, but haven’t received my copy of the download. Could you please email it to me ASAP- I am under a bit of a time crunch on a project.
Thanks!
It came through! It had been wrapped up in my spam folder. Thank you, Julian!
That’s good to hear, thanks Sarah. Do let me know how you get on and if you have any problems don’t hesitate to contact me. I can even give you a Skype call later today if you need some immediate help implementing it.
Thanks so much for this simple, fast and easy to customize PHP script! And thank you for your help via Skype on the questions I had. ANYONE reading this and who needs emailed reports from their mysql database should spend the money for the files. I spent 2-3 hours looking for something like this for free and testing out other scripts but only spent 4-5 minutes on Julian’s script and had my first report in my inbox in another 5 mins!
Thanks Brian! :)
Looks like this will do the trick… just ordered the code – am waiting to receive it.
Ah – was wondering what the time diff was :) Hopefully you’ll have a chance to see this in the morning when you get up…. I’ll be working on this later tonight so if you do have a chance to get that to me by 9am that would be really cool :)
Hi Roberta. The script was delivered. Do let me know how you get on! Automated script delivery system is now online!
Thanks for your help. Nicely written script and great customer service – what more could a gal want?!
Julian,
Great work. Makes pulling data from our servers and formatting it for end user use effortless. It certainly saved me hours of time!
Thanks!
Thanks Steve :)
The script is well written and quite easy to implement. Julian even helped us modify the script to do exactly what we were looking for. Very professional, friendly and an excellent ‘code ninja’ :) highly recommended! Thanks again, Julian.
Thanks for this Ross :) I always appreciate the opportunity to assist with a bit of coding.
Thanks Julian – saved me a lot of time – nice simple way to get what’s in the database reported out to the users – great for producing friendly daily / weekly / monthly reports from database logs… I had trouble with the underlying mail subsystem, but Julian responded quickly and helped me with the issue.
Cheers!
Thanks so much Mitch! :)
The latest version of PHP may give a warning about providing a strict date definition when using a local server. I’ll be releasing an updated version for this. Please email me if you want the latest file. Basically you just need to define the time zone in the config.php – add a new line with date_default_timezone_set(‘UTC’);
If you want to use the correct timezone you can find the appropriate string here http://nl3.php.net/manual/en/timezones.php
I needed a rather quick solution and have found your PHP MySQL Email Reporter very easy to use! Saved me hours of coding. Looking forward to the additional PDF attachments too. Great job, Julian!
Thanks Linda! Good to know the PDF functionality will be useful!
Do you have an example of how to include multiple reports (multiple sql querys) in one email?
I’ve emailed Ryan back but if anyone else needs to know how to do this let me know. Essentially there are functions for generating reports and a function for sending the reports. It’s just a case of generating two reports, appending them and send the final report in a single email.
Absolutly amazing product. Saved me some serious time. Julian helped me with the 2 (or more) reports in one email and first time was the charm. I recommend anyone looking for something like this script to BUY THIS ONE! It’s a godsend.
Aww thanks Ryan =)
I’m from Colombia and I waste maybe three, four days searching for a good solution to mailing a mysql query. I read several forums in english and spanish as well but don’t have any succed until Julian blog was find it.
In this country, paypal have restricted access so I just call a friend ask him to make de payment (like a favor to me) and he make the payment and now I have access to julian code.
I’m a php beginner , I mean, I’m works with legal issues but I’m understand very quickly the code functionality and it works so perfectly that I have to say: thanks julian!! thanks!
Greetings from Medellín, Antioquia, Colombia!.
Hi Juan, so glad it worked well for you :) Gracias!
How hard would it be to add a conditional e-mail statement to only send the e-mail report if there is data records returned from the query? BTW….Thanks for the code. It has helped our company generate reports within 15 minutes of implementation.
Hi Brent,
Thanks for the comment, I’ve done this before so it should be no problem at all. Let me have a look tonight and I’ll ping you over the code changes you need. in hindsight it’s something I should really add as an option in the configuration file.
Cheers,
Julian
Hi Julian,
Love this product! Stopped by to see if there was a way to generate 2 or more reports with one email, and see you have done that now too. Could you email me what you sent to Ryan? Thanks!
Hi Linda, thanks, I forwarded this email onto you.
All the best,
Julian
We have Julian’s SQL Reporter integrated into roughly five applications in our production facilities. These range from production scheduling to sales call logs. His script has been instrumental in getting data out to our field sales people as well as managers.
Recently, I ran into an issue that Julian helped me with. Now I can gush about the excellent quality of support! Fast, timely and efficient!
Thanks Julian!
Some genuinely excellent posts on this site, thanks for contribution. “Always aim for achievement, and forget about success.” by Helen Hayes.
Hi Julian,
Brilliant product, up and running within minutes.
However I intended to purchase the commercial version.
Is there any difference between the private one.
If not I will fund the difference as Ii will be using my version for commercial use.
Please advise how I should proceed or is it easier to purchase the commercial one.
Thanks for the kind feedback David, I’ve refunded your original purchase so please feel freee to purchase the commercial license. The products are identical. 1.5 will be available shortly so I will send that on to you. Thanks again David.
Look forward to the update.
However is it possible via a reports page I have setup to click on one of the xxx.php links and recieve a popup to populate the $recipient1 before it executes. This would be very handy as we are often asked to provide the some reports on an addhoc basis but to different destination email address.
Regards
David
Hi David,
To do this you would create a new page which simply contains a form with an email input field and a submit button.
In the report.php page you would then reference $_POST['email'] instead of report.php page.
Here is a nice bit of reference on the subject. http://stackoverflow.com/questions/871858/php-pass-variable-to-next-page
Naturally you might want some validation and if you are making the page public then perhaps some security or a capcha.
Cheers,
Julian
I was hoping to be able to put two tables from two queries (or more) onto my email one above the other.
The script really only gives me the ability to do one query and one table?
Am I correct or is there a work around to do this somehow.
thanks
Simon
Multiple reports – just read that someone else asked for this here -
Could you please send me the example of this.
many thanks
Simon
Julian Young says:
March 8, 2012 at 9:40 pm
I’ve emailed Ryan back but if anyone else needs to know how to do this let me know. Essentially there are functions for generating reports and a function for sending the reports. It’s just a case of generating two reports, appending them and send the final report in a single email.
Great piece of code, works an absolute treat.
Had a few questions and support was extremely quick and effective highly recommended.
Happy to help Antony! Thanks for pointing out that spelling error!
Howdy Julian,
NOTE: the contact form doesn’t seem to be working so I thought I would try here :)
First off, what an amazing product! Your PHP MySQL Email Report Generator is fantastic and was just what I was searching for.
However, I have run into a bit of a lurch when trying to use a more complex SELECT statement, one that uses two tables, an INNER JOIN and a few WHERE items. It works just tickity-boo in phpMyAdmin … EXACTLY what I require. But pasting the code into the report.php yields nothing when run. A simple single table SELECT works just fine, both in TEST mode and via CRON on the “live” site.
Is this a limitation of the app or is there something else that I need to do? I’m not all that up on PHP and the like, but can follow instructions “to the tee” :)
Any insight will be tremendously appreciated.
Cheers!
Lyle
Thanks Lyle, I’ll get right on that contact form issue, thanks for that!
Drop me an email at info@julian-young.com and include your report.php file (or equivilent) so I can check your code for you.
Thanks Julian, however there won’t be any need for you to check it out because as usual, it was “finger trouble”! LOL :) Or, more to the point, not reading the instructions thoroughly! For reasons unknown, I was using single quotes for the AS descriptions! (and had an = sign that wasn’t escaped) … worked fine in phpMyAdmin, but of course, would not within PHP. Changed them to double quotes (as you show in your very well done instructions) and escaped the = sign and all is fine! Awesome! :)
That’s great Lyle, thanks for letting me know ;)
I need a automatically generate report for my database in Mysql as Daily, Weekly, Monthly and Yearly. Please give me a solution.
Thanks in advance.
Hi Velhassan, with the script here the solution would be 4 reports.
The daily report would be a query that only reports on values for today and would run every 24 hours via a cron script.
The weekly report would be a query that only reports on values for the week and would run every 7 days via a cron script.
and so on…
Version 1.6 is now officially released which tackles a number of small bugs. I’ll be sending a copy to all previous purchasers, many thanks to everyone that has helped test it and all the brilliant feedback received. You guys and gals make it all worth it.
I bought this cool little tool to use with a client’s system, and the first report I tried worked like a charm. Then I tried a really complicated report that needed a temp table and a number of queries to populate that temp table before producing results – this proved too much for the 1.6 implementation due to delimiter issues. So I converted the complex script to a stored procedure (routine), but the PHP Script could not handle the stored proc result set.
So I contacted Julian – and within a couple days he and I were on Skype together building a stored proc for him to test with. A week later I had version 1.7 that perfectly handles the result set of either a stored proc or a normal query.
Thanks Julian – what incredible service and support! It has been a complete pleasure working with you!