How to Export MySQL database records to a CSV file

In this tutorial, we are going to see how to export MySQL database records to a CSV file. Few days before we have seen about how to read from a CSV file.

After connecting to the database, we have to fetch MySQL table records. These database results should be formatted as CSV string with proper delimiters and enclosures. And then, we need to download CSV after sending the header to the browser.

Example: Export MySQL database records to a CSV file

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","user", "database");

$query = "SELECT * FROM users";
$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;
?>