How to configure SQLite with php?

Requirements:

The SQLite extension is enabled by default as of PHP 5.0. Beginning with PHP 5.4, the SQLite extension is available only via PECL.

Installation:

Windows users must enable php_sqlite.dll inside of php.ini in order to use these functions.

Windows builds must also enable PDO because as of PHP 5.1.0 it depends on it. So, php.ini will end up with something like this:

extension=php_pdo.dll
extension=php_sqlite.dll

Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

SQLITE3_ASSOC (integer)
Specifies that the Sqlite3Result::fetchArray() method shall return an array indexed by column name as returned in the corresponding result set.
SQLITE3_NUM (integer)
Specifies that the Sqlite3Result::fetchArray() method shall return an array indexed by column number as returned in the corresponding result set, starting at column 0.
SQLITE3_BOTH (integer)
Specifies that the Sqlite3Result::fetchArray() method shall return an array indexed by both column name and number as returned in the corresponding result set, starting at column 0.
SQLITE3_INTEGER (integer)
Represents the SQLite3 INTEGER storage class.
SQLITE3_FLOAT (integer)
Represents the SQLite3 REAL (FLOAT) storage class.
SQLITE3_TEXT (integer)
Represents the SQLite3 TEXT storage class.
SQLITE3_BLOB (integer)
Represents the SQLite3 BLOB storage class.
SQLITE3_NULL (integer)
Represents the SQLite3 NULL storage class.
SQLITE3_OPEN_READONLY (integer)
Specifies that the SQLite3 database be opened for reading only.
SQLITE3_OPEN_READWRITE (integer)
Specifies that the SQLite3 database be opened for reading and writing.
SQLITE3_OPEN_CREATE (integer)
Specifies that the SQLite3 database be created if it does not already exist.

The SQLite3 class

A class that interfaces SQLite 3 databases.

SQLite3 {
/* Methods */
public bool busyTimeout ( int $msecs )
public int changes ( void )
public bool close ( void )
public __construct ( string $filename [, int $flags [, string $encryption_key ]] )
public bool createAggregate ( string $name , mixed $step_callback , mixed $final_callback[, int $argument_count = -1 ] )
public bool createCollation ( string $name , callable $callback )
public bool createFunction ( string $name , mixed $callback [, int $argument_count = -1 ] )
public static string escapeString ( string $value )
public bool exec ( string $query )
public int lastErrorCode ( void )
public string lastErrorMsg ( void )
public int lastInsertRowID ( void )
public bool loadExtension ( string $shared_library )
public void open ( string $filename [, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE [, string $encryption_key ]] )
public SQLite3Stmt prepare ( string $query )
public SQLite3Result query ( string $query )
public mixed querySingle ( string $query [, bool $entire_row = false ] )
public static array version ( void )

}

<?php

$db = new SQLite3(‘:memory:’);

$db->createCollation(‘translit_ascii’, function ($a, $b) {
$a = transliterator_transliterate(‘Any-Latin; Latin-ASCII; Lower()’, $a);
$b = transliterator_transliterate(‘Any-Latin; Latin-ASCII; Lower()’, $b);
return strcmp($a, $b);
});

$db->exec(‘
CREATE TABLE people (name COLLATE translit_ascii);
INSERT INTO people VALUES (“Émilie”);
INSERT INTO people VALUES (“Zebra”);
INSERT INTO people VALUES (“Emile”);
INSERT INTO people VALUES (“Arthur”);
‘);

$stmt = $db->prepare(‘SELECT name FROM people ORDER BY name;’);
$result = $stmt->execute();

while ($row = $result->fetchArray())
{
echo $row[‘name’] . PHP_EOL;
}

// Displays:
// Arthur
// Emile
// Émilie
// Zebra

?>

The SQLite3Stmt class

A class that handles prepared statements for the SQLite 3 extension.

SQLite3Stmt {
/* Methods */
public bool bindParam ( string $sql_param , mixed &$param [, int $type ] )
public bool bindValue ( string $sql_param , mixed $value [, int $type ] )
public bool clear ( void )
public bool close ( void )
public SQLite3Result execute ( void )
public int paramCount ( void )
public bool reset ( void )

}

This method can be used like this :

<?php

$db = new SQLite3(‘:memory:’);
$db->exec(‘CREATE TABLE test (column);’);

