Let’s assume that you already have a database setup with a table called “users” and you want to export users and their email addresses to a CSV file. PHP has built in functions to deal with CSV files.
“CSV is a simple file format that is widely supported by consumer, business, and scientific applications. Among its most common uses is to move tabular data between programs that naturally operate on a more efficient or complete proprietary format. For example: a CSV file might be used to transfer information from a database program to a spreadsheet.” – Wikipedia
The code to connect to a MySQL database, query for users, create a file, and write CSV data to the file is provided below.
<?php
// Connect and query the database for the users
$conn = new PDO("mysql:host=localhost;dbname=mydatabase", 'myuser', 'mypassword');
$sql = "SELECT username, email FROM users ORDER BY username";
$results = $conn->query($sql);
// Pick a filename and destination directory for the file
// Remember that the folder where you want to write the file has to be writable
$filename = "/tmp/db_user_export_".time().".csv";
// Actually create the file
// The w+ parameter will wipe out and overwrite any existing file with the same name
$handle = fopen($filename, 'w+');
// Write the spreadsheet column titles / labels
fputcsv($handle, array('Username','Email'));
// Write all the user records to the spreadsheet
foreach($results as $row)
{
fputcsv($handle, array($row['username'], $row['email']));
}
// Finish writing the file
fclose($handle);
?>The purpose of this post is not to demonstrate how to connect to a database and/or query it for contents, nor how to loop through query results, so I will not explain how PHP’s PDO database abstraction class works. The above code demonstrates how you create a file and write CSV data to it, so let’s walk through the above code.
First, the script connects to a MySQL database using PHP’s PDO class and queries the users table for all the users sorted by username. It then generates a filename with the current UNIX timestamp appended at the end. In this example, the file will be written to the server’s /tmp/ directory. You can change the path to what ever directory you want as long as you have write access to it.
The code then opens the file connection, which creates the file and overwrites any existing file. Once the file is created, the column labels are written to the file. In this example, we are only writing two columns, a Username and an Email column. Next comes the main loop that iterates through all the users that our SQL query got from the database. The loop writes the username and the email of each user record. Finally, we close the file.
What you do with the CSV file beyond this point is up to you and your requirements.
I will write another post later on how to create an actual Excel file with an XLS/XLSX extension.