"Fantastic script that is so simple to setup, modify and get going in just a few minutes! Thanks for sharing it."
-- Sporran
"Really great work. very easy to setup and run! Grazie"
-- Marco G
"...really happy with the results..."
-- Ben
1. Go to the class.email-query-results-as-csv-file.php code page on Google Code.
2. Click Edit File near the top of the page.
3. Make your changes.
4. Add a description of your changes (what you changed and why).
5.Press Suggest Patch at the bottom of the page.
6. Done!
Keep your code simple and clean. Document your changes. The Google online code editor can be used by anyone.
This is a PHP class file, with examples of how to use it. This code runs a MySQL query. Then sends the results in an email as a CSV (comma separated values) file attachment. This CSV file is properly formatted so it can be opened by OpenOffice.org Calc, or Microsoft Excel.
Copyright © 2009-2011 Stephen R. Owens
This software is licensed under the GNU GPL version 3.0.
NOTICE: You must keep intact all the copyright notices at the top of the files. I've written a very brief summary of the GNU GPL to help you understand the license. You can add your own copyright above mine if you fork the code, but don't remove my copyright.
A PHP class file that will email the results of a MySQL query as a CSV file attachment. This can be done as a standalone driver file that can be run by opening it in a web browser, or run as a CRON job. The more common way to use it, is to integrate it into some other program. The source code in this PHP file is well commented and can be modified to fit your needs. The GNU GPL license gives you the room to adapt it to your project.
What it does:
Here is an example of the minimal code required to do the above:
<?php
require('class.email-query-results-as-csv-file.php');
$emailCSV = new
EmailQueryResultsAsCsv('localhost','database_name','username','password');
$emailCSV->setQuery("SELECT * FROM table_name");
$emailCSV->sendEmail("sender@website.com","receiver@website.com","MySQL
Query Results as CSV Attachment");
?>There are many more settings than those listed above, but those six lines in a driver file are the minimum required to make the magic happen. There are more examples included in the download.
A web server with PHP and a MySQL database.
You will need to know how to:
Step 1: View then Edit the Example File
The source code is well documented. Start by opening the driver file "example.php" in your code editor (I like Notepad++ for windows, and gEdit (pimped) for Linux).
Step 2: Upload the Class File and Driver File to Your Web Sever
After you fill in your specific details, upload the two files to your webserver:
Setp 3: Run the Driver File from a Web Browser
Run the file "example.php" from a web browser.
If you are using the object in debugMode(True) then it will output messages of success or error at each step. Error messages are always output regardless if the debugMode is active or not.
Once you understand how this code works, by getting the example.php file running successfully, then you can then use a minimal amount of the code.
Minimal Amount of Code
The minimal amount of code is found in the included "example-min.php" file.
Attaching Multiple Files to One Email
To attach multiple files to a single email follow the code in the file "example-multi.php".
If you are going to use this class in a much larger program. Then you may want to use the included Garbage Collection method to free up memory. Instead of just doing an unset() on the object. Using the included destruct() method of an object is best practice and helps you avoid circular references, which lead to memory leaks.
This of course depends upon the robustness of your script, and your PHP memory limit settings. PHP automatically collects the garbage at the end of script execution.
So if you are just using this as a standalone driver then you don't need to use the included Garbage Collection.
Further reading on memory leaks from circular references by Paul M. Jones.
If you have questions on using this tool, or have a comment, you can contact me or post a message into the comments section below.
If you need helping modifying this file for a particular purpose, or integrating it with another PHP program, or just can't get it to work, I will write code and provide consulting services for money. Contact me for a quote.
2011-MAR-07 : v2.1
+ added the ability to attach multiple files
+ new file "example-multi.php" shows how to use the class to send multiple file attachments
+ moved setting the CSV file name to the method "setCSVname()"
* the method "setCSVinfo()" is now used to only set the reuseable parts of the CSV file, and no longer sets the file name
* changed "example.php" and "example-min.php" to show the new use of setCSVname()
2011-MAR-06 : v2.0.1
- Removed the extra comma at the end of each row
2011-MAR-02 : v2.0
* Changed the code to a class file, because (a) it's better to use OOP and (b) encourage people to leave the copyright in place as is required by the GNU GPL license.
+ new file: "example.php" to show how the class is used with comments explaing the code
+ new file "example-min.php" to show the minimum amount of code needed to use this class
2009-NOV-11: v1.0
* Initial Public Release
Copyright © 2001 - 2012, Stephen R. Owens, All rights reserved.
Comments for "Email MySQL Query Results as a CSV File Attachment"
Grazie
Im trying to modify the code in order to send N files por N querys. But I'm not sure if it is possible.
Do you understand the idea? Query 1 --> File1.csv ; Query2 --> File2.csv, etc...
For sure I could send N emails, but maybe this will be marked as SPAM.
I undestood how to create the CSV file, but I don't know if it possible to attach 2 or more files with the code.
Can you help me?
Attaching multiple files to one email is very possible. You would have to repeat the code found from lines 167 to Lines 181. These lines attach the CSV file. You will also have to modify the code in lines 167 to 181 to uniquely name each file attachment.
Lines 107 to 136 runs the query and builds the CSV file. Repeat these lines for each needed CSV file.
If you need help modifing the source, I sling code for money.
Count the number of columns returned by the query, which the code already does, and then have the code skip putting a comma after the last column of each row. I will add that to version 2.0.1 and release it tomorrow.
*****
On Fri, Apr 8, 2011 at 6:49 AM, Stephen Owens wrote:
Based upon the error message I would guess your server is running PHP v4.x, which has been deprecated. in v4.x class variables are defined by var and functions by function. I would suggest migrating to PHP5. If that is not an option you could re-write the class for use with PHP4. More information on Classes and Objects in PHP 4: http://www.php.net/manual/en/keyword.class.php.
Just to double check I downloaded the package from my server and retested it. Everything is working just fine.
*****
On Fri, Apr 8, 2011 at 9:54 AM, Craig wrote:
Thank you for the reply. You are exactly correct, migrating to PHP5 was the answer.
Just wonder whether it is possible to password protect the csv file?
No. CSV files are plain text files and cannot be directly password protected.
However, you could do some research and modify the code to create a password protected Excel file, or a password protected Zip file that contains the CSV file. However, password protected Zip files can be brute forced hacked and provide little benefit, besides keeping honest people honest.
Best of luck.
I have used your code and was really happy with the results I was getting with our mysql database.
The problem I have now is we are now using mysqli.
I have tried to change your code but am not getting any where with it.
Any chance you would be able to point me in the right direction as to waht I would need to change?
It is important to note that there is no PHP MySQLi equivalent for two of the PHP MySQL methods being used in this class, and you will need to come up with your own functions to do these things:
1. mysql_field_name()
Insead of using this to get the column names you will need to walk the query results array and pull out the keys.
2. mysql_fetch_array()
The mysqli_query method will return the results so you will need to walk the returned results array when building the file, instead of calling the next row with mysql_fetch_array().
You will need to change the following lines in class.email-query-results-as-csv-file.php:
* 160 to 175
* 246
* 261 to 264
* 271
* 276
* 284
References:
* http://www.php.net/manual/en/mysqli.connect.php
* http://www.php.net/manual/en/mysqli.close.php
* http://www.php.net/manual/en/mysqli.query.php
If you want me to make the changes I can send you a price quote.
really a nice script..
Thanks a lot,
Amit kumar
Thanks for sharing it.
But not receive any mail...can you help us?
Thanks
The PHP mail() function returns true when the outgoing mail server accepts the message for delivery. This doesn't mean the email has been delivered correctly however. There are many issues that could cause email sent via the PHP mail() function to not be received.
You will need to troubleshoot other possibilities, unrelated to this script, to find the point of failure. Some examples:
1. The mail was received, but marked as spam.
2. The recipient's address was incorrect.
3. There is a slow mail queue on the sending mail server.
4. There is a slow mail queue on the receiving mail server.
The problem is likely a server configuration problem, you will want to contact your web hosting provider.
If you want me to figure it out for you I charge $100 USD per hour.
Leave a Comment