Read And Create CSV File In PHP

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.

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.

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.

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

 

thanks for this tutorial. Is it possible if I want to save my database file in excel ?