Export MySQL table to CSV using PHP

Lets write a simple php program to export data from MySql table to CSV file.

PHP Code

<?php

// Database Connection

$host="localhost";
$uname="root";
$pass="";
$database = "a2zwebhelp"; 

$connection=mysql_connect($host,$uname,$pass); 

echo mysql_error();

//or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or 
die("Database could not be selected"); 
$result=mysql_select_db($database)
or die("database cannot be selected <br>");

// Fetch Record from Database

$output = "";
$table = ""; // Enter Your Table Name 
$sql = mysql_query("select * from $table");
$columns_total = mysql_num_fields($sql);

// Get The Field Name

for ($i = 0; $i < $columns_total; $i++) {
$heading = mysql_field_name($sql, $i);
$output .= '"'.$heading.'",';
}
$output .="\n";

// Get Records from the table

while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="\n";
}

// Download the file

$filename = "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo $output;
exit;

?>

Download Code Total Downloads: 12439

Other Related Posts

Read CSV File using PHP
Export MySQL table to iCalendar (.ICS) using PHP
Import data from .ICS file to MySQL table
Comments
26 February
2014
Sugiyama
Excellent job! It helps a lot. Thanks!
25 February
2014
nishant
thank You
12 February
2014
sivateja
Very small solution....:)
12 February
2014
esiek
Perfect! Thank you very much for this code.

Before te start this script I must add
[code]
mysql_query("SET NAMES utf8");
mysql_query("SET character_set_results = 'utf8',
character_set_client = 'utf8',
character_set_connection = 'utf8',
character_set_database = 'utf8',
character_set_server = 'utf8'");
[/code]

Then this code is work good for me, because in Poland we have special character
10 February
2014
Sakil
thank you very much... :)
04 February
2014
Douglas
Great piece of work to generate the .csv file... Thanks

Any chance that you could tell me how I could upload that .csv file to a secure FTP site using a CRON job?

That way I could generate the .csv in the cron and upload it to the server, all on an automated basis, and would make live very much easier...

Thanks in advance for your assistance.
03 February
2014
worker
how to create the .csv file in magento with the cron job of magento any idea,,,,?
03 February
2014
worker
how to create the .csv file in magento with the cron job of magento...
any idea
28 January
2014
spitfirelive
Great code!!

What if I wanted to store the csv file on line for downloading later?

Thanks
04 January
2014
santosh
thank You Very much sir
Great Work
03 January
2014
Cathy
This displays nicely in the browser, but how do you put a link to it so you can download the file as a csv
02 January
2014
Aamir
Awesome work, the easiest and the best .csv code I came through. Keep up the good work.
22 December
2013
Mayur
Hey,

Awesome work man! Thanks for this wonderful resource. Much appreciated!

Just one thing, how do include a date range? basically what if I wanted to download stuff from a specific start date and end date.

Probably, End Date won't matter as much but Start date does.

Please help, thank you for you time and effort!

Cheers,
Mayur
09 January
2014
vagheladev
thanking you
26 December
2013
Nestor
You could put inside sql statement the range of date and you will obtain just the data with the date range you want.

Example:

$sql_example = "SELECT * FROM example_table WHERE date_column >= START_DATE AND date_column <= END_DATE";

or

$sql_example = "SELECT * FROM example_table WHERE date_column BETWEEN START_DATE AND END_DATE";


Another comment...mysql commands are deprecated use mysqli or PDO statements.

Cheers,
Nestor
22 December
2013
Mayur
Hey,

Awesome work man! Thanks for this wonderful resource. Much appreciated!

Just one thing, how do include a date range? basically what if I wanted to download stuff from a specific start date and end date.

Probably, End Date won't matter as much but Start date does.

Please help, thank you for you time and effort!

Cheers,
Mayur
20 December
2013
Rajesh
Thanks, It works great
19 December
2013
Abhishek
thank you,very nice code.How can I send the csv file generated from this code as an email attachment
05 December
2013
Umar
Very useful to my project.
Thanks
04 December
2013
Kico
Real good code, works well for me!
Thanks!
28 November
2013
alex
Hi,
Im trying to do this for oracle database. I get no records into the CSV.
Please help me out to fix this.

Please find my code below.



$conn = oci_connect('uid', 'password',

'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=1521))(CONNECT_DATA=(SID=database)))');
if (!$conn) {
$e = oci_error();
echo("
".$e);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT server,ipaddress FROM table where rownum<=50 order by kill_time desc ');
if (!$stid) {
$e = oci_error($conn);
echo($e);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
$e = oci_error($stid);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Fetch the results of the query




$ncols = oci_num_fields($stid);
echo "\n";
for ($i = 1; $i <= $ncols; ++$i) {
$colname = oci_field_name($stid, $i);
//echo " ".htmlentities($colname, ENT_QUOTES)."\n";
}


$filename = 'file.csv';

$out = '';

// fiels to export
$out .='server, ipaddress';
$out .="\n";

// Add all values in the table
while ($l = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
for ($i = 0; $i < 2; $i++) {
$out .=''.$l["$i"].',';
}
$out .="\n";
}
// Output to browser with appropriate mime type
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename");
echo $out;
exit;
?>


27 November
2013
chaitany
super work
it helps me very much
can you please send me a code to import data from csv to mysql

thank you very much
29 November
2013
Rishi
Please check
http://www.a2zwebhelp.com/csv-file-reader-in-php

Thanks
27 November
2013
satish
Very Nice and Very Useful to Begginer.....Thank you So Much and i hope god always take care.
25 November
2013
Rean
I cant seem to get the download box to appear. Am I missing something?
21 November
2013
clavery
simple and clean code, So Good!
20 November
2013
vipin saini
I my php file following function is not working...however i have added i after mysql as mysqli .....

mysql_field_name()
11 November
2013
Olga
Great!!! Works like a charm!!! Thank you very much
comments powered by Disqus