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

Hello, with "your code" I wanted to mean the script itself.
I'vejust done some changes and I've moved the script in a new page ("printoexcel.php")
The button that makes the script run is part of a form that had its action pointing to the same page.
Now the action points to printoexcel.php and everything seems to works fine!

Thanks for answering and for this great script!!!
:)

Hi janschoepke,

Thanks for this great script!
Instead of the results being downloaded, Is there a way to save the csv file export locally? (on the same directory on the server where the script is located)

Thanks!
EvilOnMac

Hi janschoepke,

Thanks for this great script!
Instead of the results being downloaded, Is there a way to save the csv file export locally? (on the same directory on the server where the script is located)

Thanks!
EvilOnMac

Hi,
sure there is a possibility: Just remove the lines 48-51 and add the following code at the end of the file:

// Write CSV result to file
file_put_contents($csv_filename, $csv_export);

Please make sure, the executing user has permissions to write files in the current directory. After running this changed version of the script, a file in the same directory should be created. It is possible to adjust the path or the file name by changing the first parameter of the added file_put_contents()-parameter. As this code is untested, please give me feedback if it works as expected.

Hi janschoepke,
Thanks for this great script!
Instead of the results being downloaded, Is there a way to save the csv file export locally? (on the same directory on the server where the script is located)
Thanks!
EvilOnMac

Hi,
sure there is a possibility: Just remove the lines 48-51 and add the following code at the end of the file:

// Write CSV result to file
file_put_contents($csv_filename, $csv_export);

Please make sure, the executing user has permissions to write files in the current directory. After running this changed version of the script, a file in the same directory should be created. It is possible to adjust the path or the file name by changing the first parameter of the added file_put_contents()-parameter. As this code is untested, please give me feedback if it works as expected.

Hi janschoepke,

It works! a .csv export file is created on the same directory as the script file.
The character encoding is not showing correctly (Portuguese characters). Is there a way of adding the export encoding?

Thanks!
EvilOnMac

Hi janschoepke,

It works! a .csv export file is created on the same directory as the script file.
The character encoding is not showing correctly (Portuguese characters). Is there a way of adding the export encoding?

Thanks!
EvilOnMac

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

Hi janschoepke,
It works! a .csv export file is created on the same directory as the script file.
The character encoding is not showing correctly (Portuguese characters). Is there a way of adding the export encoding?
Thanks!
EvilOnMac

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

Hi janschoepke,

Thanks for your help!
Added the bellow line to the script, working! Portuguese characters are exported correctly.
mysqli_set_charset( $conn, 'utf8');

Thanks!
EvilOnMac

Awesome!

One more noob question: Is there anyway to change the names of the column names in the CSV file?

Found something like this but could not get in to work in this code...

$fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status');
fputcsv($f, $fields, $delimiter);

Awesome!

One more noob question: Is there anyway to change the names of the column names in the CSV file?

Found something like this but could not get in to work in this code...

$fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status');
fputcsv($f, $fields, $delimiter);

I would recommend something like this:

Line 37: Add table heading mappings:

$tableHeadings = [
'id' => 'ID', 
'name' => 'Name'
...
];

Where the array key is the mysql row name, and the value the string which should be listed in the CSV.

The loop could be manipulated in this way:

$counter = 0;
// 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++) {
        if($counter === 0) {
            $csv_export.= '"'.$tableHeadings[$row[mysqli_fetch_field_direct($query, $i)->name]].'";';
        } else {
            $csv_export.= '"'.$row[mysqli_fetch_field_direct($query, $i)->name].'";';
        }
    }
    $csv_export.= '
';
    $counter++;
}

As this code is untested, please give me feedback if it works as expected.

Hi janschoepke,
Thanks again for this great script,

Another question, i have on the mysql database export a particular table that contain double quotes characters inside it:
;""Satelite","TV","Pool","Gaming room"";
If i try to delete the double quotes characters before the csv export, all the " in the file will disappear and then importing the csv file to excel for example will not work (it will break the data)

Is there a way of deleting those extra " during the export for that particular table so it will look like this?
;"Satelite,TV,Pool,Gaming room";

Code added:

$space = '';
$cleanarray = array('"');
$cleancount = count($cleanarray);
for ($i=0; $i<$cleancount; $i++) {

$csv_export = str_replace($cleanarray[$i], $space, $csv_export);

}

Thanks!
EvilOnMac

Hi janschoepke,
Thanks again for this great script,

Another question, i have on the mysql database export a particular table that contain double quotes characters inside it:
;""Satelite","TV","Pool","Gaming room"";
If i try to delete the double quotes characters before the csv export, all the " in the file will disappear and then importing the csv file to excel for example will not work (it will break the data)

Is there a way of deleting those extra " during the export for that particular table so it will look like this?
;"Satelite,TV,Pool,Gaming room";

Code added:

$space = '';
$cleanarray = array('"');
$cleancount = count($cleanarray);
for ($i=0; $i<$cleancount; $i++) {

$csv_export = str_replace($cleanarray[$i], $space, $csv_export);

}

Thanks!
EvilOnMac

It is not neccessary to add an additional loop.
If the quotes appear in the headlines (=table column names in SQL), you need to adjust line 31 of the code to the following:

$csv_export.= str_replace('"', '', mysqli_fetch_field_direct($query, $i)->name).';';

If the quotes appear in the row content, you need to adjust line 42 to the following:

$csv_export.= '"'.str_replace('"', '', $row[mysqli_fetch_field_direct($query, $i)->name]).'";';

As this code is untested, please give me feedback if it works as expected.

Hi janschoepke,
The quotes where appearing in the row content, added the above suggested code and it´s working just perfectly!
Thanks!
EvilOnMac

Hi,
get a 500 Internal Error - I made the db settings but got a 500 error

Maybe you should enable error reporting in the first lines of the file, directly after "<?php":
ini_set('display_errors', '1'); ini_set('display_startup_errors', '1'); error_reporting(E_ALL);

After inserting these lines, you need to re-run the script. Then the specific error should be printed into the browser. Try debugging it or post the stack trace if you can't get it to work :)

Thanks a lot - it was my error. i fixed it and it works.
How can I change the sparator vom a " ;" to a "," and set the utf8 to iso-8859-1

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!