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 the myorder 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 using fetch_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.