User Tools

Site Tools


db:sqlite_small_tutorial

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

db:sqlite_small_tutorial [2013/03/16 17:40] (current)
Line 1: Line 1:
 +==== SQLite ====
 +
 +Introduction to SQLite \\
 +27 March 2008
 +
 +
 +=== What is SQLite? ===
 +
 +
 +SQLite is a software library that implements a self-contained,​ serverless, zero-configuration,​ transactional SQL database engine. More information on SQLite website.
 +
 +SQLite library is already included in PHP5, so you must enabled it in php.ini. Check the php website or other sources on the internet to see the installation procedure.
 +
 +
 +=== First steps. ===
 +
 +
 +So, let's begin our journey... \\
 +Let's suppose we have a folder called '​test'​ somewhere in the webserver document root and we make a script inside called '​index.php'​.
 +
 +We __create then the database__:
 +
 +<code php>
 +    $db = new SQLiteDatabase("​test.db"​) or die("​Could not open database"​); ​
 +</​code>​
 +
 +
 +which anyway is similar to '​sqlite_factory'​ function:
 +
 +<code php>
 +    $db = sqlite_factory(“test.db”);​
 +</​code>​
 +
 +but we'll stick with the OO style.
 +
 +
 +Next we __create a table__ using '​query'​ method:
 +
 +<code php>
 +    $db->​query("​CREATE TABLE users (id INTEGER PRIMARY KEY,name CHAR(20), email CHAR(50))"​);​
 +</​code>​
 +
 +We created the first field '​integer primary key' because a column defined this way is with autoincrement. The value for this field should be inserted as NULL as 
 +you see below.
 +
 +
 +__Inserting some data__
 +
 +<code php>
 +$db->​query("​BEGIN;​
 +    INSERT INTO users (id, name, email) VALUES (NULL, 'John Doe', '​john@example.org'​);​
 +    INSERT INTO users (id, name, email) VALUES (NULL, 'Rox Cox', '​rcox@example.org'​);​
 +    INSERT INTO users (id, name, email) VALUES (NULL, 'Jonny Daemon',​ '​jonnyd@example.org'​);​
 +    COMMIT;
 +    ");
 +</​code>​
 +
 +
 +__Fetching the data__
 +
 +So, we have a table with some data inside. Let's try to retrieve its data:
 +
 +<code php>
 +$result = $db->​arrayQuery('​SELECT * FROM users', ​  ​SQLITE_ASSOC);​
 +
 +foreach ( $result as $res ) {
 +    echo 'ID: ' .$res['​id'​]. ' NAME: ' .$res['​name'​]. ' EMAIL: ' .$res['​email'​] .'<​br />';​
 +}
 +</​code>​
 +
 +We're using here arrayQuery method but this is best suited only for 45 rows or less. See link for more informations. If we have more rows, then we should use $db->​query() instead.
 +
 +Because the arrayQuery returned an array, we can use foreach to iter through every item.
 +
 +What about if we had been using the query method. The code would look like:
 +
 +<code php>
 +$result = $db->​query('​SELECT * FROM users'​);​
 +
 +while ( $result->​valid() ) {
 +    $row = $result->​current();​
 +    echo 'ID: ' .$row['​id'​]. ' NAME: ' .$row['​name'​]. ' EMAIL: ' .$row['​email'​] .'<​br />';​
 +    $result->​next();​
 +}
 +</​code>​
 +
 +
 +In a similar way, we can use '​**fetchAll**'​ method (who will return an array of arrays).
 +
 +<code php>
 +$result = $db->​query('​SELECT * FROM users'​);​
 +
 +$data = $result->​fetchAll(SQLITE_ASSOC);​
 +foreach ( $data as $res ) {
 +    echo 'ID: ' .$res['​id'​]. ' NAME: ' .$res['​name'​]. ' EMAIL: ' .$res['​email'​] .'<​br />';​
 +}
 +</​code>​
 +
 +Another way to do that is (we also check if there are any results at all):
 +
 +<code php>
 +if ( $result->​numRows() > 0) {
 +    while ( $row = $result->​fetch() ) {
 +        echo 'ID: ' .$row['​id'​]. ' NAME: ' .$row['​name'​]. ' EMAIL: ' .$row['​email'​] .'<​br />';​
 +    }
 +}
 +</​code>​
 +
 +So, if you want to retrieve all the results at one moment, or every line it's up to you, function of your goals.
 +
 +__Closing the connection__
 +
 +<code php>
 +unset($db);
 +</​code>​
 +
 +in a procedural programming style, you should write:
 +
 +<code php>
 +sqlite_close($db);​
 +</​code>​
 +
  
db/sqlite_small_tutorial.txt · Last modified: 2013/03/16 17:40 (external edit)