Free Sample Dataset for PHP in CSV, JSON, and SQLite
Use this fictional reading list to practise working with data in PHP. All three downloads contain the same eight rows. The dataset is small enough that you can check a query by hand.
Fields to expect
Section titled “Fields to expect”| Field | Meaning |
|---|---|
id | Unique positive integer |
title | Book title in UTF-8 |
category | fiction, science, or cooking |
pages | Positive integer |
status | queued, reading, or finished |
finished_on | Date in YYYY-MM-DD form. Blank in CSV and null in JSON and SQLite if the book is unfinished. |
The first title contains commas within one quoted CSV cell. The second contains an accented character. Use these rows to check your parser before you move on to a larger dataset. CSV fields arrive as strings. JSON and SQLite store id and pages as integers.
Read the CSV in PHP
Section titled “Read the CSV in PHP”Save the CSV and this code in the same folder. Save the PHP file as read-csv.php and run php read-csv.php. The first line should show the whole title Stars, Maps, and Tides.
<?php
$file = fopen(__DIR__ . '/library-books.csv', 'rb');if ($file === false) { exit("Could not open library-books.csv.\n");}
fgetcsv($file, escape: ''); // Skip the header.while (($row = fgetcsv($file, escape: '')) !== false) { echo $row[1], PHP_EOL;}fclose($file);Look up how to set a delimiter or escape character in the PHP manual. PHP fgetcsv documentation
Query the SQLite file with PDO
Section titled “Query the SQLite file with PDO”Save the SQLite file in the same folder as count-finished.php. Run php count-finished.php. This example requires PDO SQLite and should print 4.
<?php
$path = __DIR__ . '/library-books.sqlite';if (!is_file($path)) { exit("Download library-books.sqlite first.\n");}
$db = new PDO('sqlite:' . $path);$query = $db->prepare('SELECT COUNT(*) FROM books WHERE status = :status');$query->execute(['status' => 'finished']);echo $query->fetchColumn(), PHP_EOL;For the JSON version, read the file and decode it with json_decode($contents, true, 512, JSON_THROW_ON_ERROR). Walk through a complete example in the JSON and APIs lesson.
Check your result
Section titled “Check your result”- Count books by category. Expect 3 fiction, 3 science, and 2 cooking.
- Sum the page counts for finished books. Expect 640.
- Order the finished books by date, newest first. Expect
Rocks After Rainat the top. - Make a copy of the CSV and set a page count to zero. Write a check that reports the corresponding row before accepting it.
The dataset is fictional and available under the project’s MIT licence.