Export Database Records to CSV (MYSQLi)

In this example, we have a MySQL table named as the toy. we are reading the field names of this table and adding them as a heading of the CSV file. And then, we are iterating table rows and write it in CSV string format.

 

 
We have to set header with content-type, disposition and file name with .csv extension to export CSV formatted string and download it as an attachment file.

 <?php
$conn = mysqli_connect("localhost","root","test", "phppot_examples");

$query = "SELECT * FROM toy";
$result = mysqli_query($conn, $query);

$num_column = mysqli_num_fields($result);        

$csv_header = '';
for($i=0;$i<$num_column;$i++) {
    $csv_header .= '"' . mysqli_fetch_field_direct($result,$i)->name . '",';
}    
$csv_header .= "\n";

$csv_row ='';
while($row = mysqli_fetch_row($result)) {
    for($i=0;$i<$num_column;$i++) {
        $csv_row .= '"' . $row[$i] . '",';
    }
    $csv_row .= "\n";
}    

/* Download as CSV File */
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename=toy_csv.csv');
echo $csv_header . $csv_row;
exit;
?>