The objective of this article is to help out people who want to start programming with SQLite (http://www.sqlite.org) which is a new database that has been added as default to the new PHP 5. It´s a embedded type database, for now it´s the most reliable lightweight database on the market.

Documentation can be found at : http://www.php.net/sqlite

The biggest advantage of working with a SQLite database is a fast access local database.

If you are running PHP 5 + please use either of the examples below but if not please use the php reference only.

Let´s open up the database and select it´s values.

/* open database if exists, if not it creates one (php reference)*/

$db = sqlite_open('mydb.sqlite', 0666, $sqliteerror);

/* alternative to create a new database (class reference)*/

$db = new SQLiteDatabase("mydb.sqlite");

Either of the following queries will work in PHP, one is by the PHP function reference and the other is by the class reference (class contains functions inside).

/*Thee first method of query is this (php function reference)*/
   $query = sqlite_query($db, "SELECT name FROM sqlite_master WHERE type='table'");

/* the second is this way (class reference)*/

$query = $db->query("SELECT name FROM sqlite_master WHERE type='table'");

Let´s fetch the results from the database, again two example of how you can either fetch from the function or class reference. I put in “$row[0]” because you may reference each field by number or name when on value retrieval, so:

If have one field on your table with at least one value, print results.

Else print “no value”.

/*if there are any values in query do while*/

if(sqlite_num_rows($query)){

/*php function reference

fetch results from query and print them*/

while ($row = sqlite_fetch_array($query)){

     echo $row[0].’<br>’;

}

}

else echo “no value”;

/*class reference version

fetch results*/

while ($query->valid()) {

//fetch result into array

$row = $query->current();

//print out array

print_r($row);

//close while

}

Let´s now create a table and insert into database, and make a function out of it.

$db->query(“BEGIN;

            CREATE TABLE name (id INTEGER PRIMARY KEY, id2);

            INSERT INTO name (id2) VALUES (1)

            COMMIT;”);

The function would look like this:

//first function to check if sqlite

function sqlite_table_exists($db,$mytable) {
  /* counts the tables that match the name given */
  $result = sqlite_query($db,"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$mytable'");

  /* casts into integer */
  $count = intval(sqlite_fetch_single($result));

  /* returns true or false */
  return $count > 0;
}

function insert($table, $typeof, $field, $value){

$result = $db->query("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$table'");

  /* casts into integer */
  $count = intval(sqlite_fetch_single($result));

 

     // open database if exists

     if ($count){

if($db->query(“INSERT INTO name (“.$field.”) VALUES (“.$value.”)”)) return “inserted with success”;

else return “Error of data insertion on table”.$table;

}

else { if($db->query(“BEGIN;

            CREATE TABLE name (“.$field.” ”.$typeof.”);

            INSERT INTO name (“.$field.”) VALUES (“.$value.”)

            COMMIT;”))

          Return “inserted with success”;

      else return “Error of data insertion / table creation on table”.$table;

}

}

Now how we would go about executing our function:

//for the first insert do this

insert(“name”, “INTEGER PRIMARY KEY”, “id”, “7”);

//for the second and others do this

insert(“name”, “”, “id”, “7”);

Last but not least let´s close our database.

unset($db);

//or

sqlite_close($db);

We now have gone throw a lot of basic sqlite topics any questions or suggestions please email me ramonklown [NoSPaM] pop.com.br

Peace