Creating CSV file from SQL

CSV (Comma Separated Value) is commonly used in creating reports especially in PHP programs that requires reports. Since csv is in tabular format and allow different separator such as comma, extensions to allow numbers, the separator character, or newline characters in text fields. CSV are created by PHP script which allows users to download and save it as a copy. Please take a look at this example.

This is an example of database table.

Firstname	Lastname	MiddleName	Gender	Age
Janzell	Jurilla	Valguna	male	22
Rose	Test	Sison	female	19

The above table of data may be represented in CSV format

Generating CSV File from SQL

1. In order to query data from SQL you need to create SQL connection (create a php file and name it as connection.php). Insert the following code.



<?php 
#credentials details
$server_host = 'your_host';
$sql_username = 'your_username';
$sql_password = 'your_password';
$db = 'database_name';


#connect to sql server
$link = mysql_connect($server, $mysql_user, $mysql_password); 
if (!$link) { 
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {  
die ('Can\'t use ' . $soc_db . ' : ' . mysql_error()); 
}

?>

2. Create another php file and rename it as generate_csv.php.

code: 

<?php
#include connection.php file 


include('connection.php');


# set a filename for your csv file 
$file_name ="filename.csv";


#opening the csv file 
$fp = fopen('/'.$file_name, 'w');   //make your csv file writable


#create a header text for your csv file
fputcsv($fp, array('header1', 'header2', 'header3 '));

$contents = "header1, header2, header3 \n";

#create a query statement 
$order_query = "SELECT * FROM tablename WHERE  condition";


$result = mysql_query($order_query);


if (mysql_num_rows($result) > 0) {
     while ($row = mysql_fetch_assoc($result)) { 


     #loop the row value  in $content variable, take note that row data below are only examples. Please replace this with your correct table fields
fputcsv($fp, array($row['header1_data'] , $row['header2_data'] , $row['header3_data']));
        $contents .= $row['header1_data']." , ".$row['header2_data']." , ".$row['header3_data']."\n"; 
     }
}else{
   echo "no records found";
}


#To generate csv and download this as attachment. 


header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$file_name);
echo $contents;

#close the csv file 
fclose($fp);


?>


3. Now try to access generate_csv.php in your browser. Make sure your using wamp / xampp or other Apache, PHP package.