Using CodeIgniter 2 with SQLite 3 databases

Note: If you haven’t updated to CodeIgniter 2.1.1 – do so. There is a bug in 2.1 preventing it from working with SQLite databases properly, see the changelog.

The process of connecting to a SQLite 3 database with CodeIgniter isn’t straightforward. You have to use the (built-in) PDO driver and put the path to the database, with protocol prefix, in the hostname parameter, here’s an example:

/application/config/database.php

...
$db['default']['hostname'] = 'sqlite:'.APPPATH.'db/your_database.sqlite';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'pdo';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;
...

This assumes you have your database under:

/application/db/your_database.sqlite

If the file does not exist, an empty database will be created at that location.

After you have connected, you can use the Active Record class normally to query the db, for example:

$query = $this->db->get('users');
foreach ($query->result() as $row)
{
    echo $row->id . '<br/>';
}

Remember that you need to build your table structures first with a tool such as phpLiteAdmin.

Why does the syntax differ when conecting to a SQLite 3 database?
The reason you have to put the database path in the hostname field is the way CI instantiates the PDO object:

system/database/drivers/pdo/pdo_driver.php

...
function db_connect()
{
    $this->options['PDO::ATTR_ERRMODE'] = PDO::ERRMODE_SILENT;
    return new PDO($this->hostname, $this->username, $this->password, $this->options);
}
...

Explore posts in the same categories: Computers, Development, Technical solutions

Tags: codeigniter, php, phpliteadmin, sqlite

You can comment below, or link to this permanent URL from your own site.