Skip to content

Instantly share code, notes, and snippets.

@navitronic
Created March 15, 2012 23:31
Show Gist options
  • Save navitronic/2047649 to your computer and use it in GitHub Desktop.
Save navitronic/2047649 to your computer and use it in GitHub Desktop.
MySQL table to csv
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
$dbname = 'dbname';
/*** mysql username ***/
$username = 'username';
/*** mysql password ***/
$password = 'password';
try {
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
$tablename = 'table';
$sql = 'SHOW COLUMNS FROM `'.$tablename.'`';
$stmt = $dbh->query($sql);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
array_push($fields, $row['Field']);
}
array_push($csv, $fields);
$sql = 'SELECT * FROM `'.$tablename.'`';
$stmt = $dbh->query($sql);
$stmt->execute();
$csv = array();
while($row = $stmt->fetch(PDO::FETCH_NUM))
{
array_push($csv, $row);
}
$fp = fopen('file.csv', 'w');
foreach ($csv as $row) {
fputcsv($fp, $row);
}
fclose($fp);
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=export.csv");
header("Pragma: no-cache");
header("Expires: 0");
readfile('file.csv');
$dbh = null;
} catch(PDOException $e) {
echo $e->getMessage();
}
@Amantel
Copy link

Amantel commented Oct 19, 2014

Hi! Kinda new to this, so I don`t know how to do push request.
Gist is great and save me some time, but I got warnings until I manualy declared $fields and $csv as arrays before pushing to them and removing such declaration from line 37 (you push earlier)

@felixhe8
Copy link

felixhe8 commented Aug 7, 2015

Great codes, very helpful.

@madahmani
Copy link

The code is great and working well but producing errors here and there.

Please add these 2 lines before pushing the arrays:

$fields = array();
$csv = array();

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