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)mp3s@db.example.com/restaurant');
// 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 (?, ?, ?)", 
               $info);
    print "Inserted $info[0]\n";
}
// Close the file
fclose($fh);

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)mp3s@db.example.com/restaurant');
// 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);
}
fclose($fh);

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)mp3s@db.example.com/restaurant');

// 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 http://pear.php.net/package/Spreadsheet_Excel_Writer.