PHP & SQLite3 with PDO

Posted by Pieter Kubben in Technology | Tagged , , , | 1 Comment

For the major upgrade of my NeuroMind iPhone application I also want to have a webbased interface, which I am testing at NeuroDSS.com. 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:

1
2
3
4
5
6
// Opening a SQLite2 database using a procedural approach
$db = sqlite_open('mydatabase.db');
$result = sqlite_query($db, 'SELECT * FROM MyTable');
while ($row = sqlite_fetch_array($result)) {
    echo 'Example content: ' . $row['column1'];
}

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

1
2
3
4
5
6
// Opening a SQLite3 database using a object-oriented (PDO) approach
$db = new PDO('sqlite:mydatabase.db');
$result = $db->query('SELECT * FROM MyTable');
foreach ($result as $row) {
    echo 'Example content: ' . $row['column1'];
}

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

1
2
// Counting the number of rows a SQLite2 query returns
$row_count = sqlite_num_rows($result);

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

1
2
3
// Counting the number of rows a SQLite3 query returns
$rows = $result->fetchAll();
$row_count = count($rows);

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 *

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>