Ease into SQLite 3 with PHP and PDO

This is the second article in a series of short tutorials on PHP and SQlite.   In this article we will discuss how to use sqlite 3 from within PHP via the PDO libraries. If you are new to sqlite, please take a moment to view our previous article: Sqlite for beginners which covered the basics of getting a very simple sqlite 2 database up running with PHP.

What is PDO?

PDO is short for PHP Data Objects. It is basically a database abstraction layer which allows an application developer to interact with a database from behind an engine neutral interface. PDO has support for numerous database’s including MySQL, Oracle, PostgreSQL, MS SQL Server, SQlite and several other engines.

Working with PDO is a breeze

Not only does PDO provide a clean database abstraction layer, it is also very easy to use.  Unlike other abstraction methodologies that can take abstraction too far, PDO remains close to its roots as a generic database interface.  The beauty of the PDO solution is that it allows you focus on the data that you are using rather than on the database connection.

Requirements:

In order to use PDO with Sqlite, your webserver must support at least the following:

  • PHP5
  • SQLite 3+
  • PDO Driver for SQLite 3.x

As in the prior article, you can use the phpinfo() function to determine if your server has the required settings. Simply copy this code to a file on your server and execute it from a browser. Then use the browsers in-page search feature to locate the PDO section.

<?php
  phpinfo();
?>

A working PDO & Sqlite example

<?php
  try
  {
    //open the database
    $db = new PDO('sqlite:dogsDb_PDO.sqlite');

    //create the database
    $db->exec("CREATE TABLE Dogs (Id INTEGER PRIMARY KEY, Breed TEXT, Name TEXT, Age INTEGER)");    

    //insert some data...
    $db->exec("INSERT INTO Dogs (Breed, Name, Age) VALUES ('Labrador', 'Tank', 2);".
               "INSERT INTO Dogs (Breed, Name, Age) VALUES ('Husky', 'Glacier', 7); " .
               "INSERT INTO Dogs (Breed, Name, Age) VALUES ('Golden-Doodle', 'Ellie', 4);");

    //now output the data to a simple html table...
    print "<table border=1>";
    print "<tr><td>Id</td><td>Breed</td><td>Name</td><td>Age</td></tr>";
    $result = $db->query('SELECT * FROM Dogs');
    foreach($result as $row)
    {
      print "<tr><td>".$row['Id']."</td>";
      print "<td>".$row['Breed']."</td>";
      print "<td>".$row['Name']."</td>";
      print "<td>".$row['Age']."</td></tr>";
    }
    print "</table>";

    // close the database connection
    $db = NULL;
  }
  catch(PDOException $e)
  {
    print 'Exception : '.$e->getMessage();
  }
?>

This simple code will output the following table when executed:

Id Breed Name Age
1 Labrador Tank 2
2 Husky Glacier 7
3 Golden-Doodle Ellie 4

Conclusion…

As you can see, using PDO lends itself to code that is fast and easy to develop in the short term and simple to maintain in the long term.   When compared to the direct use of the the SQLite API bindings, PDO excels as a great programming tool that can help insulate the developer from future changes to the underlying database technology.  This holds true not only for SQlite, but also for the other databases that PDO supports.


References and resources

addLeave a comment