A Comma-Separated Values(CSV) file stores information in a list with a comma between each item. A CSV file may consists of any number of records. CSV file format is widely supported by scientific applications, and various consumer and business applications. There are a few variations where comma is not used as the field delimiter, instead tab, pipe, etc. may be used as the field delimiter.
Example table : Employee
Here we are creating CSV file which will contain employee table given above.First we will connect to MySQL database and then query to select all the records from the employee table.After selecting records from the table, we will open a file(abc.csv) in write mode and put the content in the file.
Here we are reading back the file which we have created above and displaying out the content.fgetcsv function will parse the CSV file and return an indexed array.
The output:
Creating CSV file
Example table : Employee
Code:
+------+------------+-----------+------------+
| id | first_name | last_name | DOJ |
+------+------------+-----------+------------+
| 1 | Ramesh | Prasad | 1996-09-17 |
| 2 | Dilip | Das | 1987-07-30 |
| 3 | Prakash | Chandra | 1982-10-24 |
| 4 | Sourav | Roy | 1996-07-25 |
| 5 | Vijay | Kumar | 1982-10-18 |
| 6 | Sourav | Kumar | 1996-09-11 |
+------+------------+-----------+------------+
Here we are creating CSV file which will contain employee table given above.First we will connect to MySQL database and then query to select all the records from the employee table.After selecting records from the table, we will open a file(abc.csv) in write mode and put the content in the file.
Code: PHP
<?php
mysql_connect("localhost", "root", "");
mysql_select_db("mydb");
$data = "";
$separator = "";
//if you are on linux box change the file location url
$fp = fopen("/home/jasbir/abc.csv", "w");
$result = mysql_query("SELECT * FROM employee");
// fetch a row and write the field names into file
$row = mysql_fetch_assoc($result);
foreach ($row as $field => $value)
{
$data.= $separator . '"' . strtoupper(str_replace('"', '""', $field)) . '"';
$separator = ",";
}
$data.= "\n";
fputs($fp, $data);
// reset the pointer back to the beginning of record
mysql_data_seek($result, 0);
// and loop through the actual data
while ($row = mysql_fetch_assoc($result))
{
$data = "";
$separator = "";
foreach ($row as $value)
{
// escape double quotes
$data.= $separator . '"' . str_replace('"', '""', $value) . '"';
$separator = ",";
}
$data.= "\n";
fputs($fp, $data);
}
fclose($fp);
?>
Reading CSV File
Here we are reading back the file which we have created above and displaying out the content.fgetcsv function will parse the CSV file and return an indexed array.
Code: PHP
<?php
$fp = fopen('/home/jasbir/data.csv', 'r') or die("can't open file");
print "<table border='1' align='center'>\n";
while ($csv_line = fgetcsv($fp, 1024))
{
print '<tr>';
for ($i = 0, $j = count($csv_line);$i < $j;$i++)
{
print '<td>' . $csv_line[$i] . '</td>';
}
print "</tr>\n";
}
print '</table>';
fclose($fp) or die("can't close file");
?>
The output:
Code:
ID FIRST_NAME LAST_NAME DOJ
1 Ramesh Prasad 1996-09-17
2 Dilip Das 1987-07-30
3 Prakash Chandra 1982-10-24
4 Sourav Roy 1996-07-25
5 Vijay Kumar 1982-10-18
6 Sourav Kumar 1996-09-11
