Quick Review on SQLite with PHP

Searching for a minimal size of database i found SQLITE the best. one simple file that not take more space (in KB) and supports multiple table, index view, triggers and many more, all data is stored in one single file and work as a database engine.
Working with php, i use Sqlite3 version.

To execute a sqlite statement webserver should have a full permission to this directory and database file.
Suppose we have a sqlite database file name sqlitedb.s3db (extension is s3db). first let start with creation a database.

Creating a Connection and Database

// creating a connection with database
$dbConnSqlite = new SQLite3("sqlitedb.s3db");
// database creation
$sqlCreateTable="Create table person(
 name text,
 $sqlInsert = “Insert into person values(‘xyz’,2)”;

Above example open a SQLite3 database “sqlitedb” connection, create a table “person” and insert one row to database.
Sqlite is not rich in datatype but have some common datatype integer, varchar, text, which fullfil our requirement. In this example i am using query() to execute sql statment.

Fetching Data

$sqlPerson= "select * from person";
if($resSql = $dbConnSqlite->query($sqlPerson))
 while($rowSql = $resSql->fetchArray(SQLITE3_ASSOC))
 echo "Person name is: " . $rowSql['name'] . "and his age is" . $rowSql['age'] . "\n";
 Echo "Query Failed";

In above example call fetchArray to read data from database with passing “SQLITE3_ASSOC” flag.

In Sqlite libraries flags are of three type:
1> SQLITE_ASSOC: fetch associative array
2> SQLITE_NUM: fetch index array
3> SQLITE_BOTH :fetch assoctive array with index

Function to get Number of Rows in table and last insert id

// $numRows give number of rows
$numRows = sqlite3_num_rows($sqlPerson);

// This function return number of rows
public function sqlite3_num_rows($query, $flag="both")
 global $dbConnSqlite;
 $result = $dbConnSqlite->query($query);
 if(!$result) //make sure the result is valid
 return NULL;

 $flag = SQLITE3_ASSOC;
 else if($flag=="num")
 $flag = SQLITE3_NUM;
 $flag = SQLITE3_BOTH;
 $numArray = array();
 $i = 0;
 while($res = $result->fetchArray($flag))
 $numArray[$i] = $res;
 $numRows = count($numArray);
 return $numRows;

// To get last insert id
$sqlInsert = “Insert into person values(‘johny’,22)”;

 echo "Query failed.";
 $insertId = "SELECT last_insert_rowid() AS insert_id";
 $result        = $dbConnSqlite->query($insertId);
 // Call to function
 $numRows    = sqlite3_num_rows($insertId, "num");
 if($numRows > 0)
 $rows = $result->fetchArray(SQLITE3_ASSOC);
 $insert_id    = $rows['insert_id'];

First off, we have simple function that return number of rows in table. now to get last insert id in sqlite it has select statment “SELECT last_insert_rowid() AS insert_id”.

In Sqlite we also have unique key, foreign key constraints and can create trigger too as we create similar in other databases. this article is a way to start experimenting  a simple file that give you a big result.

References: www.sqlite.org

One Response to Quick Review on SQLite with PHP

  1. ajay sarwai says:

    Interesting article

Leave a Reply

Your email address will not be published. Required fields are marked *