PHP Tutorial - Creating and Modifying SQLite Databases

SQLite is a portable, self-contained database that is perfect for small projects that don't want or need the overhead of a dedicated database engine. According to their website, it's also the "most widely deployed database engine in the world". In this tutorial, we'll be using PHP to create, open, modify, and read data from a SQLite database. You might be surprised at how easy it is.

The SQLite libraries are built right in to PHP 5, so that's the version I'll be using for this tutorial. You don't have to worry about licensing issues when using SQLite, since it's in the public domain.

Creating or Opening SQLite Database

Before we can start putting anything into a database or reading anything out of it, we first need to create one. The SQLiteDatabase constructor will do this for us:

try 
{
  //create or open the database
  $database = new SQLiteDatabase('myDatabase.sqlite', 0666, $error);
}
catch(Exception $e) 
{
  die($error);
}

The first parameter of the constructor is the database file you'd like to create or open. It doesn't need any particular extension, however I chose .sqlite so I could easily identify SQLite databases using a file explorer. The second parameter is currently ignored by the library, however it will eventually control how the database will be opened (e.g. read-only). The default value is 0666. The last parameter will contain an error message if the database creation/open fails.

According to the documentation, the constructor will return a database handle on success or a boolean (false) on fail. However, when I tested a failure condition, it threw an exception instead - so I surrounded the creation with a try-catch block. I guess that's good advice for any developer - don't always trust the documentation.

Creating New SQLite Table

All right, now that we've successfully opened a SQLite database, let's start putting some data into it. I'm going to begin by creating a new table to hold some information about movies: Title, Director, and Year.

//add Movie table to database
$query = 'CREATE TABLE Movies ' .
         '(Title TEXT, Director TEXT, Year INTEGER)';

if(!$database->queryExec($query, $error))
{
  die($error);
}

Adding tables is pretty straight forward. Simply build the SQL query then execute it using queryExec. SQLite doesn't quite have as many data types as larger databases (like MySQL), but it has enough to get the job done. You can see all of the supported data types on SQLite's website.

If the query fails for any reason, queryExec will return false and $error will be populated with the error message. A common reason why the query will fail is that the table already exists.

Inserting Data Into SQLite Database

We've got our database, we've got our table, now it's time to push some rows into our Movies table. Inserting data will be done exactly like creating tables, except we'll use a different query.

//insert data into database
$query = 
  'INSERT INTO Movies (Title, Director, Year) ' .
  'VALUES ("The Dark Knight", "Christopher Nolan", 2008); ' .

  'INSERT INTO Movies (Title, Director, Year) ' .
  'VALUES ("Cloverfield", "Matt Reeves", 2008); ' .

  'INSERT INTO Movies (Title, Director, YEAR) ' .
  'VALUES ("Beverly Hills Chihuahua", "Raja Gosnell", 2008)';

if(!$database->queryExec($query, $error))
{
  die($error);
}

SQLite is capable of executing multiple queries at once - separated by semi-colons. Here I added three movies to my database using a single query. Just like before, if the query fails, the function will return false and the error message will be populated.

Reading Data From SQLite Database

The last thing we're going to do today is read the data that we just put into the database back out again.

//read data from database
$query = "SELECT * FROM Movies";
if($result = $database->query($query, SQLITE_BOTH, $error))
{
  while($row = $result->fetch())
  {
    print("Title: {$row['Title']} <br />" .
          "Director: {$row['Director']} <br />".
          "Year: {$row['Year']} <br /><br />");
  }
}
else
{
  die($error);
}

First off, you'll need to create the SQL SELECT statement. In this case, I just want everything in the database. Next, we call query. The first parameter is the query we just created. The second parameter tells the SQLite library how to form the output data. SQLITE_NUM will return results as arrays only accessible by index. SQLITE_ASSOC will return arrays only accessible by column name. The default value, SQLITE_BOTH, returns arrays accessible by name or index. The last parameter will be populated with an error message if one occurs.

To get the results, we simply call fetch on the result of the query call. Fetch will return an array for each record returned or false if there are no more. Since we chose SQL_BOTH, we can now reference the arrays by column name to get the values. I simply output some HTML code with the Title, Director, and Year.

That's it for creating, opening, modifying, and reading SQLite databases using PHP. Below is the entire script we created today.

try 
{
  //create or open the database
  $database = new SQLiteDatabase('myDatabase.sqlite', 0666, $error);
}
catch(Exception $e) 
{
  die($error);
}

//add Movie table to database
$query = 'CREATE TABLE Movies ' .
         '(Title TEXT, Director TEXT, Year INTEGER)';

if(!$database->queryExec($query, $error))
{
  die($error);
}

//insert data into database
$query = 
  'INSERT INTO Movies (Title, Director, Year) ' .
  'VALUES ("The Dark Knight", "Christopher Nolan", 2008); ' .

  'INSERT INTO Movies (Title, Director, Year) ' .
  'VALUES ("Cloverfield", "Matt Reeves", 2008); ' .

  'INSERT INTO Movies (Title, Director, YEAR) ' .
  'VALUES ("Beverly Hills Chihuahua", "Raja Gosnell", 2008)';

if(!$database->queryExec($query, $error))
{
  die($error);
}

//read data from database
$query = "SELECT * FROM Movies";
if($result = $database->query($query, SQLITE_BOTH, $error))
{
  while($row = $result->fetch())
  {
    print("Title: {$row['Title']} <br />" .
          "Director: {$row['Director']} <br />".
          "Year: {$row['Year']} <br /><br />");
  }
}
else
{
  die($error);
}

When this script is executed, you should see the following output:

Title: The Dark Night
Director: Christopher Nolan
Year: 2008

Title: Cloverfield
Director: Matt Reeves
Year: 2008

Title: Beverly Hills Chihuahua
Director: Raja Gosnell
Year: 2008