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.
