Friday, 25 May 2012

How to create excel file with mysql data using php code

<?php
include("dbconnect.php");
$sql="select fname,lname,email,shipping_address,city,state,zip,phone,redeem1,shop1,redeem2,shop2 from redeemption_list order by id desc";
$result=mysql_query($sql);
$i=0;
$date = date('Y-m-d-H-i-s');
$filename = "export-".$date.".csv";

$i = 0;
$sep = "\t";
$fp = fopen($filename, 'w');
$schema_insert = "";
$schema_insert_rows = "";

for ($i = 1; $i < mysql_num_fields($result); $i++)
{
$schema_insert_rows.=mysql_field_name($result,$i) . "\t";
}
$schema_insert_rows.="\n";
//echo $schema_insert_rows;
fwrite($fp, $schema_insert_rows);

while($row = mysql_fetch_row($result))
{
//set_time_limit(60); //
$schema_insert = "";
for($j=1; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= strip_tags("$row[$j]").$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);

//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\n";
    fwrite($fp, $schema_insert);
}

fclose($fp);

      ?>

No comments:

Post a Comment

Source base installation of php, mysql and apache in ubuntu/ linux

Compile and Install a LAMP(Linux/Apache/MySQL/PHP) Server from Source In the last post, I described the method to install a LAMP ser...