
PHP and MySQL: Export a Query to a Tab Delimited or CSV File
This weekend, I wanted to build a PHP page that would back up any MySQL 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.
Tab Delimited Export From MySQL in PHP
<?php
$today = date("YmdHi");
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"".$today."_Backup.txt\"");
$conn = new mysqli("hostname", "username", "password", "database_name"); // Replace with your database credentials
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$query = "SELECT * FROM `mytable` ORDER BY `myorder`";
$result = $conn->query($query);
if ($result->num_rows > 0) {
$fields = $result->fetch_fields();
// Prepare the header row
$header = [];
foreach ($fields as $field) {
$header[] = $field->name;
}
$data = implode("\t", $header) . "\n";
// Fetch and process the data rows
while ($row = $result->fetch_assoc()) {
$rowValues = [];
foreach ($fields as $field) {
$rowValues[] = $row[$field->name];
}
$data .= implode("\t", $rowValues) . "\n";
}
// Output the data
echo $data;
} else {
echo "No data found";
}
// Close the database connection
$conn->close();
?>
Let’s walk through the code step by step with explanations for each part:
<?php
// Get the current date and time in a specific format
$today = date("YmdHi");
// Set HTTP headers for file download
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"".$today."_Backup.txt\"");
// Create a MySQL database connection
$conn = new mysqli("hostname", "username", "password", "database_name"); // Replace with your database credentials
// Check if the database connection was successful
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
- We generate the current date and time in the “YmdHi” format and store it in the
$today
variable. - HTTP headers are set to specify that the content should be treated as an octet-stream (binary data) and trigger a file download with the specified filename.
- Using the extension, we create a MySQL database connection, replacing placeholders with your actual database credentials.
- We check if the database connection was successful. We terminate the script and display an error message if there’s an error.
// Define the SQL query to select data from the `mytable` table
$query = "SELECT * FROM `mytable` ORDER BY `myorder`";
// Execute the SQL query
$result = $conn->query($query);
// Check if there are any rows returned
if ($result->num_rows > 0) {
// Fetch the field (column) names
$fields = $result->fetch_fields();
// Prepare the header row for the export file
$header = [];
foreach ($fields as $field) {
$header[] = $field->name;
}
$data = implode("\t", $header) . "\n";
- We define the SQL query to select all data from the
mytable
table, ordering it by themyorder
column. - The query is executed, and the result is stored in the
$result
variable. - We check if there are any rows returned by examining the
num_rows
property of the result object. - We use
fetch_fields()
to retrieve the field (column) names and store them in the$fields
array. - The header row for the export file is prepared by looping through the field names and concatenating them with tabs.
// Fetch and process the data rows
while ($row = $result->fetch_assoc()) {
$rowValues = [];
foreach ($fields as $field) {
$rowValues[] = $row[$field->name];
}
$data .= implode("\t", $rowValues) . "\n";
}
- We use a
while
loop to fetch each data row from the result set usingfetch_assoc()
. - Inside the loop, we prepare the values of each row by iterating through the fields and collecting the corresponding data.
- The values for each row are concatenated with tabs to create a tab-delimited row, and this row is added to the
$data
variable.
// Output the data to the browser
echo $data;
} else {
// If no data is found, display a message
echo "No data found";
}
// Close the MySQL database connection
$conn->close();
?>
- If there is data found (checked with
num_rows
), we echo the concatenated data, which is the content of the export file. This triggers the file download in the user’s browser. - If no data is found, we display a message indicating that no data is available.
- We close the MySQL database connection using
$conn->close()
to release resources.
This code efficiently exports data from a MySQL database table into a tab-delimited text file and handles various scenarios, such as database connection errors and empty result sets.
Comma-Separated Values Export From MySQL in PHP
I can modify the code to export data as a CSV file. Here’s the code, updated for CSV export:
<?php
// Get the current date and time in a specific format
$today = date("YmdHi");
// Set HTTP headers for file download
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=\"".$today."_Backup.csv\"");
// Create a MySQL database connection
$conn = new mysqli("hostname", "username", "password", "database_name"); // Replace with your database credentials
// Check if the database connection was successful
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Define the SQL query to select data from the `mytable` table
$query = "SELECT * FROM `mytable` ORDER BY `myorder`";
// Execute the SQL query
$result = $conn->query($query);
// Check if there are any rows returned
if ($result->num_rows > 0) {
// Prepare the output file handle for writing
$output = fopen('php://output', 'w');
// Fetch and process the data rows
while ($row = $result->fetch_assoc()) {
// Output each row as a CSV line
fputcsv($output, $row);
}
// Close the output file handle
fclose($output);
} else {
// If no data is found, display a message
echo "No data found";
}
// Close the MySQL database connection
$conn->close();
?>
In this modified code:
- The headers for the HTTP response are updated to specify a
text/csv
content type, and the filename has the “.csv” extension. - Instead of manually creating the CSV content, we use the
fputcsv
function to output each row from the MySQL result set as a CSV line. This function handles the CSV formatting for you, including handling special characters and enclosing fields in double quotes when necessary. - We open the output file handle using
fopen
with ‘php://output’ as the file name. This allows us to write directly to the HTTP response output stream. - The code is structured to handle CSV export efficiently and closes the file handle when done.
This code will export the data from the MySQL table as a CSV file, making it easy for users to open and work with in spreadsheet applications like Excel. Don’t forget to replace the database credentials with your own.
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'
?
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!
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 🙂
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?
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)
I’ll be building that one in PHP within the week, Noah! Bloid is a MySQL Monster! Thanks for all the input!!!
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…
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!
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!
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…
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!
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
Please can someone tells me how to remove the http header row above the header titles