One type of text file gets special treatment in PHP: the CSV file. It can’t handle graphs or charts, but excels at 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 9-9 is a CSV file of information about restaurant dishes. Example 9-10 uses fgetcsv() to read the file and insert the information in it into the dishes database table from Chapter 8.
Example 9-9. dishes.csv
“Fish Balt 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 9-10. Inserting CSV data into a database table
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
} catch (Exception $e) {
print “Couldn’t connect to database: ” . $e->getMessage();
exit();
}
$fh = fopen(‘dishes.csv’,’rb’);
$stmt = $db->prepare(‘INSERT INTO dishes (dish_name, price, is_spicy)
VALUES (?,?,?)’);
while ((! feof($fh)) && ($info = fgetcsv($fh))) {
// $info[B] 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
$stmt->execute($info);
print “Inserted $info[0]\n”;
}
// Close the file
fclose($fh);
Example 9-10 prints:
Inserted Fish Ball with Vegetables
Inserted Spicy Salt Baked Prawns
Inserted Steamed Rock Cod
Inserted Sauteed String Beans
Inserted Confucius “Chicken”
Writing a CSV-formatted line is similar to reading one. The fputcsv() function takes a file handle and an array of values as arguments and writes those values, formatted as proper CSV, to the file. Example 9-11 uses fputcsv() along with fopen() and fclose() to retrieve information from a database table and write it to a CSV file.
Example 9-11. Writing CSV-formatted data to a file
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
} catch (Exception $e) {
print “Couldn’t connect to database: ” . $e->getMessage();
exit();
}
// Open the CSV file for writing
$fh = fopen(‘dish-list.csv’,’wb’);
$dishes = $db->query(‘SELECT dish_name, price, is_spicy FROM dishes’);
while ($row = $dishes->fetch(PDO::FETCH_NUM)) {
// Write the data in $row as a CSV-formatted string. fputcsv()
// adds a newline at the end.
fputcsv($fh, $row);
}
fclose($fh);
To send a page that consists only of CSV-formatted data back to a web client, you need to tell fputcsv() to write the data to the regular PHP output stream (instead of a file). 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 9-12 shows how to call the header() function with the appropriate arguments.
Example 9-12. 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 separate program
header(‘Content-Disposition: attachment; filename=”dishes.csv”‘);
Example 9-13 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 program from a user’s web browser.
Example 9-13. Sending a CSV file to the browser
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
} catch (Exception $e) {
print “Couldn’t connect to database: ” . $e->getMessage(); exit();
}
// 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”‘);
// Open a file handle to the output stream
$fh = fopen(‘php://output’,’wb’);
// Retrieve the info from the database table and print it
$dishes = $db->query(‘SELECT dish_name, price, is_spicy FROM dishes’);
while ($row = $dishes->fetch(PDO::FETCH_NUM)) {
fputcsv($fh, $row);
}
In Example 9-13, the first argument to fputcsv() is php://output. This is a special built-in file handle which sends data to the same place that print sends it to.
To generate more complicated spreadsheets that include formulas, formatting, and images, use the PHPOffice PHPExcel package.
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.