SQLiteDB Class

SQLiteDB

A PHP utility class for interacting with local SQLite databases for Alfred. The class provides functions for inserting data, querying the database, creating tables, truncating, dropping tables, and more, all in an ActiveRecord-esque way. Methods can be chained for easy readability.

Initialization

To initialize the class object, include it in your php code, then instantiate the new object.

1
2
require_once('sqlitedb.php');
$sql = new SQLiteDB();
require_once('sqlitedb.php');
$sql = new SQLiteDB();

Methods

Select

Set which table fields you wish to have returned in your result set. The list of fields can be a single string with a comma separated list of fields, or an array of field names. This function is optional if you want to retrieve all fields. If this function is not called, the default is to select *.

Example with string data

1
$sql->select( 'name, email' );
$sql->select( 'name, email' );

Example with array data

1
$sql->select( array( 'name', 'email' ) );
$sql->select( array( 'name', 'email' ) );

From

Set the table to perform your query on. This function isn’t always required because most terminating functions allow you to pass the table name there. ie. the insert function allows you to pass the data to insert, and the table to insert it into in the same function. The from function can accept data as a string, or an array of tables to be included in the query.

Example

1
$sql->from( 'users' );
$sql->from( 'users' );

Where

Set the where clause for your query. There are 3 parameters for this function.

  1. $field – The target field for the WHERE clause.
  2. $value – The value to compare for the target field of the WHERE clause.
  3. $comparison (optional) РThe type of comparison that will be performed in the where clause. Default: =

Example 1

1
2
$sql->where( 'name', 'David' )->get( 'users' );
// --> SELECT * FROM `users` WHERE `name` = "David"
$sql->where( 'name', 'David' )->get( 'users' );
// --> SELECT * FROM `users` WHERE `name` = "David"

Example 2

1
2
$sql->where( 'id', '30', '>' )->get( 'users' );
// --> SELECT * FROM `users` WHERE `id` > 30
$sql->where( 'id', '30', '>' )->get( 'users' );
// --> SELECT * FROM `users` WHERE `id` > 30

And_Where

Chain another WHERE clause to the end of the previous WHERE clause. The syntax of the and_where and or_where function both follow the same  pattern as the where function.

Example

1
2
$sql->where( 'name', 'David' )->and_where( 'id', '30', '>' )->get( 'users' );
// --> SELECT * FROM `users` WHERE `name` = "David" AND `id` > '30'
$sql->where( 'name', 'David' )->and_where( 'id', '30', '>' )->get( 'users' );
// --> SELECT * FROM `users` WHERE `name` = "David" AND `id` > '30'

More documentation coming …