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 php.net site I got following code snippet

<?php
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');
    var_dump(sqlite_fetch_array($result)); 
} else {
    die($sqliteerror);
}
?>

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

Enjoy

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…

 

<?php
 try {
 // Create (connect to) SQLite database in file
 $file_db = new PDO('sqlite:users.sqlite3');
 // Set errormode to exceptions
 $file_db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 /**************************************
 * Create tables *
 **************************************/
 // Create table users with two colums firstname and last name
 $file_db->exec("CREATE TABLE IF NOT EXISTS tbl_users (
 id INTEGER PRIMARY KEY, 
 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
 $stmt->execute();
 }
 
 // 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();
 }
?>
Advertisements

What you think ? 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