SQLite

From Phpmaniac

1 What is SQLite
2 Why use SQLite
3 How do we use SQLite
4 Creating a database table
5 Inserting data
6 Select data

What is SQLite

SQLite is a lightweight datbase that requires no server. This is because the database engine is written into the driver. This makes it a perfect replacement for storing data in files.

Why use SQLite

Storing data in files is prone to corruption if a single page is accessed more than once simultaneously. SQLite handles all the file locking for you which means that you won't have corruption issues and has the added advantage of being a full database which means that skills gained by using SQLite will be transferable to other database systems.

There is a small overhead in using SQLite, but it is insignificant.

How do we use SQLite

In this example we will access SQLite using PHP PDO. This has the added advantage of being portable so that if you choose to move your database to another back end such as PostgreSQL, the move will only require changing the PDO DSN (Described in PHP PDO).

Creating a database table

Creating a database in SQL is the same as creating a table in other Database management systems.

<?php
        $create_sql = "CREATE TABLE `people` (
                `first_name` text NOT NULL,
                `last_name` text NOT NULL,
                `age` int NOT NULL
                );";                                // Create SQL
 
        $db_conn = new PDO("sqlite:test_database"); // Connect to the database
        $query = $db_conn->prepare($create_sql);    // Create a prepared statement
        $query->execute();                          // Execute the query
?>

Inserting data

Inserting data into SQLite is the same as inserting data into any other database

<?php
        $insert_sql = "insert into people (first_name, last_name, age) values (:first_name, :last_name, :age)";
 
        $db_conn = new PDO("sqlite:test_database");    // Connect to the database
        $query = $db_conn->prepare($insert_sql);       // Create a prepared statement
 
        $first_name = "Leigh";                      
        $last_name = "Finch";
        $age = 25;
 
        $query->bindParam(":first_name", $first_name); // bind the variable $first_name to :first_name
        $query->bindParam(":last_name", $last_name);   // bind the variable $last_name to :last_name
        $query->bindParam(":age", $age);               // bind the variable $age to :age
 
        $query->execute();                             // Execute the query
?>

Select data

Select statements are exactly the same as other database systems.

<?php
        $select_sql = "select * from people";          // Simple select squery
 
        $db_conn = new PDO("sqlite:test_database");    // Connect to the database
        $query = $db_conn->prepare($select_sql);       // Create a prepared statement
 
        $query->execute();                             // Execute the query
        while($row = $query->fetch())                  // Fetch the rows one by one
        {
                var_dump($row);                        // Dump the results to output
        }
 
?>

array(6) {
  ["first_name"]=>
  string(5) "Leigh"
  [0]=>
  string(5) "Leigh"
  ["last_name"]=>
  string(5) "Finch"
  [1]=>
  string(5) "Finch"
  ["age"]=>
  string(2) "25"
  [2]=>
  string(2) "25"
}

del.icio.us