Previous Section  < Day Day Up >  Next Section

10.4 Working with CSV Files

One type of text file gets special treatment in PHP: the CSV file. It can't handle graphs or charts, but excels for sharing tables of data among different programs. To read a line of a CSV file, use fgetcsv( ) instead of fgets( ). It reads a line from the CSV file and returns an array containing each field in the line. Example 10-10 is a CSV file of information about restaurant dishes. Example 10-11 uses fgetcsv( ) to read the file and insert the information in it into the dishes database table from Chapter 7.

Example 10-10. dishes.csv for Example 10-11
"Fish Ball with Vegetables",4.25,0

"Spicy Salt Baked Prawns",5.50,1

"Steamed Rock Cod",11.95,0

"Sauteed String Beans",3.15,1

"Confucius ""Chicken""",4.75,0

Example 10-11. Inserting CSV data into a database table
require 'DB.php';

// Connect to the database

$db = DB::connect('mysql://hunter:w)');

// Open the CSV file

$fh = fopen('dishes.csv','rb');

for ($info = fgetcsv($fh, 1024); ! feof($fh); $info = fgetcsv($fh, 1024)) {

    // $info[0] is the dish name    (the  first field in a line of dishes.csv)

    // $info[1] is the price        (the second field)

    // $info[2] is the spicy status (the  third field)

    // Insert a row into the database table

    $db->query("INSERT INTO dishes (dish_name, price, is_spicy) VALUES (?, ?, ?)", 


    print "Inserted $info[0]\n";


// Close the file


Example 10-11 prints:

Inserted Fish Ball with Vegetables

Inserted Spicy Salt Baked Prawns

Inserted Steamed Rock Cod

Inserted Sauteed String Beans

Inserted Confucius "Chicken"

The second argument to fgetcsv( ) is a line length. This value needs to be longer than the length of the longest line in the CSV file. Example 10-11 uses 1024, which is plenty longer than any of the lines in Example 10-10. If you might have lines longer than 1K in a CSV file, pick a bigger length, such as 1048576 (1 MB).

Writing a CSV-formatted line is trickier than reading one. There's no built-in function for it, so you've got to format the line yourself. Example 10-12 contains a make_csv_line( ) function that accepts an array of values as an argument and returns a CSV-formatted string containing those values.

Example 10-12. Making a CSV-formatted string
function make_csv_line($values) {

    // If a value contains a comma, a quote, a space, a 

    // tab (\t), a newline (\n), or a linefeed (\r),

    // then surround it with quotes and replace any quotes inside

    // it with two quotes

    foreach($values as $i => $value) {

        if ((strpos($value, ',')  !=  = false) ||

            (strpos($value, '"')  !=  = false) ||

            (strpos($value, ' ')  !=  = false) ||

            (strpos($value, "\t") !=  = false) ||

            (strpos($value, "\n") !=  = false) ||

            (strpos($value, "\r") !=  = false)) {

            $values[$i] = '"' . str_replace('"', '""', $value) . '"';



    // Join together each value with a comma and tack on a newline

    return implode(',', $values) . "\n";


Example 10-13 uses the make_csv_line( ) function from Example 10-12 along with fopen( ) and fwrite( ) to retrieve information from a database table and write it to a CSV file.

Example 10-13. Writing CSV-formatted data to a file
require 'DB.php';

// Connect to the database

$db = DB::connect('mysql://hunter:w)');

// Open the CSV file for writing

$fh = fopen('dishes.csv','wb');

$dishes = $db->query('SELECT dish_name, price, is_spicy FROM dishes');

while ($row = $dishes->fetchRow( )) {

    // Turn the array from fetchRow( ) into a CSV-formatted string

    $line = make_csv_line($row);

    // Write the string to the file. No need to add a newline on

    // the end since make_csv_line( ) does that already

    fwrite($fh, $line);



To send a page that consists only of CSV-formatted data back to a web client, you have to take an extra step beyond just printing the data. You also have to use PHP's header( ) function to tell the web client to expect a CSV document instead of an HTML document. Example 10-14 shows how to call the header( ) function with the appropriate arguments.

Example 10-14. Changing the page type to CSV
// Tell the web client to expect a CSV file

header('Content-Type: text/csv');

// Tell the web client to view the CSV file in a seprate program

header('Content-Disposition: attachment; filename="dishes.csv"');

Example 10-15 contains a complete program that sends the correct CSV header, retrieves rows from a database table, and prints them. Its output can be loaded directly into a spreadsheet from a user's web browser.

Example 10-15. Sending a CSV file to the browser
require 'DB.php';

// Connect to the database

$db = DB::connect('mysql://hunter:w)');

// Tell the web client that a CSV file called "dishes.csv" is coming

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename="dishes.csv"');

// Retrieve the info from the database table and print it

$dishes = $db->query('SELECT dish_name, price, is_spicy FROM dishes');

while ($row = $dishes->fetchRow( )) {

    print make_csv_line($row);


To generate more complicated spreadsheets that include formulas, formatting, and images, use the Spreadsheet_Excel_Writer PEAR package. You can download it from

    Previous Section  < Day Day Up >  Next Section