Amyst Design Company, web and print design and development services

I have been wanting to try SQLite for some time and I recently got the chance to for a new project.

For the uninitiated:
SQLite is a flat-file database system with most of the functionality of larger database software like MySQL or MSSQL. SQLite is best suited for small footprint, light use databases, handling site newsreels or client contact data.

To start my new project I decided to clone a PHP database class that I have been using for some time for another platform. It does:

• Database connection/creation.
• Select, insert, and update queries.
• Array based insert/update queries for rapid programming.
• Error reporting on SQLite class functions.
• Data scrubbing on insert/update queries to prevent issues.

Source Code:

class sqlite_database
{
	protected $link;
	protected $error;
	private $db_path = '/path/to/your/database.db';

	function __construct()
	{
		$this->link = sqlite_open( $this->db_path, 0666, $this->error ) or die ('Error: ' . $this->error);
	}

	function close()
	{
		sqlite_close($this->link);
	}

	function last_id()
	{
		return sqlite_last_insert_rowid($this->link);
	}

	function escape_str( $string )
	{
		return sqlite_escape_string($string);
	}

	function query( $sql )
	{
		$r = sqlite_unbuffered_query($this->link, $sql, SQLITE_ASSOC, $this->error) or die('Error: ' . $this->error . '<br />SQL: ' . $sql);
		if ( !$r )
		{
			return false;
		}
		else
		{
			if ( sqlite_valid($r) )
			{
				$data->dataset = array();
				while ( $row = sqlite_fetch_object($r) )
				{
					$data->dataset[] = $row;
				}
				$data->rows = count($data->dataset);
				return $data;
			}
			else
			{
				return false;
			}
		}
	}

	function query_insert( $table, $data )
	{
		$q = 'INSERT INTO ' . $table;
		$v = '';
		$n = '';
		foreach ( $data as $key => $val )
		{
			$n .= $key . ', ';
			if ( strtolower($val) == 'null' )
			{
				$v .= 'NULL, ';
			}
			elseif (strtolower($val) == 'now()' )
			{
				$v .= 'NOW(), ';
			}
			else
			{
				$v .= "'" . $this->escape_str($val) . "', ";
			}
    	}
		$q .= ' (' . rtrim($n, ', ') . ') VALUES (' . rtrim($v, ', ') . ');';
		$r = sqlite_unbuffered_query($this->link, $q, SQLITE_ASSOC, $this->error) or die('Error: ' . $this->error . '<br />SQL: ' . $q);
		return ( $r ) ? true : false;
	}

	function query_update($table, $data, $where)
	{
		$q = "UPDATE {$table} SET ";
		foreach ( $data as $key => $val )
		{
			if ( strtolower($val) == 'null' )
			{
				$q .= "`$key` = NULL, ";
			}
			elseif ( strtolower($val) == 'now()' )
			{
				$q .= "`$key` = NOW(), ";
			}
			else
			{
				$q .= "`$key`='" . $this->escape_str($val) . "', ";
			}
		}
		foreach ( $where as $key => $val )
		{
			$where_sql .= '( ' . $key[0] . ' ' . $key[1] . ' \'' . $key[2] . '\' ) ';
		}
		$where_sql = substr($where_sql, 0, -1);
		$q = rtrim($q, ', ') . ' WHERE ' . $where_sql . ';';
		$r = sqlite_unbuffered_query($this->link, $q, SQLITE_ASSOC, $this->error) or die('Error: ' . $this->error . '<br />SQL: ' . $q);
		return ( $r ) ? true : false;
	}
}

Examples
Connect to a database:

// require the sqlite database class library from Amyst Design
require('path/to/sqlite.php');
// instantiate the database object
// the path and file name of the database are set within the class
// If the database file doesn't exist SQLite will create the file
$db = new sqlite_database();

Insert a row of data:

// build the array of data to be inserted
// using key/value pairs, set keys to database field names.
$data = array('field1' => $value1,
			  'field2' => $value2,
			  'fieldn' => $valuen);
// assuming the database object has been instantiated
// function will return true or false depending on outcome
$result = $db->query_insert( 'tablename', $data );

Update a row of data:

// build the array of data to be inserted
// using key/value pairs, set keys to database field names.
$data = array('field1' => $value1,
			  'field2' => $value2,
			  'fieldn' => $valuen);
// build the array of criteria
// field name, operator, criteria
// this does not support complex where clauses
$where = array(array('fieldname1', '=', 'value1'),
			   array('fieldname2', '>', 'value2'));
// assuming the database object has been instantiated
// function will return true or false depending on outcome
$result = $db->query_insert( 'tablename', $data, $id_to_update );