$stmt  = $db->prepare(‘SELECT column FROM test;’);
var_dump($stmt->readOnly()); // -> true

$stmt  = $db->prepare(‘INSERT INTO test (42);’);
var_dump($stmt->readOnly()); // -> false

?>

You can even execute your sql commands online here

SQLite – Useful Functions

SQLite has many built-in functions for performing processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form. For more details, you can check official documentation for SQLite:

S.N. Function & Description
1 SQLite COUNT Function
The SQLite COUNT aggregate function is used to count the number of rows in a database table.
2 SQLite MAX Function 
The SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column.
3 SQLite MIN Function
The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
4 SQLite AVG Function 
The SQLite AVG aggregate function selects the average value for certain table column.
5 SQLite SUM Function
The SQLite SUM aggregate function allows selecting the total for a numeric column.
6 SQLite RANDOM Function
The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
7 SQLite ABS Function
The SQLite ABS function returns the absolute value of the numeric argument.
8 SQLite UPPER Function
The SQLite UPPER function converts a string into upper-case letters.
9 SQLite LOWER Function
The SQLite LOWER function converts a string into lower-case letters.
10 SQLite LENGTH Function
The SQLite LENGTH function returns the length of a string.
11 SQLite sqlite_version Function
The SQLite sqlite_version function returns the version of the SQLite library.

You can download pdf file from here

References:

http://sqlite.org/

http://in1.php.net/sqlite3

http://zetcode.com/db/sqlite/

http://www.tutorialspoint.com/sqlite/

What is SQLite and when to use it??

What is SQLite?

To define in an simple manner, SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is a relational database management system contained in a C programming library. In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but an integral part of it.

SQLite is a popular choice as embedded database for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems, among others. SQLite has many bindings to programming languages.

Unlike client–server database management systems, the SQLite engine has no standalone processes with which the application program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the application program. The library can also be called dynamically. The application program uses SQLite’s functionality through simple function calls, which reduce latency in database access: function calls within a single process are more efficient than inter-process communication. SQLite stores the entire database (definitions, tables, indices, and the data itself) as a single cross-platform file on a host machine. It implements this simple design by locking the entire database file during writing. SQLite read operations can be multitasked, though writes can only be performed sequentially.

In the simplest terms, SQLite is a public-domain software package that provides a relational database management system, or RDBMS. Relational database systems are used to store user-defined records in large tables. In addition to data storage and management, a database engine can process complex query commands that combine data from multiple tables to generate reports and data summaries. Other popular RDBMS products include Oracle Database, IBM’s DB2, and Microsoft’s SQL Server on the commercial side, with MySQL and PostgreSQL being popular open source products.

The “Lite” in SQLite does not refer to its capabilities. Rather, SQLite is lightweight when it comes to setup complexity, administrative overhead, and resource usage. SQLite is defined by the following features:

Serverless
SQLite does not require a separate server process or system to operate. The SQLite library accesses its storage files directly.

Zero Configuration
No server means no setup. Creating an SQLite database instance is as easy as opening a file.

Cross-Platform
The entire database instance resides in a single cross-platform file, requiring no administration.

Self-Contained
A single library contains the entire database system, which integrates directly into a host application.

Small Runtime Footprint
The default build is less than a megabyte of code and requires only a few megabytes of memory. With some adjustments, both the library size and memory use can be significantly reduced.

Transactional
SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.

Full-Featured
SQLite supports most of the query language features found in the SQL92 (SQL2) standard.

Highly Reliable
The SQLite development team takes code testing and verification very seriously.

Overall, SQLite provides a very functional and flexible relational database environment that consumes minimal resources and creates minimal hassle for developers and users.

Misconceptions

Don’t be deceived into thinking that Sqlite is only for testing and development. For example, it works fine for websites receiving up to 100,000 hits a day– and this is a conservative limit. The maximum size for a Sqlite database is 140 Terabytes (which should be enough, right?), and it can be substantially faster than a full-blown RDBMS. The full database and all other necessary data is stored in a normal file in the host’s file system, so no separate server process is needed (cutting out all need for slow inter-process communication).

Best example:

It’s good for situations where you don’t have access to a “real” database and still want the power of a relational db. For example, Firefox stores a bunch of information about your settings/history/etc in an SQLite database. You can’t expect everyone that runs firefox to have MySQL or postgre installed on their machine.