User Quotes

"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

How to Contribute Your own Code Changes to this php class

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.

Code >> Email MySQL Query Results as a CSV File Attachment

Email MySQL Query Results as a CSV File Attachment

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.

Language: PHP
File Size: 19 kb
Version: 2.1.2 - February 21, 2012
MD5 Sum: 00a91edc660ba6ac78707365677d5870
download Email MySQL Query Results as a CSV File Attachment

License & Copyright:

Copyright © 2009-2012 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.

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
  • Write an SQL query

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

comments powered by Disqus