Query mysql and export data as CSV in PHP

This is a solution that incorporates the headers or field names by using MySQLi's fetch_fields function -

$query = "SELECT * FROM table";
$result = $db->query($query);
if (!$result) die('Couldn\'t fetch records');
$headers = $result->fetch_fields();
foreach($headers as $header) {
    $head[] = $header->name;
}
$fp = fopen('php://output', 'w');

if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, array_values($head)); 
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

This is also a modification of a different answer where a similar solution was suggested but the headers part did not work for me, so I changed the method of retrieving them. Credit is due to @Jrgns

You can try to utilize MySql INTO OUTFILE clause:

SELECT *
  INTO OUTFILE '/tmp/tablename.csv'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
   FROM tablename
  WHERE userid = 1

for creating and downloading a csv file from a mysqli query (using object oriented) I think this would help.

This is a Class that connects with the database and the functions will do whatever you want using mysqli and PHP. In this case, calling this class (require or include), just use the "downloadCsv()" function.

As an example, this would be the "class.php" file:

<?php
class DB{

private $con;

//this constructor connects with the database
public function __construct(){
$this->con = new mysqli("Your_Host","Your_User","Your_Pass","Your_DatabaseName");

if($this->con->connect_errno > 0){
    die('There was a problem [' . $con->connect_error . ']');
    }
}

//create the function that will download a csv file from a mysqli query

public function downloadCsv(){

$count = 0;
$header = "";
$data = "";
//query
$result = $this->con->query("SELECT * FROM Your_TableName");
//count fields
$count = $result->field_count;
//columns names
$names = $result->fetch_fields();
//put column names into header
foreach($names as $value) {
    $header .= $value->name.";";
    }
}
//put rows from your query
while($row = $result->fetch_row())  {
    $line = '';
    foreach($row as $value)       {
        if(!isset($value) || $value == "")  {
            $value = ";"; //in this case, ";" separates columns
    } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . ";"; //if you change the separator before, change this ";" too
        }
        $line .= $value;
    } //end foreach
    $data .= trim($line)."\n";
} //end while
//avoiding problems with data that includes "\r"
$data = str_replace("\r", "", $data);
//if empty query
if ($data == "") {
    $data = "\nno matching records found\n";
}
$count = $result->field_count;

//Download csv file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=FILENAME.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data."\n";

}
?>

After creating the "class.php" file, in this example, use that function on "download.php" file:

<?php
//call the "class.php" file
require_once 'class.php';
//instantiate DB class
$export = new DB();
//call function
$export->downloadCsv();
?>

After download, open the file with MS Excel.

I hope this help you, I think I wrote it well, I didn't feel comfortable with the text and code field.

Try this out.

function addRowToCsv(& $csvString, $cols) {
    $csvString .= implode(',', $cols) . PHP_EOL; //Edits must be 6 characters - all I added was a "." before the =. :-)
}

$csvString = '';
$first = true;

while ($row = mysqli_fetch_assoc($query)) {
    if ($first === true) {
        $first = false;
        addRowToCsv($csvString, array_keys($row));
    }
    addRowToCsv($csvString, $row);
}

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=MyCsvFile.csv');

echo $csvString;

Notice that the first argument to addRowToCsv is passed by reference. This is not required and you could easily use a return value, but this is just how I would do it.

If you want to save the output to a file rather than serving it as a download then use the above but replace

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=MyCsvFile.csv');

echo $csvString;

With..

file_put_contents('MyCsvFile.csv', $csvString);