I'm new to PHP and am trying to create a small snippet of code that reads the tables in my database and allows the user to download the tables into a CSV file.
So far, I've been able to connect to my database and echo through the tables
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed1: " . $conn->connect_error);
}
// SQL query
$sql = "SHOW TABLES IN `abc1`";
// perform the query and store the result
$result = $conn->query($sql);
// if the $result not False, and contains at least one row
if($result !== false) {
// if at least one table in result
if($result->num_rows > 0) {
// traverse the $result and output the name of the table(s)
while($row = $result->fetch_assoc()) {
echo '<br />'. $row['Tables_in_abc1'];
}
}
else echo 'There is no table in "tests"';
}
else echo 'Unable to check the "tests", error - '. $conn->error;
$conn->close();
?>
Now I'd like to turn each table into a link so when the user clicks on it, they will be able to download the table's data into a CSV file.
How do I do that?
This should be a comment but I'm not high enough of a level to leave one. You should check out PHPExcel.
https://github.com/PHPOffice/PHPExcel
It comes with numerous examples which should help you achieve what you are trying to do.
You could stream the data to the client like this:
header('Content-type: text/csv');
header('Content-disposition: attachment;filename=file.csv');
$stdout = fopen('php://stdout', 'w');
while($row = $result->fetch_assoc()) {
fputcsv($stdout, $row);
}
fclose($stdout);
or write to a file:
$filePath = __DIR__ .'/tmp.csv'; // for instance current folder
$fh = fopen($filePath, 'w+');
while($row = $result->fetch_assoc()) {
fputcsv($fh, $row);
}
fclose($fh);
and then send the find to client:
header('Content-type: text/csv');
header('Content-disposition: attachment;filename=file.csv');
readfile($filePath);