This weekend, I wished to construct a PHP web page that will 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 via all of the desk discipline names to construct the header row with column names after which loop via all of the information for the remaining knowledge 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
$in the present day = date("YmdHi");
header("Content material-type: utility/octet-stream");
header("Content material-Disposition: attachment; filename="".$in the present day."_Backup.txt"");
$conn = new mysqli("hostname", "username", "password", "database_name"); // Change along with your database credentials
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$question = "SELECT * FROM `mytable` ORDER BY `myorder`";
$end result = $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;
}
$knowledge = implode("t", $header) . "n";
// Fetch and course of the info rows
whereas ($row = $result->fetch_assoc()) {
$rowValues = [];
foreach ($fields as $discipline) {
$rowValues[] = $row[$field->name];
}
$knowledge .= implode("t", $rowValues) . "n";
}
// Output the info
echo $knowledge;
} else {
echo "No knowledge discovered";
}
// Shut the database connection
$conn->shut();
?>
Let’s stroll via the code step-by-step with explanations for every half:
<?php
// Get the present date and time in a particular format
$in the present day = date("YmdHi");
// Set HTTP headers for file obtain
header("Content material-type: utility/octet-stream");
header("Content material-Disposition: attachment; filename="".$in the present day."_Backup.txt"");
// Create a MySQL database connection
$conn = new mysqli("hostname", "username", "password", "database_name"); // Change along with your database credentials
// Examine 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
$in the present day
variable. - HTTP headers are set to specify that the content material ought to be handled as an octet-stream (binary knowledge) and set off a file obtain with the required filename.
- Utilizing the extension, we create a MySQL database connection, changing placeholders along with your precise database credentials.
- We verify 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 knowledge from the `mytable` desk
$question = "SELECT * FROM `mytable` ORDER BY `myorder`";
// Execute the SQL question
$end result = $conn->question($question);
// Examine if there are any rows returned
if ($result->num_rows > 0) {
// Fetch the sector (column) names
$fields = $result->fetch_fields();
// Put together the header row for the export file
$header = [];
foreach ($fields as $discipline) {
$header[] = $field->title;
}
$knowledge = implode("t", $header) . "n";
- We outline the SQL question to pick all knowledge from the
mytable
desk, ordering it by themyorder
column. - The question is executed, and the result’s saved within the
$end result
variable. - We verify if there are any rows returned by analyzing the
num_rows
property of the end result object. - We use
fetch_fields()
to retrieve the sector (column) names and retailer them within the$fields
array. - The header row for the export file is ready by looping via the sector names and concatenating them with tabs.
// Fetch and course of the info rows
whereas ($row = $result->fetch_assoc()) {
$rowValues = [];
foreach ($fields as $discipline) {
$rowValues[] = $row[$field->name];
}
$knowledge .= implode("t", $rowValues) . "n";
}
- We use a
whereas
loop to fetch every knowledge row from the end result set utilizingfetch_assoc()
. - Contained in the loop, we put together the values of every row by iterating via the fields and gathering the corresponding knowledge.
- The values for every row are concatenated with tabs to create a tab-delimited row, and this row is added to the
$knowledge
variable.
// Output the info to the browser
echo $knowledge;
} else {
// If no knowledge is discovered, show a message
echo "No knowledge discovered";
}
// Shut the MySQL database connection
$conn->shut();
?>
- If there may be knowledge discovered (checked with
num_rows
), we echo the concatenated knowledge, which is the content material of the export file. This triggers the file obtain within the person’s browser. - If no knowledge is discovered, we show a message indicating that no knowledge is on the market.
- We shut the MySQL database connection utilizing
$conn->shut()
to launch sources.
This code effectively exports knowledge from a MySQL database desk right into a tab-delimited textual content file and handles varied eventualities, reminiscent of database connection errors and empty end result units.
Comma-Separated Values Export From MySQL in PHP
I can modify the code to export knowledge 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
$in the present day = date("YmdHi");
// Set HTTP headers for file obtain
header("Content material-type: textual content/csv");
header("Content material-Disposition: attachment; filename="".$in the present day."_Backup.csv"");
// Create a MySQL database connection
$conn = new mysqli("hostname", "username", "password", "database_name"); // Change along with your database credentials
// Examine if the database connection was profitable
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Outline the SQL question to pick knowledge from the `mytable` desk
$question = "SELECT * FROM `mytable` ORDER BY `myorder`";
// Execute the SQL question
$end result = $conn->question($question);
// Examine 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 info 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 knowledge is discovered, show a message
echo "No knowledge 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 sort, and the filename has the “.csv” extension. - As an alternative of manually creating the CSV content material, we use the
fputcsv
perform to output every row from the MySQL end result 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 obligatory. - We open the output file deal with utilizing
fopen
with ‘php://output’ because the file title. This enables us to put in writing 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 info from the MySQL desk as a CSV file, making it straightforward for customers to open and work with in spreadsheet functions like Excel. Don’t neglect to interchange the database credentials with your personal.