PHP and MySQL: Export a Query to a Tab Delimited File
Douglas Karr at 3:06 pmThanks for stopping by my personal blog on Marketing Technology! Over 50,000 visitors a month find my content worth returning for, so don't forget to subscribe to the Marketing Technology Blog RSS feed or to the Marketing Technology Email to have new content sent directly to your inbox. You may also find my other business blog helpful, Social Media Domination.
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.
SELECT `mytable` order by `myorder`
INTO OUTFILE '/tmp/Backup.txt'
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
?
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!
Glad to point out other directions and new things though
You won’t be able to do that if the mysql server doesn’t have access to the filesystem of course…
mysqlimport database_name --local backup.txtOr 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)
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…
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!
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!