Email MySQL Query Results as a CSV File Attachment

About the PHP script to Email the Results of a MySQL Query as a CSV File Attachment

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:

1. Connects to a MySQL Database.
2. Runs a MySQL query that you define.
3. Builds a correctly formatted CSV file from the query results.
4. Emails the CSV file as an attachment to an address you specify (this code doesn't save the results to a file on the server. If you are looking for that, try Google).

Here is an example of the minimal code required to do the above:

1. <?php 
   

2. require('class.email-query-results-as-csv-file.php');
   

3. $emailCSV = new
   EmailQueryResultsAsCsv('localhost','database_name','username','password');
   

4. $emailCSV->setQuery("SELECT * FROM table_name");
   

5. $emailCSV->sendEmail("sender@website.com","receiver@website.com","MySQL
   Query Results as CSV Attachment");
   

6. ?>

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.

Requirements

A web server with PHP and a MySQL database.
You will need to know how to:

• Create an Object of a Class
  • The example files included will show you how this is done.
  • example: $myObject = new className;
• Call Methods of an Object
  • The example files inlcuded will show you how this is done.
  • example: $myObject->someMethod();
• Assign values to PHP variables
  • Comments in the example files are provided to help in this process.
  • If you need more help with PHP. A good place to start is the PHP tutorials at W3schools.com: http://w3schools.com/php/php_variables.asp
• Write an SQL query
  • There are so many excellent resources online about writing SQL queries.
  • A great place to start is the SQL Tutorial at W3Schools.com :  http://www.w3schools.com/SQl/default.asp

How to Use

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).

• Each of the lines of code is very well commented and documented in this file.
• Follow the directions outlined in comments of the file.
• Some items are optional, and they are noted as such.

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:

• class.email-query-results-as-csv-file.php
• example.php

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".

Optional Garbage Collection Method

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.

Support

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.

History

2012-FEB-12 : v2.1.1 (r25)
+ added behavior to only attach file when at least one row has been returned from the database
+ added if no files were attached do not send email

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