PHP Export MYSQL to CSV/Excel
Something I’ve often thought would be the cherry on the intranet page pudding is a button to export a visible table as a spreadsheet. Quite often my job involves developing an intranet application to gather and present data. I’ve developed a few such pages and nearly always have to provide a tabular summary of the data. Again, nearly always I find people after development has finished scraping the page with a good old copy/paste to further manipulate the data or send it to external customers….
So, I spent an hour knocking together a simple yet flexible and powerful script which can be used to export a mysql query from a mysql table to a spreadsheet.
Yes - the user could continue copying and pasting, but it looks and feels far more polished and professional if they have a button which automates the process for them. It also prevents them nagging me when they inadvertently copy the style sheet and formatting across. Also, sometimes, apparently its too difficult to copy and paste a table these days.
There are only four options - but really these are all you need. In fact three of them are just that - optional. You can force a specific database, or set a default within the script and use that. You can use column headings or not. The choice is yours. You can even select which columns you wish to export if you want to limit the data that is on tap. The only required parameter is the SQL query of the information you wish to export. Usually you already have this stored as a variable, I use, and will assume you do to, $sql.
The script works by you passing the parameters to the php scrip by a POST form. It needs to be POST because of the encoding and the fact that get is limited to 100 characters (not nice for long sql queries). Added security is provided by the fact that POST data is invisible to the client in the address bar. Of course they could view the source, but we can’t get too complicated.
The form’s action is the script, the method is POST.
<form name="export" action="export.php" method="post">
A button will be the user’s trigger for exporting the file.
<input type="submit" value="Export">
There are then a series of “hidden” fields which allow you to pass parameters to the script. First is the SQL for the query - this is the full SQL that you would pass to mysql_query.
<input type="hidden" value="'.$sql.'" name="sql">
Secondly, there is a “table” option. If you include this the script will generate a row of field names in the first line of the spreadsheet, you can omit this input and the script will ignore column names.
<input type="hidden" value="'.$table.'" name="table">
The “cols” option should take a comma separated list of selected column numbers. Below code shows me selecting 5 distinct columns - the script will only show these columns and will ignore all other fields. Again this is optional, but realise that usually hidden fields like unique id’s or fluff data will be included.
<input type="hidden" value="5,6,9,11,12" name="cols">
Finally, the script allows you to select which database you’re working in. You can omit this option and it will default to a hardcoded preset in the script - which you must set!.
<input type="hidden" value="limesurvey" name="database">
And close the form.
</form>
The following is the code for the script - you have to set your database credentials and a default database (you could leave the default database but you will need to pass the parameter each and everytime you call the script).
<?php
//you're delimiter - I prefer tab (as below) because I sometimes have commas in field contents.
$delimiter = "\t";
//filename for exported sheet - change it or leave it, the user will be able to save as any filename they want anyway.
$filename="Export.xls";
//Send headers
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
//add database credentials here.
$con = mysql_connect("DATABASE ADDRESS","USERNAME","PASSWORD");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
//set database - don't forget to set the default database.
if($_POST["database"]!=”"){
mysql_select_db($_POST["database"], $con);
}else{
mysql_select_db(”DEFAULT DATABASE NAME”, $con);
}
if($_POST["table"]!=”"){
$res = mysql_query(”SHOW COLUMNS FROM “.$_POST['table']);
while ($row = mysql_fetch_array($res)) echo str_replace(”\r\n”, ” “, $row[0]).”\t”;;
echo “\r\n”;
}
$sql = $_POST["sql"];
$result = mysql_query( $sql, $con );
if($_POST["cols"]!=”){
$cols = explode(”,”,$_POST["cols"]);
}
$fields = mysql_num_fields($result);
while ($row = mysql_fetch_array($result)) {
for ( $c=0; $c<=$fields; $c++) {
if(count($cols)>0){
if(in_array($c,$cols)){
echo str_replace(”\r\n”, ” “, $row[$c]).”\t”;
}
}else{
echo str_replace(”\r\n”, ” “, $row[$c]).”\t”;
}
}
echo “\r\n”;
}
?>
Filed by Kieran at November 28th, 2008 under Uncategorized
Pingback by kierandelaney.net » Blog Archive » PHP Export MYSQL to CSV/Excel — November 28, 2008 @ 11:21 am