Recipe 10.2. Using an SQLite Database

10.2.1. Problem

You want to use a relational database that doesn't involve a separate server process.

10.2.2. Solution

Use SQLite. This robust, powerful database program comes with PHP 5 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.

Creating an SQLite database

<?php $db = new PDO('sqlite:/usr/local/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,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,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(); } ?>

10.2.3. Discussion

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, since 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.

10.2.4. See Also

Documentation on SQLite at and on sqlite_master at

PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: