PHP and MySQL: Export a Question to a Tab Delimited or CSV File

This weekend, I wished to construct a PHP web page that may again up any MySQL question or desk right into a Tab Delimited file. A lot of the examples out on the web have the columns hard-coded.

In my case, I wished the columns to be dynamic, so I needed to first loop by all of the desk discipline names to construct the header row with column names after which loop by all of the data for the remaining information rows. I additionally set the header in order that the browser will provoke the file obtain within the filetype (txt) with the title of the file date and timestamped.

Tab Delimited Export From MySQL in PHP

<?php
$at this time = date("YmdHi");
header("Content material-type: software/octet-stream");
header("Content material-Disposition: attachment; filename="".$at this time."_Backup.txt"");
$conn = new mysqli("hostname", "username", "password", "database_name"); // Substitute together with your database credentials

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$question = "SELECT * FROM `mytable` ORDER BY `myorder`";
$outcome = $conn->question($question);

if ($result->num_rows > 0) {
    $fields = $result->fetch_fields();
    
    // Put together the header row
    $header = [];
    foreach ($fields as $discipline) {
        $header[] = $field->title;
    }
    $information = implode("t", $header) . "n";

    // Fetch and course of the information rows
    whereas ($row = $result->fetch_assoc()) {
        $rowValues = [];
        foreach ($fields as $discipline) {
            $rowValues[] = $row[$field->name];
        }
        $information .= implode("t", $rowValues) . "n";
    }

    // Output the information
    echo $information;
} else {
    echo "No information discovered";
}

// Shut the database connection
$conn->shut();
?>

Let’s stroll by the code step-by-step with explanations for every half:

<?php
// Get the present date and time in a particular format
$at this time = date("YmdHi");

// Set HTTP headers for file obtain
header("Content material-type: software/octet-stream");
header("Content material-Disposition: attachment; filename="".$at this time."_Backup.txt"");

// Create a MySQL database connection
$conn = new mysqli("hostname", "username", "password", "database_name"); // Substitute together with your database credentials

// Test if the database connection was profitable
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

• We generate the present date and time within the “YmdHi” format and retailer it within the $at this time variable.
• HTTP headers are set to specify that the content material ought to be handled as an octet-stream (binary information) and set off a file obtain with the required filename.
• Utilizing the extension, we create a MySQL database connection, changing placeholders together with your precise database credentials.
• We test if the database connection was profitable. We terminate the script and show an error message if there’s an error.

// Outline the SQL question to pick out information from the `mytable` desk
$question = "SELECT * FROM `mytable` ORDER BY `myorder`";

// Execute the SQL question
$outcome = $conn->question($question);

// Test if there are any rows returned
if ($result->num_rows > 0) {
    // Fetch the sphere (column) names
    $fields = $result->fetch_fields();

    // Put together the header row for the export file
    $header = [];
    foreach ($fields as $discipline) {
        $header[] = $field->title;
    }
    $information = implode("t", $header) . "n";

• We outline the SQL question to pick out all information from the mytable desk, ordering it by the myorder column.
• The question is executed, and the result’s saved within the $outcome variable.
• We test if there are any rows returned by inspecting the num_rows property of the outcome object.
• We use fetch_fields() to retrieve the sphere (column) names and retailer them within the $fields array.
• The header row for the export file is ready by looping by the sphere names and concatenating them with tabs.

    // Fetch and course of the information rows
    whereas ($row = $result->fetch_assoc()) {
        $rowValues = [];
        foreach ($fields as $discipline) {
            $rowValues[] = $row[$field->name];
        }
        $information .= implode("t", $rowValues) . "n";
    }

• We use a whereas loop to fetch every information row from the outcome set utilizing fetch_assoc().
• Contained in the loop, we put together the values of every row by iterating by the fields and accumulating the corresponding information.
• The values for every row are concatenated with tabs to create a tab-delimited row, and this row is added to the $information variable.

    // Output the information to the browser
    echo $information;
} else {
    // If no information is discovered, show a message
    echo "No information discovered";
}

// Shut the MySQL database connection
$conn->shut();
?>

• If there may be information discovered (checked with num_rows), we echo the concatenated information, which is the content material of the export file. This triggers the file obtain within the consumer’s browser.
• If no information is discovered, we show a message indicating that no information is offered.
• We shut the MySQL database connection utilizing $conn->shut() to launch sources.

This code effectively exports information from a MySQL database desk right into a tab-delimited textual content file and handles varied eventualities, similar to database connection errors and empty outcome units.

Comma-Separated Values Export From MySQL in PHP

I can modify the code to export information as a CSV file. Right here’s the code, up to date for CSV export:

<?php
// Get the present date and time in a particular format
$at this time = date("YmdHi");

// Set HTTP headers for file obtain
header("Content material-type: textual content/csv");
header("Content material-Disposition: attachment; filename="".$at this time."_Backup.csv"");

// Create a MySQL database connection
$conn = new mysqli("hostname", "username", "password", "database_name"); // Substitute together with your database credentials

// Test if the database connection was profitable
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Outline the SQL question to pick out information from the `mytable` desk
$question = "SELECT * FROM `mytable` ORDER BY `myorder`";

// Execute the SQL question
$outcome = $conn->question($question);

// Test if there are any rows returned
if ($result->num_rows > 0) {
    // Put together the output file deal with for writing
    $output = fopen('php://output', 'w');

    // Fetch and course of the information rows
    whereas ($row = $result->fetch_assoc()) {
        // Output every row as a CSV line
        fputcsv($output, $row);
    }

    // Shut the output file deal with
    fclose($output);
} else {
    // If no information is discovered, show a message
    echo "No information discovered";
}

// Shut the MySQL database connection
$conn->shut();
?>

On this modified code:

• The headers for the HTTP response are up to date to specify a textual content/csv content material kind, and the filename has the “.csv” extension.
• As a substitute of manually creating the CSV content material, we use the fputcsv perform to output every row from the MySQL outcome set as a CSV line. This perform handles the CSV formatting for you, together with dealing with particular characters and enclosing fields in double quotes when vital.
• We open the output file deal with utilizing fopen with ‘php://output’ because the file title. This permits us to jot down on to the HTTP response output stream.
• The code is structured to deal with CSV export effectively and closes the file deal with when performed.

This code will export the information from the MySQL desk as a CSV file, making it straightforward for customers to open and work with in spreadsheet purposes like Excel. Don’t overlook to interchange the database credentials with your individual.