Download CSV of table Records using OUTFILE sql command and PHP

Create and Download CSV of table Records using OUTFILE Sql command and PHP

1. Create temporary CSV file using OUTFILE SQL command: OUTFILE command is used to write your sql command output to the specified location. You can write the output to some files using OUTFILE when required.

Syntax:

SELECT  INTO OUTFILE ‘fileName’ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'    FROM tableName
Each Field data is terminated by “,” and each record line is terminated by ”\n”.
// first create connection with mysql
$dbConnection = mysqli_connect(host,username,password,databasename);
// fetching the column names of the Table using sql query
$result = $dbConnection->query('DESCRIBE table_name);
if(mysqli_num_rows($result) > 0)
{
 while($row = $result->fetch_assoc())
{
$columnNames .= $row['Field'].",";
$csvHeaders .= ucfirst(str_replace('_',' ',$row['Field'])).",";
 }
 }
 $csvHeaders=rtrim($csvHeaders,',');  //header row generated for csv file
 $columnNames=rtrim($columnNames,',');  //dynamically fetch all the column names of the table 	
// then fetch all the value related to header and place the content in tmp file 
/tmp/Inactive_csv.csv
 $sql = "SELECT $columnNames INTO OUTFILE '/tmp/Inactive_csv.csv' FIELDS 
 TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'    FROM table_name";$dbConnection->query ( $sql );
 $dbConnection->close();

2.Generate csv file and download it:

$file="/tmp/Inactive_csv.csv"; //Enter file location here	
// reading the content from tmp file
$data = file_get_contents ( $file );
// file_put_content is used to putting the content to your desired file
file_put_contents ( $file,  $csvHeaders . " \r\n" . $data );
// downloaded file should be of csv format
header('Content-Type: text/csv');	
// name the csv
header('Content-Disposition: attachment; filename='. $name);
header('Pragma: no-cache');
header("Expires: 0");	
// php://output forces the browser to download the file
file_put_contents("php://output", file_get_contents($file));
unlink ( '/tmp/Inactive_csv.csv' );