Export MySQL to CSV (php script)

export_csv.php

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

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

tyfs
very helpful.

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

mysql_connect() is no longer supported in php7

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

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?

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

Thx, Very helpful.

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?