PHP and MySQL: Export a Query to a Tab Delimited File

This weekend I wanted to build a page that would simply backup any query or table into a Tab Delimited file. Most of the examples out on the net have the columns hard-coded.

In my case, I wanted the columns to be dynamic so I had to first loop through all the table field names to build the header row with column names, and then loop through all the records for the remaining data rows. I also set the header so that the browser will initiate the file download in the filetype (txt) with the name of the file date and timestamped.

I left out the database open and closing connection, but here’s the resulting code that worked quite well:
$today = date("YmdHi"); header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=\"".$today."_Backup.txt\""); $query = "SELECT * FROM `mytable` order by `myorder`"; $result = mysql_query($query); $count = mysql_num_rows($result); $fields= mysql_num_fields($result); $data = ""; for ($i=0; $i > $fields; $i++) { $field = mysql_fetch_field($result, $i); $data .= $field->name; $data .= "\t"; } $data .= "\n"; while ($row=mysql_fetch_row($result)) { for($x=0; $x > $fields; $x++) { $field->name=$row[$x]; $data .= $field->name = $row[$x]; $data .= "\t"; } $data .= "\n"; } echo $data;

The code can be easily modified for comma separated values as well.

http://www.dzone.com bloid

Can’t you just do:

SELECT `mytable` order by `myorder` INTO OUTFILE '/tmp/Backup.txt' FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'

?

http://marketingtechblog.com Douglas Karr

I suppose you could!

In this case, I was actually building a ‘backup’ link in a web application, so the PHP functionality is what I needed. However, I never knew you could also write to a file directly from the MySQL statement. Very cool!

Thanks!

http://www.dzone.com bloid

Your way would of course be the best way if the MySQL server is on a remote machine, as it probably wouldn’t be able to write onto the machine where PHP is running

Glad to point out other directions and new things though
K

But you could just run the query to a file, and just redirect the browser to the generated file, or use PHP’s “readfile” if all else fails?

You won’t be able to do that if the mysql server doesn’t have access to the filesystem of course…

http://indylance.com Noah

Great post. Do you know of an easy, free/open source method of importing/restoring a tab-delimited file (like you just created) back into the mysql db?

http://www.dzone.com bloid

Errr… mysqlimport?

mysqlimport database_name --local backup.txt

Or with the SQL command:

LOAD DATA LOCAL INFILE 'backup.txt' INTO TABLE `my_table` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

With mysqlimport, the filename has to match the table name (just something to watch out for)
http://marketingtechblog.com Douglas Karr

I’ll be building that one in PHP within the week, Noah! Bloid is a MySQL Monster! Thanks for all the input!!!

Joe Richards

I just lost more than 6 hours of my life trying to work out why Internet Explorer 6/7 was applying filetype ‘html’ and and not accepting my custom filenames specified in the headers.. and also not allowing files to be saved.. when attempting to have users download text files created in a similar way to above.

I was using HTTPS and IE does not cache these files.

I found the solution on a comment by Brandon K at http://uk.php.net/header.

He says:

—–
I just lost six hours of my life trying to use the following method to send a PDF file via PHP to Internet Explorer 6:

When using SSL, Internet Explorer will prompt with the Open / Save dialog, but then says “The file is currently unavailable or cannot be found. Please try again later.” After much searching I became aware of the following MSKB Article titled “Internet Explorer file downloads over SSL do not work with the cache control headers” (KBID: 323308)

PHP.INI by default uses a setting: session.cache_limiter = nocache which modifies Content-Cache and Pragma headers to include “nocache” options. You can eliminate the IE error by changing “nocache” to “public” or “private” in PHP.INI — This will change the Content-Cache header as well as completely remove the Pragma header. If you cannot or do not want to modify PHP.INI for a site-wide fix, you can send the following two headers to overwrite defaults:

You will still need to set the content headers as listed above for this to work. Please note this problem ONLY effects Internet Explorer, while Firefox does not exhibit this flawed behavior.
—-

Well.. at least he only lost 6 hours…
newbie@this

This works well. However, I just get everything on one line separated by a space. I am trying to modify it to print everything on a separate line like this:

Column1_name
Field1_value
Column2_name
Field1_value
Column3_name
Field1_value

Column1_name
Field2_value
Column2_name
Field2_value
Column3_name
Field2_value

For example:

Name
Mike
Location
Work
Number
1

Name
Sue
Location
Home
Number
2

Name
John
Location
Travel
Number
10

and so on. Can this script be modified to do it?
Thanks!

Polyglot

Sure can.

Try something like this:

SELECT * from MyTableName INTO OUTFILE ‘MyTableName_MySQL-TAB-DELIMITED-29JUN08.txt’ FIELDS TERMINATED BY ‘\n’ LINES TERMINATED BY ‘\n’;

If you want a double space (two empty lines) between record groups, just say “LINES TERMINATED BY ‘\n\n’;” instead.

The “FIELDS TERMINATED BY ‘\n’” portion is what puts a newline after each record, instead of a tab. A tab would be ‘\t’ instead.

Maranatha!

Francisco Caamano

this is defiantly a great post, I tried it and work great, the only thing is that my txt file has an extra row above the header titles, and some results are been separated in 2 rows, this may be cause by the data I have in my Database no idea, but this is a great help to build feeds…
Joseph Raymond Umali

Douglas Karr your code really rocks! It's very useful specially if you just need an output that is in textfile format. Thanks a lot! From team Philippines!
Jr . Umali

hey there! Is there any one from here that can give me a hint about importing a textfile to my database(phpmyAdmin) using my php as my front end. I have an idea about downloading a file and opening it, my problem is that how I can get the row result and how to insert it in my tables, thanks
Jimmy Omolaja

Please can someone tells me how to remove the http header row above the header titles

Marketing Technology Blog

PHP and MySQL: Export a Query to a Tab Delimited File

About Douglas Karr