start from here

Extract Your Data

< ?php
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
?>

In this section of code, we will attempt to export the values from the database and write them into the correct columns of our excel spreadsheet. First we start a loop using while(). Basically that is saying while something is true, keeping looping. In this case, the loop will loop steadily until all the values have been extracted.

In the next line of code we start by clearing the variable of $line. We do this so that old values don’t get mixed in with our new values. Next we start a foreach() loop. A foreach() loop is a loop that can only be used in conjunction with arrays. If you would try to use it any other way, you will get an error. Basically a foreach() loop will loop until there are no more values to be scanned in an array. Our array in this case is $row. Inside the foreach() loop we have an if() condition. What is happening here is that we are telling our program that if the current extracted value from the database is blank, write a tab in its’ place, otherwise, write the value and then a tab. The line right under the “} else {” section is used to preserve quotes if any are found. This is needed because quotes will mess up the exportation process.

After the if - elseif statement we add our $value to the variable of $line. Here again we see the period before the equal sign, stating that we want the values added on the end of the $line variable. If we did not have this period we would only get the last value extracted from our database. Next we trim our $line variable of any white space, set all the lines to our $data variable, and add a new line. The last thing we are going to do is get rid of any returns in the data. The last line of code is simply looking for any characters that are carriage returns and replacing them with nothing. A carriage return will mess up all your data. That will force the next value to a new line and your results will not be as you expected. Good thing we caught that in time.

With those 13 lines of code you just extracted all the data from a table in your database and formatted it for an excel spreadsheet. Pretty easy wasn’t it. So let’s see what we did so far. First we extracted each field name and set them as our column headers, then we extracted all our data and wrote it in the appropriate position under our field names, now all we have to do is write the data to the screen so we can download it.

Setting A Default Message
< ?php
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
?>

This tiny but very important section of code examines if any data was even found. If no data was found or extracted, we set the $data variable to tell the user there are no records. This is very important just for the simple fact that it is nice to keep the user informed. If we did not do this small task and no records were found, we would have a blank excel spreadsheet and chaos would ensue. In all seriousness, it is just nice to tell the user that there is nothing to extract instead of seeing nothing at all.

Setting Up An Automatic Download
< ?php
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

This is our final section of code. It uses the header() function to tell the browser that we have a file that needs to be downloaded. This is nice because the actual page will never be shown, the user will just see a pop-up asking them to save the spreadsheet. There are only really two lines above you need to be concerned about. Line #2 tells the browser what the file name is going to be. You can either hand code it like I have, or let it be set dynamically by a variable, that choice is yours. This is how you name your soon to be downloadable .xls file.

Line #5, or the last line is the crucial line, without this simple line, the whole project would fail. After we tell the browser we have a file to be downloaded we must write the data for that file to the screen. As you can see we are writing the $header variable with all our field names, then a new line, and finally all of our data. If we did not write our data to the screen the automatic download would not work at all. Basically the automatic download just reads the page, places the contents in the file you named in line #2, and prompts the user for a download.

If at anytime you use this method of writing a file directly for download, and you make a coding error, the user will not be prompted for a download, they will see the error on the page. Always remember to debug your programs before making them live to the public.

I Hope You Had An Excel-lent Time

There you have it, you have just hopefully figured out that extracting your data into an excel spreadsheet is pretty simple. Let’s recap our key ingredients for this process to work.

* Set up your database connection variables
* Extract and write your field names seperated by tabs
* Preserve quotes in your data
* Remove carriage returns from your data
* Print data to the screen after the auto download header is called

That about covers everything you will need to know to format excel spreadsheets from a MySQL database. I hope you enjoyed today’s tutorial and remember, a freak without PHP is just plain weird.