Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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);
?>
@Toomean

This comment has been minimized.

Copy link

Toomean commented Mar 14, 2014

Thanks for that)

@sidecarken

This comment has been minimized.

Copy link

sidecarken commented Jul 21, 2015

What goes here?
$db_record = 'XXXXXXXXX';

I put first_name but I don't know if that is correct?

@apocratus

This comment has been minimized.

Copy link
Owner Author

apocratus commented Sep 22, 2015

@sidecarken: That's where the table name is supposed to go, so probably something along the lines of "users" in your case.

@tareksmoubarak

This comment has been minimized.

Copy link

tareksmoubarak commented Nov 8, 2015

it downloads all the webpage

@pvdptje

This comment has been minimized.

Copy link

pvdptje commented Jun 27, 2016

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

@velicuicristian

This comment has been minimized.

Copy link

velicuicristian commented Oct 17, 2016

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

@sanjayrnath

This comment has been minimized.

Copy link

sanjayrnath commented Feb 8, 2017

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

@paulchabotca

This comment has been minimized.

Copy link

paulchabotca commented Mar 16, 2017

@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.

@janschoepke

This comment has been minimized.

Copy link

janschoepke commented Jun 20, 2017

@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

@nawissor

This comment has been minimized.

Copy link

nawissor commented Jan 27, 2018

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

@domino4evers

This comment has been minimized.

Copy link

domino4evers commented Mar 7, 2018

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

@C-Abner

This comment has been minimized.

Copy link

C-Abner commented May 29, 2018

tyfs
very helpful.

@ivink3aray

This comment has been minimized.

Copy link

ivink3aray commented Dec 21, 2018

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

@BlockLabTV

This comment has been minimized.

Copy link

BlockLabTV commented Feb 16, 2019

mysql_connect() is no longer supported in php7

@Kyaw-Swar-Linn-Mg

This comment has been minimized.

Copy link

Kyaw-Swar-Linn-Mg commented Mar 8, 2019

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

This comment has been minimized.

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

This comment has been minimized.

Copy link

Engerwald commented Oct 3, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.