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

6 Responses to Ease into SQLite 3 with PHP and PDO

  1. Erik says:

    Finaly a working php/sqlite3/pdo example, could not find documentation anywhere…

  2. Enrique says:

    Awesome! I’ve finally found a working example of php with sqlite3…

    Thanks!!

  3. jjc_Mtl says:

    This method seems to open SQLite 2.x databases only;
    did you try to open an existing SQLite 3.x database created by SQLite3?
    j

  4. Shaun says:

    Hi,
    when I first started working with SQLite, I did run into a similar problem with an SQLite 3.0 db that I created using SQLite Manager (the firefox plugin)…

    I found that I needed to make sure that my web-host supports SQLite 3.0+ and that my PHP page was being executed with PHP 5.0+.

    As a test, I just created a database similar to the one in this article using the latest command line sqlite3.exe (version 3.6.23.1) and tried it on my server. It worked fine.

    Check to see that your script is being executed as PHP 5.0+ and that the permissions on the uploaded sqlite db file are correct.

  5. rafalP says:

    YES
    This woeks well wits sqlite3. Pretty basic example. To be safer use this with bind params ;)

    Good Job
    Thanks

  6. Thank you very much for this code. I had a problem. The INSERT and UPDATE and CREATE TABLE etc queries does not function with exec() function. I can get data by query() but cannot execute any sql query by exec() function. How can i solve this?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>