Home > csv, exel, export data, mysql, php > Export MYSQL data into Excel/CSV via php

Export MYSQL data into Excel/CSV via php

Hello Friends,

Today i came across a functionality where i need to Export the MYSQL data into CSV/Excel file via PHP function/script. There are such requirement where client needs to Export the MYSQL data (Order data,Member data,Newsletter emails etc) into Excel sheet or CSV file for future reference or need to send to other team for future work. You can give a button or link from where client can click on it and get a Excel or CSV file with all data from MYSQL database tables using(through) PHP.

Here i am sharing a function using which you can easily export the MYSQL data into Excel/CSV with a single click on button or link. If you are looking to export the filtered data than you can pass parameters into function and make a sql query accordingly. Do you know how to Import CSV/Excel data into MYSQL ?

<?php
function export_excel_csv()
{
$conn = mysql_connect(“localhost”,”root”,””);
$db = mysql_select_db(“database”,$conn);

$sql = “SELECT * FROM table”;
$rec = mysql_query($sql) or die (mysql_error());

$num_fields = mysql_num_fields($rec);

for($i = 0; $i < $num_fields; $i++ )
{
$header .= mysql_field_name($rec,$i).”\t”;
}

while($row = mysql_fetch_row($rec))
{
$line = ”;
foreach($row as $value)
{
if((!isset($value)) || ($value == “”))
{
$value = “\t”;
}
else
{
$value = str_replace( ‘”‘ , ‘””‘ , $value );
$value = ‘”‘ . $value . ‘”‘ . “\t”;
}
$line .= $value;
}
$data .= trim( $line ) . “\n”;
}

$data = str_replace(“\r” , “” , $data);

if ($data == “”)
{
$data = “\n No Record Found!n”;
}

header(“Content-type: application/octet-stream”);
header(“Content-Disposition: attachment; filename=reports.xls”);
header(“Pragma: no-cache”);
header(“Expires: 0”);
print “$header\n$data”;
}
?>

What you need to do is…
1) Copy above function and paste it into your file.
2) Change MYSQL connection settings in mysql_connect(“localhost”,”root”,””).
3) Change database name in mysql_select_db(“database”,$conn)
4) Change table name in $sql = “SELECT * FROM table”.
5) Thats it.

Let me know your thoughts for the same. If you face any problem in this than let me know via comment or contact us form.

To know more about programming,JavaScript issues,jQuery,Expression Engine,MYSQL database and Open-source, enter your email address below. We will send you free tutorials.

Advertisements
  1. Mital patel
    May 3, 2012 at 6:12 pm

    Nice Blog !

  2. Olivia
    June 27, 2012 at 7:37 pm

    Took a little bit to clean up the code, but this works pretty well!
    Thanks

  3. May 24, 2013 at 10:01 pm

    Hi, I would like to subscribe for this website to take
    hottest updates, so where can i do it please assist.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: