Skip to content

Instantly share code, notes, and snippets.

@janschoepke
Created June 20, 2017 15:55
Show Gist options
  • Save janschoepke/3e7a3639546d0d740c023e11289cf13d to your computer and use it in GitHub Desktop.
Save janschoepke/3e7a3639546d0d740c023e11289cf13d to your computer and use it in GitHub Desktop.
MySQL CSV Export via PHP (PHP7 ready)
<?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);
@janschoepke
Copy link
Author

janschoepke commented Dec 11, 2019

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.

@evilonmac
Copy link

evilonmac commented Feb 29, 2020

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

@janschoepke
Copy link
Author

janschoepke commented Mar 2, 2020

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.

@evilonmac
Copy link

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

@herrstromberg
Copy link

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

@janschoepke
Copy link
Author

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

@herrstromberg
Copy link

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

@janschoepke
Copy link
Author

janschoepke commented Aug 13, 2020

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

@evilonmac
Copy link

evilonmac commented Aug 19, 2020

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

@janschoepke
Copy link
Author

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.

@evilonmac
Copy link

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

@janschoepke
Copy link
Author

janschoepke commented Aug 19, 2020

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

@tocsindata
Copy link

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 ;

@casfreitas
Copy link

Thanks a lot!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment