Working with database and understanding the MySql is not much of thing to worry about for programmers and web-developers. A huge database can be a headache at times though. But there are times when a normal user require to download the information from database which should be in easily understandable/readable format. Hence, letting them download the data in CSV format is a better option.

A comma-separated values (CSV) (also sometimes called character-separated values, because the separator character does not have to be a comma) file stores tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers. Read more Wiki.

Below is a code snippet I wrote to overcome a similar scenario as I discussed above.


<?php
$mysqli = new mysqli($host, $user, $pass, $db); //("localhost","root","","database_name") normally in localhost environment

if($mysqli->connect_error) {
    die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());
}

$csv_data = "ID, First name, Last name \r\n"; //Headings for columns in csv file

$result = $mysqli->query("SELECT * FROM tableName");
while ($row = mysqli_fetch_assoc($result)) {
    foreach ($row as $key => $val) {
        $csv_data .= ($val ? str_replace(',', ' ', $val) : 'Null') . ',';
    }
    $csv_data .= "\r\n"; //for new line after adding a row
}

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=data_extracted_on_" . date('d_M_Y') . ".csv");
header("Pragma: no-cache");
header("Expires: 0");

echo $data;

exit; // exit compulsorily else computer will blast :p

Notes :

  1. This code should run on top of any other HTML code. Any HTML code placed above these codes will be included in CSV file too.
  2. If content has comma (,) it will be replaced by a space in the csv file. I know this is little disappointing. I’ll definitely update the code as soon as I can after some research and omit that awkward behavior.