In this post we will discuss how to create csv file with PHP and force it to download. We will be pulling data from MySQL database.

1st of all we will need connection to database, for that we will use this script. Change the necessary details and save it as conn.php. Now lets start with the csv.

<?php
require_once 'conn.php';

$sql = $dbh->prepare("SELECT u.name, u.email, u.phone, u.city, s.filename FROM_UNIXTIME(s.sub_time, '%M %D %Y - %l:%i %p') from users u join submissions s on u.uid=s.uid where s.uid != '' and s.filename != '' order by s.sub_time desc");
$sql->execute();
$row = $sql->rowCount();

if ($row > 0) {

  $columns = array('NAME','EMAIL','PHONE','CITY','FILENAME','TIME');

  $filep = fopen('php://output', 'w');
  if ($filep) {

    $file = uniqid().'_'.time().'.csv';
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename=$file");
    header("Pragma: no-cache");
    header("Expires: 0");
    fputcsv($filep, $columns);

    while ($rows = $sql->fetch(MYSQL_NUM)) {
      fputcsv($filep, $rows);
    }

  die;
  }

}
?>

The script is quite straight forward and simple to use/follow. Little bit explanation: we are joining two tables here and then checking if any record found with rowCount function, if so we proceed to create csv. The array store the headers which will be column headers in the csv file. Giving a unique name to the file we will download and then using PHP fputcsv function to write data, quite easy and painless.

Series:
Part-1: Setup web server on Amazon AMI or CentOS
Part-2: Backup MySQL data into CSV
Part-3: Backup MySQL data into CSV with PHP
Part-4: Backup and Restore MySQL databases
Part-5: Speed up your website loading time by using PHP APC