DEV Community

loading...

PHP, MYSQLI, and CSV Export from database

madeinmilwaukee profile image Made in Milwaukee ・2 min read

First contribution here and thought I'd share a snippet from a recent project. When scrubbing around the net for info on how to output a csv file directly to the browser I noticed there are quite a few outdated and overly verbose examples. Here's what I ended up with.

My project needed the data to be pulled by date so this example uses a date field. For me, a user is picking a date and submitting a form to get the csv download so I am using a prepared statement to avoid any mysql injection.
I didn't put in error handling for brevity's sake, but you can mark up your code with as much or little as you see fit.

The examples I have seen usually use a bunch of concatenation when iterating over the data to separate out and build the header line and the body lines but there are a few biult in functions that replace all that

  • fputcsv - formats array line to csv format
  • array_values - grab the values of associative array and add them to the csv array
  • array_keys - set the header of the csv file if it is needed using the returned associative array

This is working for me currently, and producing expected results. I'm not an expert by any means but I hope this is helpful to someone out there looking for examples on how to do this.

This example should work for PHP 5 >= 5.3.0, PHP 7.

$conn = new mysqli('myHOST', 'myUSER', 'myPASS', 'myDB');
$date=date('Y-m-d'); //this would be $_POST['date'] from the form
$q = "SELECT dateColumn,column2,column3,column4 FROM myTABLE WHERE dateColumn = ? ";
$stmt = $conn->prepare($q);
$stmt->bind_param('s', $date);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
   //These next 3 Lines Set the CSV header line if needed
   $data = $result->fetch_assoc();
   $csv[] = array_keys($data);
   $result->data_seek(0);
   //SET THE CSV BODY LINES
    while ($data = $result->fetch_assoc()) {
        $csv[] = array_values($data);
    }
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="myCSV.csv";');
    //You could save the file on your server
    //but we want to download it directly so we use php://output
    $f = fopen('php://output', 'w');
    foreach ($csv as $line) {
        fputcsv($f, $line, ',');
    }
    exit;
} else {
    echo 'No data to export for ' . $date;
}

Discussion (1)

pic
Editor guide
Collapse
w3gaucho profile image
W3G

it's relevant to say that data native exportation from mysql is possible using INTO OUTFILE.