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

it downloads all the webpage

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Same effect, no PHP needed :)

Hello.

I need to put the php code to generate csv from mysql and save in a directory like /public_html

How can i do this ?

Thx

You are using all deprecated functions. Please update according to today's standards !

@pvdptje comment about using the simple SQL statement is great, but will probably only work on a dev box as you will probably have the --secure-file-priv option (and should have) turned on in production. So that wont work, this script will however.

@sanjayrnath: A PHP7-ready version can be found here, as nearly all of the mysql functions used above will be outdated in PHP7:

https://gist.github.com/janschoepke/3e7a3639546d0d740c023e11289cf13d

Brilliant! Thank you so much. Works like a charm and exactly what I needed!

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?