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", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF
() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber
($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel
($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $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.
Follow Us on Google+
Our RSS Feeds
