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/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s