Creating a text or csv file of information from your database

Thursday, December 4th, 2008 at 9:50am, in File Management, written by Stefan Ashwell

There are some occasions where you would want to export your site's information in CSV or similar text format. You might want to do this so you can view reports in a spreadsheet, or you might want an export of your product information to upload to a service like Google Products. Either way the method and end result are essentially the same.

First lets think about what steps need to occur to get our end result. First, we need to get our information from the database. Then we need to put that information in the correct format. Finally we want to save the result as a file with a ".csv" or ".txt" extension.

Getting our information from the database

I will presume readers will know how to create the database connection itself so I won't dwell on it here. In our example I am querying a 'products' table from an online store ready to create a product feed.

$db             = new mysqli('localhost', 'root', 'password', 'database');

$result         = $db->query("SELECT * FROM products");

while ( $record = $result->fetch_assoc() ) {

}

The code above is a pretty simple SELECT statement and a while loop through the results.

Putting our information in the correct format

Now we have our query sorted and looping through the results, we need to start creating the contents of our file. At the very top of the CSV file we might want to include column headings, so before our loop through the query results let's create the first line of our file.

$file = "Product ID,Product Name,Description,Image,Price,Stock\r\n";

Next, in our loop we'll start adding rows to the file, including each product's information.

while ( $record = $result->fetch_assoc() ) {
   $file .= $record['id'].','.$record['name'].','.$record['description'].','
   .$record['image'].','.$record['price'].','.$record['stock']."\r\n";
}

You might be wondering what the "\r\n" is, this is formatting that will create a new line in the file. You can only use these in double quotes in PHP.

Save the file

Now we should have a variable called $file that contains all the contents of our CSV file ready to be saved. We can do this like so:

header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=".date('dmY')."_csvfile.csv");
echo $file;

This will cause the page to prompt the user to download our file when it is opened in a browser. You can call the filename whatever you like, in this example I have included a date for reference.

I hope you've found this article useful, if you have any comments don't hesitate to leave one!