add a note add a note

User Contributed Notes 4 notes

6 years ago
As of July 2010, there are two ways to use SQLite from PHP:
- procedural: sqlite (=sqlite2), sqlite3
- object-oriented: SQLite3, PDO
Andrew Paul Dickey
7 years ago
If you intend to implement 2.x releases of SQLite with your development you are in the right place, as this library is suitable for use with your application (reference http://us.php.net/manual/en/book.sqlite.php).

If you intend to use SQLite 3.x releases of SQLite with your development please refer to the section on PHP Data Objects, and specifically the PDO-SQLite implementation available at(references: http://us.php.net/manual/en/book.pdo.php , http://au2.php.net/manual/en/ref.pdo-sqlite.php).

It is my hope that this post will save both new users and experienced developers time during their initial or a new implementation of PHP & SQLite by encouraging them to use the appropriate libraries.
nosdudefr at gmail dot com
5 years ago
Regarding  the table creation you can optimize this code a bit by using the built in " CREATE TABLE IF NOT EXISTS <tablename>". This will let the table creation decision to the sqlite engine.

Then, if i may, your code could be something like :

if ($db = new SQLiteDatabase('filename')) {
// first let the engine check table, and create it eventualy
$q = @$db->query('CREATE TABLE IF NOT EXISTS tablename (id int, requests int, PRIMARY KEY (id))';

//the rest of the code, according error checks etc
// ...


For more "tweaks" feel free to look at sqlite language ref : http://www.sqlite.org/lang_createtable.html

Have fun with this powerfull&simple engine :)
saivert at saivert dot com
8 years ago
How to open a database, create a table if it doesn't exist and inserting initial value.

if ($db = new SQLiteDatabase('filename')) {
$q = @$db->query('SELECT requests FROM tablename WHERE id = 1');
        if (
$q === false) {
$db->queryExec('CREATE TABLE tablename (id int, requests int, PRIMARY KEY (id)); INSERT INTO tablename VALUES (1,1)');
$hits = 1;
        } else {
$result = $q->fetchSingle();
$hits = $result+1;
$db->queryExec("UPDATE tablename SET requests = '$hits' WHERE id = 1");
    } else {

Use this as boilerplate code for any new project using SQLite.
To Top