PHP Database Access Using an SQLite Database

PHP Database Access

Using an SQLite Database

Problem

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

Solution

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. Creates an SQLite database, populates it with a table if it doesn’t already exist, and then puts some data into the table.

Example   Creating an SQLite database

         <programlisting>$db = new PDO('sqlite:/tmp/zodiac');

         // Create the table and insert the data atomically

         $db-&gt;beginTransaction();

         // Try to find a table named 'zodiac'

         $q = $db-&gt;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-&gt;fetch() === false) {

              $db-&gt;exec(&lt;&lt;&lt;_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=&lt;&lt;&lt;_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-&gt;exec(trim($q));

                   }

                   $db-&gt;commit();

         } else {

                   // Nothing happened, so end the transaction

                   $db-&gt;rollback();

         }</programlisting>

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