Exporting from MySQL to client

Anon

Hello, I was wondering how can I export data from a mysql table to a csv file in the client machine (instead of putting it in a file in the server).

If this not possible, is it possible to show CSV data in the browser?

Both are for a user interface, so I cant do this manually with (let´s say) phpmyadmin.

Thank you

Anon

By manipulating the headers, you can cause the
client browser to want to save the incoming data
to disk:

  header("Content-type: application/octet-stream");
  // optionally, tell the prowser the default filename
  // (the user can,of course, still change it in the
  // save-as dialog box):
  header("Content-Disposition: attachment; ilename=my_csv.txt");

Then just write your csv data. Be sure you don't output anything else,
as it, too, would end up in the saved csv file.

Anon

I believe phpMyAdmin will do that for you. It gives you the option to download a database dump directly from your browser. I'm not sure if it exports csv but it is a quick and easy way to export a db dump.

http://phpwizard.net/projects/phpMyAdmin/

Jamie

Anon

O, thats great , I didnt know this.
Thank you.
So I dont need to use select into outfile with this.
Do I need to write the output of the query as a the CSV file to the browser manually?
I would normally write it in a table, but this wont be useful to the user.

Thank you!

monikan

but how do i write the query results into a cvs file?

jasper

Send some headers:
header( "Content-type: application/vnd.ms-excel" );
header( "Content-Disposition: attachment; filename=myoutput.xls" );
header( "Content-Description: Excel output" );

Then loop over your data, in pseudocode:
while( $result )
{
echo "$field1\t$field2\t$field3\n";
}

of course you can also use other delimiters, but Excel understands tab's immediately.

monikan

thank you so much , but the problem is that i alreadyu have headers set in a included file, so when i set these headers i get errors !! tell me what to do !!!

jasper

Create a different script and use 1) a clickable link in the browser or 2) use "header( "Location: http://xxxx/cvs.php" );" to go to the CVS-script.

monikan

it doesn't help...even if i create a new file ...coZ the headers are included intially in the begining of this fiel, so when the person clicks on the link, it tries to include the headers again, which again cause the same error !!!!

jasper

If you got all your layout, session and database-includes in one file that's too bad. Do you call any functions from that include to generate layout or sessions before the code is started, or are these not functions but direct executed lines in your include?

monikan

ya the included file has some kind of headee displayed on the htm page wich include the various menu options !!!

jasper

Hmmm, I recommend pulling apart that single include in several like layout, session, database etc. but that's too late now I guess 🙂 A somewhat easy solution would be not to call that HTML-menu directly in the include, but to put it in a separate function. Then use UltraEdit or something alike to do a search&replace in all files... Could be a 1-minute job. Otherwise, I guess your stuck.

MarkB423

Is this a situation where ob_start(); and ob_end_flush(); would solve your problem?

monikan

Hey..its a predesigned site and i am just coding for the download option. So i cannot make anymore changes to the exsiting design .

Pls give a way out !!!

MarkB423

If you had more control, you could put ob_start(); as the very first line of code in the document, i.e.

Ln#
1------- <?php
2------- ob_start();

and then place ob_end_flush(); at the very end of the document.

Other than that, you are in for a rough time if you can't get them to place a PHP include file at the very top of the page for you...

Ln#
1------- <?php include 'file.inc.php' ?>

MarkB423

I should mention that the file.inc.php file should have <?php ob_start(); on the very first and second lines like in the first example.