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 well. But if got any questions, use comment form for the feedback.
Arif
Popularity: 7%
Home
Loading ...
