Hi folks,

Some time we need to have a CSV file and store our database data in it for reporting and other things. To create such file on the fly we will need PHP, Apache and MySQL installed which i am sure is installed. What this script will be capable of:

• Get data from MySQL table
• Export multiple rows from db
• Save file with different file name with date & time

Here is the script. One would need to connect to database and the rest of explanation i write in the comments inside the script to explain the lines.

<?php
//db.php connection to your database

// Query to get results from mysql table
$query = “SELECT id,name,phone,address FROM user”;
$res = mysql_query($query);

// Functions for export to excel.
function xlsBOF() {
echo pack(“ssssss”, 0×809, 0×8, 0×0, 0×10, 0×0, 0×0);
return;
}
function xlsEOF() {
echo pack(“ss”, 0x0A, 0×00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack(“sssss”, 0×203, 14, $Row, $Col, 0×0);
echo pack(“d”, $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack(“ssssss”, 0×204, 8 + $L, $Row, $Col, 0×0, $L);
echo $Value;
return;
}
// Unique file name with datetime
$file_name = “csv-”.date(‘dmy-His’);
header(“Pragma: public”);
header(“Expires: 0″);
header(“Cache-Control: must-revalidate, post-check=0, pre-check=0″);
header(“Content-Type: application/force-download”);
header(“Content-Type: application/octet-stream”);
header(“Content-Type: application/download”);;
header(“Content-Disposition: attachment;filename=$file_name.xls “);
header(“Content-Transfer-Encoding: binary “);

xlsBOF();

// Top of the excel sheet to write header
xlsWriteLabel(0,0,”User Data – CSV”);

// Make column labels. (at line 3)
xlsWriteLabel(2,0,”Id”);
xlsWriteLabel(2,1,”Name”);
xlsWriteLabel(2,2,”Phone”);
xlsWriteLabel(2,3,”Address”);

// To start writing from row three from top
$xlsRow = 3;

// Put data records from mysql by while loop.
while($row=mysql_fetch_array($res)){

xlsWriteNumber($xlsRow,0,$row['id']);// 0 indicates column number
xlsWriteLabel($xlsRow,1,$row['name']);
xlsWriteLabel($xlsRow,2,$row['phone']);
xlsWriteLabel($xlsRow,3,$row['address']);

$xlsRow++;
}
xlsEOF();
exit();
?>

This script is tested and working nicely.

Related posts:

1. Create and Write to a file in PHP
2. Open letter to EC : “We are using MySQL, help save it”
3. Extract from Archive Tar/Gz/Tar.gz with Pear in PHP