Skip to content

Instantly share code, notes, and snippets.

@apocratus
Created April 22, 2011 10:10
Show Gist options
  • Save apocratus/936404 to your computer and use it in GitHub Desktop.
Save apocratus/936404 to your computer and use it in GitHub Desktop.
Export MySQL to CSV (php script)
<?php
/* vars for export */
// database record to be exported
$db_record = 'XXXXXXXXX';
// 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 = "localhost";
$user = "XXXXXXXXX";
$password = "XXXXXXXXX";
$database = "XXXXXXXXX";
// Database connecten voor alle services
mysql_connect($hostname, $user, $password)
or die('Could not connect: ' . mysql_error());
mysql_select_db($database)
or die ('Could not select database ' . mysql_error());
// create empty variable to be filled with export data
$csv_export = '';
// query to get data from database
$query = mysql_query("SELECT * FROM ".$db_record." ".$where);
$field = mysql_num_fields($query);
// create line with field names
for($i = 0; $i < $field; $i++) {
$csv_export.= mysql_field_name($query,$i).';';
}
// newline (seems to work both on Linux & Windows servers)
$csv_export.= '
';
// loop through database query and fill export variable
while($row = mysql_fetch_array($query)) {
// create line with field values
for($i = 0; $i < $field; $i++) {
$csv_export.= '"'.$row[mysql_field_name($query,$i)].'";';
}
$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);
?>
@domino4evers
Copy link

What if i don't whan't to download it, but save it on the server?

@C-Abner
Copy link

C-Abner commented May 29, 2018

tyfs
very helpful.

@ivink3aray
Copy link

hi, script it's works but how to determine path url for export result

@LorenzNickel
Copy link

mysql_connect() is no longer supported in php7

@Kyaw-Swar-Linn-Mg
Copy link

shell_exec("export LC_ALL=en_US.UTF-8\nexport LANG=en_US.UTF-8\n/Applications/MAMP/Library/bin/mysql -uroot -proot mahar -e '".$query."'| tr '\t' ',' > ".$file);

@MogagaP
Copy link

MogagaP commented Jul 4, 2019

Hola buenas tardes.
Espero que podáis ayudarme.
Me gustaría poder exportar a csv los datos de una tabla que se muestra según el usuario logado. Tengo el siguiente código, pero hay algo que no hace. El csv se descarga con el nombre del archivo, pero sin datos.

Podéis ayudarme por favor?

@Engerwald
Copy link

I created an account just to say thank you! You saved the day :)
The script works perfectly.

@Robin87119
Copy link

Robin87119 commented Mar 1, 2020

Thx, Very helpful.

@batyna
Copy link

batyna commented Mar 28, 2020

Hi, I really liked your code, but when running it on the server, it outputs the table contents to the page. Logically, it should download a file in csv format. Can you tell me what the problem is?

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