Getting started with PHP PDO and SQLITE

In android i used to have sqlite db which is light weight and sufficiently smart for smaller operations

PHP too supports the SQLITE db operations so i searched for some time and found certain code to make use of the SQLITE in php


First thing i tried to create the db file, on site I got following code snippet

if ($db = sqlite_open('mysqlitedb', 0666, $sqliteerror)) { 
    sqlite_query($db, 'CREATE TABLE foo (bar varchar(10))');
    sqlite_query($db, "INSERT INTO foo VALUES ('fnord')");
    $result = sqlite_query($db, 'select bar from foo');
} else {

After I executed the code with sample values and some data, when i checked in SQLite db browser, it gave me version issue then i came across fact that This function only support database of SQLite 2 or below. For SQLite 3, you must use PDO.

So i started searching on SQLite with PDO

Finally some sort of working code I got which can create the db file execute some statements and finally that can be browsed via the sqlite browser below is the code with comments in between.. so


In following code explanation given for how to create sqlite db, create some tables, insert data, drop table, access the data.. and so on

The inline commenting is good enough to understand the code, so not writing here in much detail…


 try {
 // Create (connect to) SQLite database in file
 $file_db = new PDO('sqlite:users.sqlite3');
 // Set errormode to exceptions
 * Create tables *
 // Create table users with two colums firstname and last name
 $file_db->exec("CREATE TABLE IF NOT EXISTS tbl_users (
 firstname TEXT, 
 lastname TEXT 
 * Set initial data *
 // Array with some test data to insert to database 
 $users = array(
 array('firstname' => 'Abhijeet',
 'lastname' => 'K'),
 array('firstname' => 'Testing firstname',
 'lastname' => 'Testing lastname'),
 array('firstname' => 'Dinesh',
 'lastname' => 'Patil')
 // Prepare INSERT statement to SQLite3 file db
 $insert = "INSERT INTO tbl_users (firstname, lastname) 
 VALUES (:firstname, :lastname)";
 $stmt = $file_db->prepare($insert);
 // Bind parameters to statement variables
 $stmt->bindParam(':firstname', $firstname);
 $stmt->bindParam(':lastname', $lastname);
 // Loop thru all users data and execute prepared insert statement
 foreach ($users as $u) {
 // Set values to bound variables
 $firstname = $u['firstname'];
 $lastname = $u['lastname'];
 // Execute statement
 // Select all data from users tabel and display 
 $result = $file_db->query('SELECT * FROM tbl_users');
 foreach($result as $row) {
 echo "Firstname: " . $row['firstname'] . "&nbsp;";
 echo "Lastname: " . $row['lastname'] . "<br>";
 echo "<br>";
 * Drop tables *
 // Drop table users from file db
 $file_db->exec("DROP TABLE tbl_users");
 // Close file db connection
 $file_db = null;
 catch(PDOException $e) {
 // Print PDOException message
 echo $e->getMessage();