-
-
Save janschoepke/3e7a3639546d0d740c023e11289cf13d to your computer and use it in GitHub Desktop.
<?php | |
/* vars for export */ | |
// database record to be exported | |
$db_record = 'XXX_TABLE_NAME_XXX'; | |
// optional where query | |
$where = 'WHERE 1 ORDER BY 1'; | |
// filename for export | |
$csv_filename = 'db_export_'.$db_record.'_'.date('Y-m-d').'.csv'; | |
// database variables | |
$hostname = "XXX_HOSTNAME_XXX"; | |
$user = "XXX_USER_XXX"; | |
$password = "XXX_PASS_XXX"; | |
$database = "XXX_DATABASE_XXX"; | |
$port = 3306; | |
$conn = mysqli_connect($hostname, $user, $password, $database, $port); | |
if (mysqli_connect_errno()) { | |
die("Failed to connect to MySQL: " . mysqli_connect_error()); | |
} | |
// create empty variable to be filled with export data | |
$csv_export = ''; | |
// query to get data from database | |
$query = mysqli_query($conn, "SELECT * FROM ".$db_record." ".$where); | |
$field = mysqli_field_count($conn); | |
// create line with field names | |
for($i = 0; $i < $field; $i++) { | |
$csv_export.= mysqli_fetch_field_direct($query, $i)->name.';'; | |
} | |
// newline (seems to work both on Linux & Windows servers) | |
$csv_export.= ' | |
'; | |
// loop through database query and fill export variable | |
while($row = mysqli_fetch_array($query)) { | |
// create line with field values | |
for($i = 0; $i < $field; $i++) { | |
$csv_export.= '"'.$row[mysqli_fetch_field_direct($query, $i)->name].'";'; | |
} | |
$csv_export.= ' | |
'; | |
} | |
// Export the data and prompt a csv file for download | |
header("Content-type: text/x-csv"); | |
header("Content-Disposition: attachment; filename=".$csv_filename.""); | |
echo($csv_export); |
Hi janschoepke,
When trying to run the query in PHPmyadmin the results are displayed correctly, no errors.
The weird thing is that the script is running on another server, no changes, no problem but when moving the scrypt and database to a new server, the error happen.
Thanks,
EvilOnMac
Does that maybe have something to do with the database credentials or the connectivity to the database server? Try to access the database via ssh remotely on the new server. When the script works fine on another server, the mistake should not be in the script. Maybe ask your DevOps :D
Just wanted to leave a note, or maybe a question... am I the only one still using PHP_EOL for newlines?
// newline (seems to work both on Linux & Windows servers)
$csv_export.= PHP_EOL ;
Thanks a lot!
Hi,
the fact that your $query-variable is boolish (false) indicates that the query could not be processed successfully. Please try to enter the MySQL query directly into MySQL console or other platforms e.g. PHPMyAdmin. There you should receive a more informative error message. Then try to fix the error in the query.