Export Mysql data to CSV – PHP tutorial
Written by Gertjan on December 16th, 2007Want to export your Mysql data to a CSV(comma seperated value) file? In this tutorial i will show you how to export your data from Mysql into a CSV file.
Fist off we start by creating the connection to the Mysql database:
- $host: this is the location for the Mysql server it can be a hostname or an ip adress. it is usualy localhost.
- $db: this is the Mysql user account used to access the database.
- $user: this is the password for the Mysql user account
- $pass: this is the name of the Mysql database used.
Now we include the export function.
We will need to include the function ‘exportMysqlToCsv‘ in the file ‘exportcsv.inc.php‘
Now we declare what table should be exported from Mysql into the CSV file:
- $table: The name of the table that should be exported from Mysql to csv.
Now we start the export to csv from mysql by calling the function ‘exportMysqlToCsv‘:
This function will export all data in the Mysql table. It will place the fieldnames on the first row of the CSV file.
The function will export all Mysql data to a file called ‘export.csv’ by default.
You can change this by adding an extra parameter to the exportMysqlToCsv: exportMysqlToCsv($tablename,$filename).
When the export finishes all your data will be inserted into the csv file and the file will be presented as a download.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?php $host = 'localhost'; // MYSQL database host adress $db = ''; // MYSQL database name $user = ''; // Mysql Datbase user $pass = ''; // Mysql Datbase password // Connect to the database $link = mysql_connect($host, $user, $pass); mysql_select_db($db); require 'exportcsv.inc.php'; $table=""; // this is the tablename that you want to export to csv from mysql. exportMysqlToCsv($table); ?> |
The actual Mysql Export to csv function:
Feel free to alter this file to your needs:
To change the default filename of the exported data change the $filename variable to your likings.
file: exportcsv.inc.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | <?php function exportMysqlToCsv($table,$filename = 'export.csv') { $csv_terminated = "\n"; $csv_separator = ","; $csv_enclosed = '"'; $csv_escaped = "\\"; $sql_query = "select * from $table"; // Gets the data from the database $result = mysql_query($sql_query); $fields_cnt = mysql_num_fields($result); $schema_insert = ''; for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out)); // Output to browser with appropriate mime type, you choose ;) header("Content-type: text/x-csv"); //header("Content-type: text/csv"); //header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); echo $out; exit; } ?> |
Tags: csv, csv export, export, howto, mysql, mysql export, PHP, tutorial
Related Posts:
January 14th, 2008 at 6:57 AM
tnx… this is really useful
February 25th, 2008 at 3:59 AM
Very handy,
Is there a method to concatenate particular row data (denoted by a unique identifier) from several tables into one CSV file?
Thanx
March 14th, 2008 at 5:30 PM
Thanks for this great info! Is there a way to save the .csv file to your server after export, and not have it as a download? Or is it done already?
March 22nd, 2008 at 12:24 AM
Hi, new to PHP so not too sure what i am doing. Script works great, and just what i need but i would like to change the field headings to match outlook 2007. I am using 2 fields, currently ‘username’ and ‘e-mail’, and would like to name them ‘First Name’ and ‘E-mail Address’. Have tried for hours now and can’t figure it out. Help please.
April 12th, 2008 at 1:51 PM
is there any way to bold the title of exported CSV file. Kind Regards, Naughty
May 29th, 2008 at 8:01 AM
I might be wrong, as I only had a quick look at the code. You can expand this function by passing the function a query in stead of a table. This should enable you to pass it a join query and thus join multiple tables (Jay).
You can then also use the ‘as’ identifier to rename the fields (Stevet540)
If you want to know how, just replace the line
$sql_query = “select * from $table”;
with
$sql_query = $table;
and then pass the query when calling the function:
exportMysqlToCsv(“select * from some_table”);
This is not neat, or good code, but should work.
Thanks for the tut. I found it very useful.
June 28th, 2008 at 8:56 AM
Thank u very much ,It’s very useful for me.
August 14th, 2008 at 8:52 AM
Thanks for this Great Code!
August 21st, 2008 at 11:11 PM
Perfect. Excellent, simple solution.
Rather than require the other file, I just combined the two php files into one. Any problems with that?
September 1st, 2008 at 10:49 AM
I want to this csv file as email attachment to mail on single address.
Plz hlp me. Send me code on my email as soon as possible.
September 2nd, 2008 at 12:09 PM
good stuff! thanks a lot..
September 2nd, 2008 at 12:34 PM
Nice function. Thanks a lot
September 5th, 2008 at 4:12 AM
i’m trying to use your script here; looks to be pretty solid. however, i’m encountering some issuesw/ the headers…any help would be appreciated…i’m still a noob.
September 5th, 2008 at 5:19 AM
Thanks a lot . It’s useful for me
September 5th, 2008 at 6:37 PM
so, i left a comment about how i was having problems w/ the headers.
(don’t see it here)
people may run into the same issue i did (noob-sauce for reals) but here’s a tutorial/explaination for what was happening.
http://www.kirupa.com/forum/showpost.php?p=2149084&postcount=3
with the script provided here (awesome, btw) and that tutorial, you have everything (as a noob) to get this export to csv going. cheers.
September 8th, 2008 at 12:19 PM
Hiii …
This is very nice n useful code..
but I actually want to store file on localhost after exporting ..
can u tell me wht can I do for it??
Thanks in advance
September 10th, 2008 at 7:09 PM
what if i want to export only some fields from the table?
September 11th, 2008 at 5:08 AM
plz help me to attach csv file in as emailattachment to send on specified email address only.
September 11th, 2008 at 11:57 AM
Nard, if you want to export some fields, replace the line
$sql_query = “select * from $table”;
with
$sql_query = “select id, Name, Email from $table”;
(where id, Name, Email are require fields)
September 15th, 2008 at 7:48 AM
thank you very much tech! solved my problem.
September 15th, 2008 at 2:33 PM
I am having some problems:
At first I had a problem with my database connection so the .csv download was presented but no data was in it.
I have since fixed the connection string but now the data is just dumped into the browser instead of providing me with the download.
Can someone please help!
September 16th, 2008 at 12:10 AM
Thanks a lot for the post!! Really helped me.
October 22nd, 2008 at 1:44 PM
Very nice! Thanks for taking the time to put this up!
October 23rd, 2008 at 5:25 PM
Greetings, Thank you so much for the scripts. I have been working for weeks trying to figure out how to get the array to work and then make it where it would take the CSV and place the field names at the top with seperators. If whoever created it were standing right next to me they would have a whopper from BK sitting on their desk for lunch and a big hug to boot. Thanks again.
October 24th, 2008 at 7:29 AM
if i want to add some value in action of this file
than what can i do
this not work properly by me
for example id of table in query
October 24th, 2008 at 6:54 PM
There is a issue with microsoft explorer downloading files from
a site using SSL (secure socket layer). In order to get it to work
you must add this additional header:
Header(‘Pragma: public’);
October 27th, 2008 at 2:39 PM
This just dumps to the browser display – no prompt for filesave or anything.
November 11th, 2008 at 7:45 PM
thanks for that code. is there any way to format the cell in csv(ex. from numeric to text)
November 24th, 2008 at 4:27 PM
Thanks a ton!! it was very useful
December 2nd, 2008 at 3:27 PM
How do you export text fields which contain blank lines?
When exporting in windows (IIS), the blank lines are converted into a funny symbol.
Thanks
December 4th, 2008 at 3:40 PM
Can anyone explain how to save the file on the server instead of downloading?? Please help!!
thanks…
December 23rd, 2008 at 6:38 PM
wow its great thank you very much
December 27th, 2008 at 7:52 AM
thank u very much but how can we export multiple table data into bone excel sheet
January 6th, 2009 at 8:19 AM
i have tested the code available in this site..iam using php code..so how to implement this code
for getting the output form of the data that has been stored in the database..that is the whole datastored in the perticular table….
January 28th, 2009 at 11:57 AM
Thanks a ton!! it is very useful.but i have a problem ,i want to export
all tables with data into bone execl sheet.any one can help me??
Thanks
January 28th, 2009 at 11:59 AM
Thanks a ton!! it IS very useful.i have a problem,how can we export multiple table data into bone excel sheet.any one can help me.
Thanks
February 3rd, 2009 at 4:16 PM
Thank you so much for this excellent tutorial – you’ve just made my day much easier!
February 5th, 2009 at 12:35 PM
Hey
Finally i got something to cheer,I was searching the net for 2 hours and i was getting all crap of code snippets.
This works fine for me,and very happy now after two hours of search
thanks a lot
Finally i can relax a bit
thanks again
February 17th, 2009 at 2:48 PM
THANK YOU, THANK YOU!
This was what I was looking for. Downloaded, insterted my db info and I had the “backup” file I needed inless than 15 mis.
Again, Thank You
March 5th, 2009 at 7:53 PM
LAURA SAID:
“Can anyone explain how to save the file on the server instead of downloading?? Please help!!”
Hi Laura,
You have to write to the disk instead of sending info to the browser. What you need to do is to forget about all the commands HEADER(blabla). Take all those off the script. Instead, white the following:
if (!$handle = fopen(‘filename.ext’, ‘x’)) {
echo “Cannot open/create filename.ext”;
exit;
}
if (fwrite($handle, $out) === FALSE) {
echo “Cannot write to filename.ext”;
exit;
}
echo “Success!”;
fclose($handle);
Watch that the $OUT variable used in fwrite() is the CSV data produced by the exportMysqlToCsv() above…
March 13th, 2009 at 1:20 AM
Hello, very good and working code. Im new to that kind of code so forgive me.
I have 3 diferent tables
players:
id_player
name_player
players_details:
id_player
position
age
players_eval:
id_player
value
wage
You can see that the ‘id_player’ is in all players. Now, what i want to do is export on csv and if the ‘id_player’ is the same number with the other tables to continew and show the rest of the info i have on the mysql
Thank you
April 5th, 2009 at 4:06 PM
Thank you so much,
This was really useful!
April 28th, 2009 at 4:39 PM
woot woot! That was so simple, you saved me hours! Much appreciation!
May 25th, 2009 at 9:48 AM
Thank you so much!!
I have a problem about the export function.
I am using UTF-8 coding for programming.
I tried to export the CSV, and open by EXCEL directly, but I can not see the Chinese word.
I need to open the excel first, then import external data and choose the Unicode coding.
Any method let me more easily open the CSV?
(simply double click to open)
June 5th, 2009 at 3:10 AM
[...] View Tutorial No Comment var addthis_pub=”izwan00″; BOOKMARK This entry was posted on Friday, June 5th, 2009 at 7:39 am and is filed under Php Tutorials. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. [...]
June 14th, 2009 at 9:00 AM
When i used the above code i got the text below in the export.csv file, whats the matter…
http://www.prizeitem.com:: Administration Console
function s
June 22nd, 2009 at 5:55 AM
Hi!
Very useful tutorial for me.
July 1st, 2009 at 4:16 PM
Great tutorial! I was able to get it working with just the addition of my MySQL information.
One question – my csv export has some field data that seems to have ‘wrapped’ onto the next field — the table I am querying has over 110 fields. I can export in PHPMyAdmin and the data does not wrap.
Is there somewhere in the above code I can set a file length?
July 1st, 2009 at 5:07 PM
Regarding Post #47 – the issue is data related – text fields that have special characters entered like \@”.
July 9th, 2009 at 8:03 AM
PHP 5 now has fputcsv function that was missing in 4.x releases of PHP. This function should take care of most of the conversion/parsing.
http://www.php.net/fputcsv
July 24th, 2009 at 12:23 PM
Thank you so much, I’ve been through lots of crappy scripts out there before finding your. Works just fine !!
August 19th, 2009 at 11:27 PM
Wonderful. Thank you so much for taking the time to post and explain the script.
September 4th, 2009 at 2:27 PM
Great script it works perfect, I only have 1 question, how can I make the column names Bold.
Thanks
September 16th, 2009 at 7:28 PM
Is there a was to specify custom field names for the export?
Example the field name in the db is ‘order_number’ and I would like the export field name to be ‘Order Num’
Thanks
October 8th, 2009 at 10:30 AM
mind blowing, i have been searching for this for so long. internet is alive because you people like you Gertjan
October 9th, 2009 at 9:46 AM
Bold? There is no text formatting in CSV it’s PLAIN text.
October 13th, 2009 at 3:30 PM
Thankx for the great script. I have one question, how can i write the contents to a file and save it automatically to a disk?
October 31st, 2009 at 3:22 AM
By default the script gets all fields in the table. I wanted to limit what fields where returned. Also, I wanted to have custom field names. This approach achieves both.
Created array $fields_limit. This array holds the field names you want to include in the export and their custom names. Added parameter to function.
$fields_limit = array(
‘first_name’ => ‘First Name’,
‘url’ => ‘Website’
);
exportMysqlToCsv($table,$filename,$fields_limit);
You will need to modify exportcsv.inc.php:
function exportMysqlToCsv($table,$filename = ‘export.csv’,$fields_limit=array())
{
$csv_terminated = “\n”;
$csv_separator = “,”;
$csv_enclosed = ‘”‘;
$csv_escaped = “\\”;
$fields = ”;
if (count($fields_limit) > 0) {
foreach ($fields_limit as $field => $format) {
$fields .= $field . ‘, ‘;
}
$fields = substr($fields, 0, -2); // strip trailing comma
$sql_query = “select {$fields} from $table”;
} else {
$sql_query = “select * from $table”;
}
…
To get custom field names:
for ($i = 0; $i < $fields_cnt; $i++)
{
$field_name = stripslashes(mysql_field_name($result, $i));
$format_field_name = $fields_limit[$field_name];
$l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
$format_field_name) . $csv_enclosed;
$schema_insert .= $l;
$schema_insert .= $csv_separator;
} // end for
November 16th, 2009 at 1:32 PM
Hi, great script.
Is there a way assign a default directory where the file will be saved automatically? meaning without asking the user to save the file where ever they like.
November 17th, 2009 at 4:01 PM
WOW! Worked like a stinking charm. Awesome, thanks!
November 17th, 2009 at 6:56 PM
Thank you! Just what I was looking for!
November 23rd, 2009 at 10:31 AM
This looks great. Nice and simple script and nice explanations too. thanks for posting this tutorial!
December 1st, 2009 at 5:14 PM
Great script! Is it possible to only export the records without the fieldnames?
December 9th, 2009 at 7:00 PM
Great script, save me time, thanks a lot.
December 21st, 2009 at 2:03 PM
Hi,
Script seems to work, but I get the following error:
Warning: Cannot add header information – headers already sent by…
I tried different solutions without luck
Pleeeeze help!!
December 21st, 2009 at 10:56 PM
Great work! This is exactly what I needed.
January 14th, 2010 at 8:59 AM
Hi Gertjan,
The script works great, thanks a ton.
Just wanted to know why you use the following conditional;
if ($csv_enclosed == ”) {
$schema_insert .= $row[$j];
}else {
$schema_insert .= $csv_enclosed . str_replace($csv_enclosed,$csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
}
$csv_enclosed == ” will always return false as $csv_enclosed is set in the begining and never closed?
I’m just interested.
Thanks
Roark
January 14th, 2010 at 9:00 AM
Aplologies, set in the begining and never CHANGED
January 19th, 2010 at 10:03 AM
gr8 script..working perfectly
January 21st, 2010 at 8:08 AM
great, thanks a lot!
January 26th, 2010 at 6:31 AM
Is it possible to export the selected rows? by using checkbox? anyone…??
February 1st, 2010 at 11:06 AM
Hi,
Script seems to work, but I get the following error:
Warning: Cannot add header information – headers already sent by…
I tried different solutions without luck
Pleeeeze help!!
where is the solution?????
February 1st, 2010 at 3:27 PM
Hi guys,
i found some time to post some replies, so here goes:
the header already send error:
This happens when you copy and paste the script from here and leave in some extra whitespace before the tags
Also if you’re using this script in other script, the PHP header() function cannot be called after output has already been given to the browser, it has to be called BEFORE, or use output buffering.
so
test
will produce this error. as the HTTP header MUST be send before outputting something to the browser.
—
@roark
Good observation, it’s a little leftover code from the switch to spaghetticode to function, it can be removed as the variables are set in the top of the function.
@ batotoy
Not sure what you mean by the selected row and a combo box,
but you can redefine the query with a where clause to narrow your export rows.
something like this: $query = “select * from $table where id < 50" would export all rows where the ID column is less then 50
Hope this helps
greetz
February 10th, 2010 at 10:20 AM
Hi there,
excellent tutorial, works like a charm.
one item i’m a bit fuzzy on – the function declaration…
function exportMysqlToCsv($table,$filename=’file.csv’) {
i’m not familiar with the variable being specified and given a value in the declaration – ie, $filename=’file.csv’.
i’m trying to customize the filename somewhat, with the date, but this doesn’t work, it seems – all i get is a file called “$filenamed”. any ideas on how to make this work?
$filenamed = “webentries” . date(Ymj_hi) . “.csv”;
function exportMysqlToCsv($table,$filename=’$filenamed’) {
i have tried with and without the ‘ quotes, but without, i get a message about an unexpected T_VARIABLE.
is there another way to specify this file name perhaps?
February 23rd, 2010 at 4:27 PM
Hi,
This script is great. Is there possibility to export only those rows to .csv that fullfill some conditional expectation?
For example if there are column which tells if some rows are accepted by someone somewhere and the value of those cells in that column are changed 1 (for example) and those which are not accepted are 0. Then i would want to export only those who are accepted (cell value = 1).
February 24th, 2010 at 2:47 PM
Also another question which is bothering me. Is it possible make two different sqlquery which are then written to .csv files so that first querys first row is first row in .csv and second will be from second query (first row of that query) and third is again secod row of the first query and so on?
February 26th, 2010 at 6:13 AM
g8 work.. thanks a lot… how can i get all the tables records from database?
March 9th, 2010 at 12:29 PM
HELP……. PLEASE……
Ii want to export my data from mysql, and this is the case
1. i have table called PO_BBD, then the structure like this
msisdn varchar, calltime=varchar, calldate=varchar and charger = int
| msisdn | calltime | calldate | charger|
———————————————————–
| 005744 | 01:12:00 | 02JAN2010 | 125 |
| 005744 | 12:12:20 | 04JAN2010 | 1 |
| 005744 | 02:10:15 | 10JAN2010 | 125 |
| 001212 | 09:22:15 | 01JAN2010 | 1 |
| 001212 | 10:12:02 | 11JAN2010 | 300 |
———————————————————–
thats my example data, i have 300 record. every msisdn have +- 30 record
my questions
1. how can i export to csv with condition every msisdn have its record
2. i want otomatically loading and save, like this
05744.csv
001212.csv
and if i open that file(05744.csv or 001212.csv) in notepad i want like this
msisdn calltime calldate charger
“005744″,”01:12:00″,”02JAN2010″, 125
“005744″,”12:12:20″, “04JAN2010″, 1
Please…… Help me…………..
March 9th, 2010 at 12:31 PM
HELP……. PLEASE……
I want to export my data from mysql, and this is the case
1. i have table called PO_BBD, then the structure like this
msisdn varchar, calltime=varchar, calldate=varchar and charger = int
| msisdn | calltime | calldate | charger|
———————————————————–
| 005744 | 01:12:00 | 02JAN2010 | 125 |
| 005744 | 12:12:20 | 04JAN2010 | 1 |
| 005744 | 02:10:15 | 10JAN2010 | 125 |
| 001212 | 09:22:15 | 01JAN2010 | 1 |
| 001212 | 10:12:02 | 11JAN2010 | 300 |
———————————————————–
thats my example data, i have 300 record. every msisdn have +- 30 record
my questions
1. how can i export to csv with condition every msisdn have its record
2. i want otomatically loading and save, like this
005744.csv
001212.csv
and if i open that file(005744.csv or 001212.csv) in notepad i want like this
“msisdn”,”calltime”,”calldate”,”charger”
“005744″,”01:12:00″,”02JAN2010″, 125
“005744″,”12:12:20″, “04JAN2010″, 1
Please…… Help me…………..
March 11th, 2010 at 3:44 PM
@Tomo
Can you provide a plain text version of this code in its entirety? I would greatly appreciate it. There seems to be problems with copy/pasting this. Thanks.
March 16th, 2010 at 3:13 PM
I have a problem using this code….the web can show me the output…but i cannot find any csv file…/.\
March 22nd, 2010 at 9:17 PM
A great little script and much appreciated.
Can easily be enhanced by including prompts for the database and table.
A genuine utility.
March 27th, 2010 at 7:45 PM
Great script it gives me the output, but the files is not saved. Of course like always, I am the only one, please help
April 5th, 2010 at 10:52 PM
A great little script and much appreciated, I’ll try to make it work on my website
April 10th, 2010 at 6:18 AM
Thank you.
April 14th, 2010 at 4:30 PM
Is there a way that I can export images that are stored in the database to show on the spreadsheet. Each one of our products has a color code and I would like to be able to pull that color code image from the database and have it show beside the product on the spreadsheet.
Thank You
April 17th, 2010 at 6:57 PM
OOOHHHH!
What a relief. With newbie skills I’ve been trying to clean up and export stuff. Thanx to you Gertjan, my weekend is saved!
*RESPECT*
April 28th, 2010 at 4:58 PM
good job bro, many thanx to your tut
April 30th, 2010 at 11:05 AM
Good skills, just a lovely “Fist” typo at the top!
FIST!
May 6th, 2010 at 10:12 PM
Great work …
I need to save the csv file after creation into a folder.. please give me sample to do that ..
Appreciated
May 19th, 2010 at 11:36 PM
it works fine if I use scalc (openoffice) to test the output file
,
but if I use my old-school-2003 excel to test the .csv output file,….no way!….
….commas are not recognized as separators
May 19th, 2010 at 11:49 PM
@Robin
where do you want to save it, on the server or on a client computer?
May 20th, 2010 at 1:17 AM
@bell418
…here is a solution if you want to put the file on your server in a folder:
in exportcsv.inc.php take all the header-stuff away, also take away echo $out; and exit;
…then add the folowing:
$filename = ‘theway/tomyfolder/’.$filename; // of course you change theway/tomyfolder/
if (!$handle = fopen($filename, ‘w’)) {
echo “Impossible to open file : $filename”;
}
if (fwrite($handle, $out) === FALSE) {
echo “Impossible to write to file : $filename”;
}
fclose($handle);
….an empty page will show-up, but the csv file is on your server where you want it to be
this is only a sample!
June 8th, 2010 at 1:13 PM
I’m trying to implement this function to run from a page within a WordPress website – basically a user who is viewing the DB table on the page has the option to click an export button to export the data to a CSV.
The function is getting the data from the DB table fine, but is displaying it on the page rather than exporting to a CSV file. Does anyone know what is causing this, and how I can fix it?
Thanks,
Matt
June 11th, 2010 at 6:21 PM
I am a noob at this, should the two parts on top be in a file called exportcsv.inc.php? is is it two file and if so what do i call the first one?
June 22nd, 2010 at 10:48 AM
this code is excellent,
it really works
i want to add a feature more
when i click in checkbox and select i data in the , i need just to export the seleted data
how to
means whichever data selected by checking in checkbox only that particular datas should be exported to excel or csv
June 23rd, 2010 at 7:24 AM
Hi
Works a treat but I am missing the 0 off the front of the Telephone number column.
Any ideas?
June 29th, 2010 at 10:29 PM
Gr8 script…thanks so much for saving our time……I can get the .csv file when the query is “select * from table” but my problem is that I want to get an array from the previous page and get the .csv file according to that query; my query would be:
“select * FROM `portal` WHERE groups IN ($list)”. $ list is my array of values coming from previous page from a selectbox.
Any help would be greatly appreciated.
July 1st, 2010 at 4:46 PM
@Ash Glover
Had to do the same thing, here is what works.
in, exportcsv.inc.php edit this,
<?php
function exportMysqlToCsv($table)
{
$today = date("F_j,_Y,_g:ia");
$filename = "Info_Request__".($today).".csv";
July 9th, 2010 at 1:25 PM
Nice work but what about multiple line inside one cell? I tried but not worked! Any suggestion?
August 10th, 2010 at 5:57 AM
This was so awesome. You my friend know how to do tutorials well. A straight-out-of-the-box solution for the coding-challenged.
August 17th, 2010 at 4:28 PM
Hi for some reason IE does not want to download the file. I have tried in FF and Chrome and both worked.
IE just pops up an error :
“Unable to download statsDisplay.php form testarea.mydomain.com
Unable to open this internet site.”
Is there a specific option to choose from the headers?
// header(“Content-type: text/x-csv”);
//header(“Content-type: text/csv”);
//header(“Content-type: application/csv”);
i have added an if statement to check if i’m using IE and then to have another header uncommented.
Content-type: text/csv works for FF and Chrome but none of the others work for IE.
Any Ideas?
Thanks for the script so far.
August 18th, 2010 at 12:37 PM
wow, thx!
works fine for me.
September 20th, 2010 at 6:05 PM
Why not using fputcsv PHP function? is this method safer?
October 29th, 2010 at 4:47 PM
Hello,
I used the function (with some changes) all was working as it should on a windows xp system, however not on a windows 7.
function exportMysqlToCsv($filename = ‘Upload.csv’)
{
$csv_terminated = “\n”;
$csv_separator = “,”;
$csv_enclosed = ‘”‘;
$csv_escaped = “\\”;
$fields_cnt = 5;
$schema_insert = ”;
for ($i = 0; $i < $fields_cnt; $i++)
{
if($i=="0"){$temp="column 1";}
if($i=="1"){$temp="column 2";}
if($i=="2"){$temp="column 3";}
if($i=="3"){$temp="column 4";}
if($i=="4"){$temp="column 5";}
$l = $csv_enclosed . $temp . $csv_enclosed;
$schema_insert .= $l;
$schema_insert .= $csv_separator;
} // end for
$out = trim(substr($schema_insert, 0, -1));
$out .= $csv_terminated;
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=$filename");
echo $out;
exit;
}
Does somebody knows how to solve this problem ???
Thanks in advance, John
October 29th, 2010 at 10:51 PM
@John
I, John, solved the problem it was a language isue, I changed Control Panel>Regional and
Language options to English.
November 9th, 2010 at 1:39 PM
Hi
Im using ur code but im getting dis error msgs.
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /home/omangolf/public_html/admin/DownloadDetails.php on line 67
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/omangolf/public_html/admin/DownloadDetails.php on line 84
Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 115
Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 116
Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 118
Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 121
December 6th, 2010 at 9:45 AM
@John
install office;)
January 17th, 2011 at 4:51 AM
Thanks, this was exactly what I was looking for.
January 18th, 2011 at 6:11 AM
Thank’s man it’s work
February 8th, 2011 at 7:33 AM
Worked Perfectly! And it was just what I needed. Thank you so much for sharing- I just “bought” you a beer- wish I could afford more, I’m hoping that with enough “beers” you can pay for your time
February 13th, 2011 at 6:54 AM
This is a great post it really help me finish up my final year project at school. I also learnt PHP amd Mysql at Tutorial arena
March 7th, 2011 at 9:43 AM
This code is working perfectly with little changes which are mandatory. Itz cool try………
April 7th, 2011 at 9:20 PM
Is there a way to make one html button calling this php function export 2 csv files from 2 separate tables with one click? Or do they have to be separate button.
April 13th, 2011 at 1:41 PM
Hi. Excellent script and has helped out more than I could ever say. Thanks.
One thing I’m trying to do is relating to JT’s post on October 31st, 2009 at 3:22 where I’d like to change each column heading to begin with v_
so something like product_id column heading would save as v_product_id and product_ model column heading would output v_product_model column heading and so on.
I’ve tried JT’s example but it errors for me.
Please could someone help me to get v_ at the start of each column heading?
Thank you.
April 25th, 2011 at 4:25 AM
thanks for this!!!
May 9th, 2011 at 7:29 PM
Thanks! That was quick and painless. I like that.
May 11th, 2011 at 7:06 PM
Excellent. I changed all the database connection and queries to fit my own database class. Everything works superb. Final output was perfect. Thank you for the code.
May 23rd, 2011 at 7:29 PM
Thanks!
I used your code as a part of complete example package about manipulating MySql database with PHP: http://www.sanaracreations.fi/web/an-example-of-manipulating-mysql-database-with-php-and-downloading-it-as-csv
June 1st, 2011 at 11:31 AM
Thanks! its great;
If want to search my list and export only search result then what can i do…
June 5th, 2011 at 3:40 PM
Thank you very much, you just made my day
June 16th, 2011 at 3:56 AM
Same basic question as Shamim: Works great to dump all records in table, but how can I substitute a query result for the $table var? Thank you for your great contribution in this site!
June 17th, 2011 at 3:32 PM
Congrats.
Your code works perfect to me…. thx a lot
July 5th, 2011 at 7:59 PM
Great Work. Worked for me too.
July 12th, 2011 at 8:48 AM
great article indeed. used and got it working in the first shot. excellent work. thank you.
and God bless.
July 16th, 2011 at 12:43 PM
Good Work… Thanks !
August 24th, 2011 at 6:22 AM
You actually make it appear really easy along with your presentation however I find this matter to be actually one thing that I think I might never understand. It sort of feels too complex and very broad for me. I am looking forward to your next put up, I’ll attempt to get the grasp of it!
August 24th, 2011 at 6:28 AM
you’re truly a just right webmaster. The site loading pace is incredible. It kind of feels that you are doing any unique trick. In addition, The contents are masterpiece. you have performed a magnificent task in this matter!
September 29th, 2011 at 9:42 PM
When i use this, the first row is empty and then the column names show up on row 2 and the results below that… what should i do to get rid of the empty first row (sorry if i missed this somewhere).
December 8th, 2011 at 5:39 PM
thanks dear…. useful script 4 me..
January 29th, 2012 at 2:40 AM
Thank you for your post.Really thank you! Fantastic.