You want to use a relational database that doesn’t involve a separate server process.
Use SQLite. This robust, powerful database program is easy to use and doesn’t require running a separate server.
An SQLite database is just a file. Example 10-7 creates an SQLite database, populates it with a table if it doesn’t already exist, and then puts some data into the table.
Example 10-7. Creating an SQLite database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<programlisting>$db = new PDO('sqlite:/tmp/zodiac'); // Create the table and insert the data atomically $db->beginTransaction(); // Try to find a table named 'zodiac' $q = $db->query("SELECT name FROM sqlite_master WHERE type = 'table'" . " AND name = 'zodiac'"); // If the query didn't return a row, then create the table // and insert the data if ($q->fetch() === false) { $db->exec(<<<_SQL_ CREATE TABLE zodiac ( id INT UNSIGNED NOT NULL, sign CHAR(11), symbol CHAR(13), planet CHAR(7), element CHAR(5), start_month TINYINT, start_day TINYINT, end_month TINYINT, end_day TINYINT, PRIMARY KEY(id) ) _SQL_ ); // The individual SQL statements $sql=<<<_SQL_ INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19); INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20); INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21); INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22); INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22); INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22); INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23); INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',10,24,11,21); INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,<?pdf-cr?>21); INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19); INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,<?pdf-cr?>18); INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20); _SQL_; // Chop up each line of SQL and execute it foreach (explode("\n",trim($sql)) as $q) { $db->exec(trim($q)); } $db->commit(); } else { // Nothing happened, so end the transaction $db->rollback(); }</programlisting> |
Because SQLite databases are just regular files, all the precautions and gotchas that apply to file access in PHP apply to SQLite databases.
The user that your PHP process is running as must have permission to read from and write to the location where the SQLite database is.
It is an extremely good idea to make this location somewhere outside your web server’s document root.
If the database file can be read directly by the web server, then a user who guesses its location can retrieve the entire thing, bypassing any restrictions you’ve built into the queries in your PHP programs.
In PHP, the sqlite extension provides regular SQLite access as well as a PDO driver for SQLite version 2. The pdo_sqlite extension provides a PDO driver for SQLite version 3.
If you’re starting from scratch, use the PDO driver for SQLite 3, because it’s faster and has more features. If you already have an SQLite 2 database, consider using the PDO drivers to migrate to SQLite 3.
The sqlite_master table referenced in Example 10-7 is special system table that holds information about other tables—so it’s useful in determining whether a particular table exists yet.
Other databases have their own ways of providing this sort of system metadata.
