PHP + MySQL = CSV

---

This is basically the exact same code I used in my Addressbook to export all of the contact information. Its good to note that a CSV is not a full backup, while you can restore the information in a Database you will not restore the structure.

A CSV file is commonly used to export information for manipulation, or importing into other applications.

You could export all of your contacts fro example and import them into Google contacts.

So what is a CSV file?

A file format is a particular way to encode information for storage in a computer file. Particularly, files encoded using the CSV format are used to store tabular data. The format dates back to the early days of business computing and is widely used to pass data between computers with different internal word sizes, data formatting needs, and so forth. For this reason, CSV files are common on all computer platforms.

From Wikipedia: http://en.wikipedia.org/wiki/Comma-separated_values

CSV files don’t have to have there values separated with a comma (,) you can use any character you like IE: | : or #

Lets get Started

What do we want to do? Well we want to export all the data in a given table. Im this case its contact information, so each row will contain a series of columns containing personal information. When we execute the script we should Prompt for a download to the users computer, and we will also save a copy of the file to a folder on the server.

I’m not going to show how you connect to the Database as this should already be done in order to have data to export.

We need to tell the script what table to grab our data from. Since we are exporting contacts from our address book database we will use the table “addressbook” or whatever you feel like using.

Next we need to set up our query using the mysql_query() function. Our Query will be “select * from $table”, this will grab everything under the addressbook table. At the same time we will start an empty variable called $out, this will hold our data in a bit.

// Connect database
$table="addressbook";
$result=mysql_query("select * from $table");
$out = '';

The next step is to execute our query using mysql_list_fields(), we need to pass this out login information, as well as the table we will be looking up ($table).

In order to be able to export all of the data we need to count the rows. we do this with mysql_num_fields() this should give us a number to work with.

// Get all column names in the table".
$fields = mysql_list_fields($dbname,$table);

// Count the table fields and put the value into $columns.
$columns = mysql_num_fields($fields);

The next piece of code is fairly simple, We will cycle thought the row and take the column names and print them to the first row. You will see $out .= ‘”‘.$rowNames.’”,’; by using $out .= “” we are constantly appending to $out. We take $rowNames place it in Quotes and then add a comma to the end. Repeat until we are finished and reach the end.

Each row is ended with \n this creates a new line and will keep the file tidy if you open it with a text editor.

// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
	$rowNames = mysql_field_name($fields, $i);
	$out .= '"'.$rowNames.'",';
	}
$out .="\n";

The next piece of code is fairly simple as well, We will cycle thought the data one row at a time until the total number of rows is reached. The rest is very similar to the field names in that we go thought each row now and print the data. The main difference is we are now working with an array. As $i is incremented per row we also increment each column. So While we are on row x and For every column in that Row we set $out. Once the columns are finished we move on to the next row until we reach the end.

Read up on While and For

// Add all values in the table to $out.
while ($rowNames = mysql_fetch_array($result)) {
	for ($i = 0; $i < $columns; $i++) {
		$out .='"'.str_replace('”', '”"', $l["$i"]).'",';
		}
	$out .="\n";
}

At this point we are basically finished with the exporting, we have build up $out to hold our field names, and our data. The next step is to dump it to a file. We take $f and assign it fopen and a file name with the property w (write). I also used date(“Y-m-d-H”) to give the file a unique name that would have some meaning.

fputs is going to take our opened file ($f) and save $out to it. The file has been created and our data has been save. Now we need to close the connection to the file using fclose

// Open file export-addressbook(date).csv.
// Make sure this directory is "755"
$f = fopen ('backup/export-addressbook'.date("Y-m-d-H").'.csv','w');

// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);

All the above code is going to do is save the file on the server. If we want to save the file to our own computer we need to be prompted to save. This is done by setting out files header content type and file path. One last function readfile() with the path to our freshly created file will finish the deed, the browser will take care of the rest.

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="backup/export-addressbook'.date("Y-m-d-H").'.csv"');
readfile('backup/export-addressbook'.date("Y-m-d-H").'.csv');
?>

It should be noted that the date format only uses hours as the smallest measure of time, if we chose seconds or minutes we can potentially lose the file as the set time would be different.

This can easily be worked around by saving date to a variable and using the variable in each instance of date()

Source

<?
// Connect database
$table="addressbook";
$result=mysql_query("select * from $table");
$out = '';

// Get all column names in the table".
$fields = mysql_list_fields($dbname,$table);

// Count the table fields and put the value into $columns.
$columns = mysql_num_fields($fields);

// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
	$rowNames = mysql_field_name($fields, $i);
	$out .= '"'.$rowNames.'",';
	}
$out .="\n";

// Add all values in the table to $out.
while ($rowNames = mysql_fetch_array($result)) {
	for ($i = 0; $i < $columns; $i++) {
		$out .='"'.str_replace('”', '”"', $l["$i"]).'",';
		}
	$out .="\n";
}

// Open file export-addressbook(date).csv.
// Make sure this directory is "755"
$f = fopen ('backup/export-addressbook'.date("Y-m-d-H").'.csv','w');

// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="backup/export-addressbook'.date("Y-m-d-H").'.csv"');
readfile('backup/export-addressbook'.date("Y-m-d-H").'.csv');
?>