SQLite

Introduction
Installing/Configuring
Predefined Constants
SQLite Functions
- sqlite_array_query — Execute a query against a given database and returns an array
- sqlite_busy_timeout — Set busy timeout duration, or disable busy handlers
- sqlite_changes — Returns the number of rows that were changed by the most recent SQL statement
- sqlite_close — Closes an open SQLite database
- sqlite_column — Fetches a column from the current row of a result set
- sqlite_create_aggregate — Register an aggregating UDF for use in SQL statements
- sqlite_create_function — Registers a "regular" User Defined Function for use in SQL statements
- sqlite_current — Fetches the current row from a result set as an array
- sqlite_error_string — Returns the textual description of an error code
- sqlite_escape_string — Escapes a string for use as a query parameter
- sqlite_exec — Executes a result-less query against a given database
- sqlite_factory — Opens an SQLite database and returns an SQLiteDatabase object
- sqlite_fetch_all — Fetches all rows from a result set as an array of arrays
- sqlite_fetch_array — Fetches the next row from a result set as an array
- sqlite_fetch_column_types — Return an array of column types from a particular table
- sqlite_fetch_object — Fetches the next row from a result set as an object
- sqlite_fetch_single — Fetches the first column of a result set as a string
- sqlite_fetch_string — Alias of sqlite_fetch_single
- sqlite_field_name — Returns the name of a particular field
- sqlite_has_more — Finds whether or not more rows are available
- sqlite_has_prev — Returns whether or not a previous row is available
- sqlite_key — Returns the current row index
- sqlite_last_error — Returns the error code of the last error for a database
- sqlite_last_insert_rowid — Returns the rowid of the most recently inserted row
- sqlite_libencoding — Returns the encoding of the linked SQLite library
- sqlite_libversion — Returns the version of the linked SQLite library
- sqlite_next — Seek to the next row number
- sqlite_num_fields — Returns the number of fields in a result set
- sqlite_num_rows — Returns the number of rows in a buffered result set
- sqlite_open — Opens an SQLite database and create the database if it does not exist
- sqlite_popen — Opens a persistent handle to an SQLite database and create the database if it does not exist
- sqlite_prev — Seek to the previous row number of a result set
- sqlite_query — Executes a query against a given database and returns a result handle
- sqlite_rewind — Seek to the first row number
- sqlite_seek — Seek to a particular row number of a buffered result set
- sqlite_single_query — Executes a query and returns either an array for one single column or the value of the first row
- sqlite_udf_decode_binary — Decode binary data passed as parameters to an UDF
- sqlite_udf_encode_binary — Encode binary data before returning it from an UDF
- sqlite_unbuffered_query — Execute a query that does not prefetch and buffer all data
- sqlite_valid — Returns whether more rows are available

User Contributed Notes

6 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.

Anonymous

5 years ago


As of July 2010, there are two ways to use SQLite from PHP:
- procedural: sqlite (=sqlite2), sqlite3
- object-oriented: SQLite3, PDO

arriolasimon at gmail dot com

3 months ago


just in case some is facing the error:
"file is encrypted or is not a database"

check this page out:
http://stackoverflow.com/questions/1513849/error-file-is-encrypted-or-is-not-a-database

nosdudefr at gmail dot com

4 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 : 

<?php
    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.

<?php
    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 {
        die($err);
    }
?>

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