PHP og MySQL: Eksporter en spørring til en tabulatordelt eller CSV-fil
Denne helgen ønsket jeg å bygge en PHP side som vil sikkerhetskopiere alle MySQL spørring eller tabell til en tabulatordelt fil. De fleste eksemplene ute på nettet har kolonnene hardkodet.
I mitt tilfelle ønsket jeg at kolonnene skulle være dynamiske, så jeg måtte først gå gjennom alle tabellfeltnavnene for å bygge overskriftsraden med kolonnenavn og deretter gå gjennom alle postene for de gjenværende dataradene. Jeg har også satt overskriften slik at nettleseren starter nedlastingen av filen i filtypen (txt) med navnet på fildatoen og tidsstemplet.
Tabulatordelt eksport fra MySQL i 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();
?>
La oss gå gjennom koden trinn for trinn med forklaringer for hver del:
<?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);
}
- Vi genererer gjeldende dato og klokkeslett i "YmdHi"-formatet og lagrer det i
$today
variabel. - HTTP-hoder er satt til å spesifisere at innholdet skal behandles som en oktettstrøm (binære data) og utløse en filnedlasting med det angitte filnavnet.
- Ved å bruke utvidelsen oppretter vi en MySQL-databasetilkobling, og erstatter plassholdere med din faktiske databaselegitimasjon.
- Vi sjekker om databasetilkoblingen var vellykket. Vi avslutter skriptet og viser en feilmelding hvis det er en feil.
// 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";
- Vi definerer SQL-spørringen for å velge alle data fra
mytable
bord, bestille det ettermyorder
kolonne. - Spørringen utføres, og resultatet lagres i
$result
variabel. - Vi sjekker om det er noen rader returnert ved å undersøke
num_rows
egenskapen til resultatobjektet. - Vi bruker
fetch_fields()
for å hente felt (kolonne) navn og lagre dem i$fields
matrise. - Overskriftsraden for eksportfilen er klargjort ved å gå gjennom feltnavnene og sette dem sammen med faner.
// 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";
}
- Vi bruker en
while
loop for å hente hver datarad fra resultatsettet ved hjelp avfetch_assoc()
. - Inne i løkken forbereder vi verdiene for hver rad ved å iterere gjennom feltene og samle inn tilsvarende data.
- Verdiene for hver rad er sammenkoblet med tabulatorer for å lage en tabulatordelt rad, og denne raden legges til
$data
variabel.
// 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();
?>
- Hvis det er data funnet (sjekket med
num_rows
), ekko de sammenkoblede dataene, som er innholdet i eksportfilen. Dette utløser filnedlastingen i brukerens nettleser. - Hvis ingen data blir funnet, viser vi en melding som indikerer at ingen data er tilgjengelig.
- Vi lukker MySQL-databaseforbindelsen ved hjelp av
$conn->close()
å frigjøre ressurser.
Denne koden eksporterer effektivt data fra en MySQL-databasetabell til en tabulatordelt tekstfil og håndterer ulike scenarier, for eksempel databasetilkoblingsfeil og tomme resultatsett.
Komma-separerte verdier Eksporter fra MySQL i PHP
Jeg kan endre koden for å eksportere data som en CSV-fil. Her er koden, oppdatert for CSV-eksport:
<?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();
?>
I denne modifiserte koden:
- Overskriftene for HTTP-svaret oppdateres for å spesifisere en
text/csv
innholdstype, og filnavnet har filtypen ".csv". - I stedet for manuelt å lage CSV-innholdet, bruker vi
fputcsv
funksjon for å sende ut hver rad fra MySQL-resultatsettet som en CSV-linje. Denne funksjonen håndterer CSV-formateringen for deg, inkludert håndtering av spesialtegn og omslutning av felt i doble anførselstegn ved behov. - Vi åpner utdatafilhåndtaket ved hjelp av
fopen
med 'php://output' som filnavn. Dette lar oss skrive direkte til HTTP-svarutdatastrømmen. - Koden er strukturert for å håndtere CSV-eksport effektivt og lukker filhåndtaket når den er ferdig.
Denne koden vil eksportere dataene fra MySQL-tabellen som en CSV-fil, noe som gjør det enkelt for brukere å åpne og jobbe med i regnearkapplikasjoner som Excel. Ikke glem å erstatte databaselegitimasjonen med din egen.