Technology

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.

14 Comments

  1. 1

    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'

    ?

    • 2

      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!

      • 3

        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 🙂

      • 4

        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…

  2. 5

    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?

    • 6

      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)

    • 7
  3. 8

    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…

  4. 9

    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!

    • 10

      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!

  5. 11

    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…

  6. 12

    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!

  7. 13

    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

  8. 14

Leave a Reply