PHP & SQLite3 with PDO

Posted by Pieter Kubben in Technology | Tagged , , , | Leave a 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.

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>