MySQL CSV Export via PHP (PHP7 ready)

export_csv.php

<?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);

Change separator:
change the ends from lines 31/42 from .';'; to .',';

Set Charset:
Try setting the mysqli_charset to the correct value with this function: https://www.php.net/manual/de/mysqli.set-charset.php

Hi janschoepke,
Your script is working just wonderfully, i have it working on 2 different servers without problems. I have tried it on a new server account but now this error is displayed on the log when i run the script:
[19-Aug-2020 14:09:41 Europe/Lisbon] PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/henriquespropert/public_html/crm/export/export_mysql_csv.php on line 35

Line 35 is:

while($row = mysqli_fetch_array($query)) {
// create line with field values
for($i = 0; $i < $field; $i++) {
$csv_export.= '"'.str_replace('"', '', $row[mysqli_fetch_field_direct($query, $i)->name]).'";'; // Remove double quotes from features
}
$csv_export.= '
';
}

Thanks for your precious help,
EvilOnMac

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.

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!