PHP & SQLite3 with PDO

For the major upgrade of my NeuroMind iPhone application I also want to have a webbased interface, which I am testing at I read that people had trouble getting SQLite3 to work in combination with PHP5, so I will post what worked for me.

I created the SQLite3 database with SQLiteManager version 3 for Mac (also available for Windows). I have been using this tool, including the previous version, for over 2 years now and prefer it over the FireFox plugin or other tools I tested. I name my databases with a *.db extension, but you can choose what you like (*.sql or *.sqlite are fine too).

Now, there is a difference between handling SQLite2 and SQLite3 files with PHP. For SQLite2 you can use a procedural approach like this:

For SQLite3 you need to use an object-oriented approach called PDO (PHP Data Objects):

Now there is an isse about counting the number of rows. In the procedural approach you could simply call the following function:

This does not work for SQLite3. You need a small workaround here like this:

This was all I needed to know to get started. Hope it is helpful for you too!

In the next blog post I will explain how I used this in combination with AppCelerator’s Titanium to create a workaround for a database issue on the Android OS.

One Response to “PHP & SQLite3 with PDO”

  1. Mark says:

    SQLite3 is a different animal than SQLite2

    Trying your last example on an SQLite3 query which has 2 results in the database, when using the aforementioned method returns 1 result using count(), you can’t easily get a number of results because the makers of SQLite3 claim that it is impossible to do because it would mean iterating every record to obtain, Utter bull crap I say given that to query the database for information with specific terms, the search has to iterate all the objects in the database to start with or is SQLite3 makers trying to tell us that you will get results but you may not know how many because it does not give a full set of results…

    Anyone wanting to count the number of rows returned by a query using SQL3 will need to do something like this.

    $db = new SQLite3("problemswith.sql3");
    // the query
    $results = $db->query( "Select * FROM
    wordlist WHERE word='$searchword';" );
    // the query to count results
    $count = $db->query( "Select count(*) FROM wordlist WHERE word='$searchword';" )->fetchArray()[0];
    // the total number of records
    $total = $db->query("SELECT count(*) FROM wordlist;")->fetchArray()[0];
    echo "Found {$count} results from {$total} records.";

    using ->fetchArray()[0]; like that returns the number (int) value of the number of results in the query which is an array of data with [0] being the number.

Leave a Reply

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