SQLite Class

This class makes connecting to, and interacting with an SQLite database super easy. The best, and almost only documentation for the SQLite database is at their official website: www.sqlite.org. The following is what I based my code on:

bool SQLite ( [ string $database= ':memory:' [, integer $version= 3 ]] )

This is the constructor for the class, and it will open an SQLite database in your BASE . 'sqlite\' folder. This method also turns 'PRAGMA foreign_keys = ON', and will alert you if foreign_keys are not supported. If no database is specified, then one will be opened in memory.

$database The name of the database beyond where you put it in the BASE . 'sqlite/' folder. If you leave it blank, then the database will be created in memory.
$version What version the sqlite database you are opening / creating is. Anything besides a 2 is a 3.
Returns True or false - whether or not the database was successfully opened.
Example 
$db = new SQLite (str_replace(BASE, '', BASE_URI) . 'users'); // ie. site.com/users

bool attach ( string $database, string $alias )

This will attach a database to the other(s) you currently have open.

$database What you named the database. Also, where you put it beyond the BASE . 'sqlite/' folder.
$alias How you plan on referring to it in your queries.
Returns Whether or not the database was successfully attached.
Example 
$db->attach('some/folder/database', 'reference');

bool create ( string $table, array $columns [, array $changes ] )

This will create an SQLite table in your database. You can call this method every time you open the database, and for every table created. If any changes were made, then it will automatically update (or alter) the table. Don't forget to index your referenced fields! The documentation says that it is beneficial in every instance.

$table The name of the database table you are creating.
$columns An array of columns in the form of array($name => $type).
$changes If you are altering a table, then specify the changed field(s) in the form of array('old field name' => 'new field name').
Returns False - if the table has already been created in it's requested state.
True - if the table was newly created (or altered).
Example 
$table = 'users';  
$columns = array();  
$columns['id'] = 'INTEGER PRIMARY KEY';  
$columns['email'] = 'TEXT NOT NULL UNIQUE COLLATE NOCASE';  
$columns['firstname'] = 'TEXT NOT NULL';  
$columns['lastname'] = 'TEXT NOT NULL';  
$columns['state'] = 'INTEGER NOT NULL DEFAULT 0';  
$columns['registered'] = 'TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP';  
$db->create($table, $columns); 
// $db->index($table, 1, 'state');

bool index ( string $table, string $suffix, mixed $columns )

Creates an index on an SQLite table's field(s).

$table The name of the table being referenced.
$suffix A unique suffix to distinguish this index from any others you may have on the same table.
$columns You can index one field (a string), or many (in an array).
Returns Whether or not the index was created successfully.
Example 
$db->index('users', 1, 'state');

mixed pragma ( string $name [, string $value [, bool $all= false ]] )

This method allows you to set or retrieve the value(s) of an SQLite pragma.

$name The name of the pragma.
$value The value you would like to set the pragma name to. If you want to retrieve the value(s), then leave this blank.
$all Set to true if you are expecting more that one row of values.
Returns True or false whether the query was successful or not when you specify a value, or an array of values when retrieving the value.
Example 
$db->pragma('encoding', 'UTF-8');  
$html .= 'Encoding: ' . $db->pragma('encoding');  
$html .= '<pre>' . print_r($db->pragma('table_info', '', true), true) . '</pre>';

bool query ( string $query [, array $values ] )

Performs a query on your SQLite database.

$query The SQLite query.
$values An array of values that match the question marks in your query. This is a better way to query your SQLite database, as it is unncessary to escape any data when doing this, and you are protected from SQL injection attacks.
Returns Whether or not the query was successful.
Example 
$db->query("SELECT * FROM users WHERE email = ?", array('email@address.com'));  
$email = "Hacker'); DROP TABLE users;--";  
$db->query("SELECT * FROM users WHERE email='{$email}'"); // This is bad!

bool exec ( string $query )

Performs an exec command on your SQLite database. In general, you should only use this method when you are making changes to the database that you are in control of. Leave the inserts, updates, selects, and deletes for either the query or statement methods.

$query The exec command you would like to perform.
Returns Whether or not the exec was successful.
Example 
$db->exec("PRAGMA foreign_keys = ON");

mixed statement ( string $query, array $values )

Use this method when you want to perform multiple insert, update, or delete statements where the query structure remains fixed, and only the values are different.

$query Similar to the query method, only this time your query must have question marks where / when appropriate.
$values An array of values that match up with the question marks in your query. If the array is multi-dimensional then all of your queries will be put into a transaction, and they will be completed much faster than if you were to do one query at a time.
Returns Either the total number of rows affected by your query and values, or false if the query was unsuccessful.
Example 
$query = "UPDATE lost SET candidate = ? WHERE id = ?";  
$values = array();  
$values[] = array('John Locke', 4);  
$values[] = array('Hugo Reyes', 8);  
$values[] = array('James Ford', 15);  
$values[] = array('Sayid Jarrah', 16);  
$values[] = array('Jack Shephard', 23);  
$values[] = array('Jin & Sun Kwon', 42);  
$db->statement($query, $values);

integer insert ( string $table, array $array )

This allows to you to easily insert one or many rows into an SQLite table.

$table The table you want to insert records into.
$array An array of $field => $value pairs to be inserted. If this array is multi-dimensional then multiple rows will be inserted.
Returns Either the total number of rows inserted if the array is multi-dimensional, or the last inserted id if only one record was inserted. If the query failed for some reason then it will return false.
Example 
$insert = array();  
$insert[] = array('group'=>'The Others');  
$insert[] = array('Oceanic Flight 815');  
$insert[] = array('DHARMA Initiative');  
$insert[] = array('Murderers');  
$db->insert('groups', $insert);

integer update ( string $table, array $array, string $column, mixed $id [, string $add ] )

This method allows you to update one, or many rows in your SQLite database.

$table The name of the table you are updating.
$array An array of $field => $value pairs to be updated. If this array is multi-dimensional then multiple rows will be updated.
$column The column you're using to define what exactly should be updated (usually the table's primary id column).
$id The id (or value) of the column you'd like to update. If array (above) is multi-dimensional, then this should also be a matching array of values / id's.
$add If you have any other conditional statements that you would like to make then you can say so here.
Returns The total number of rows updated, or false if the query failed.
Example 
$updates = array();  
$updates[] = array('on_island'=>'N', 'off_island'=>'N', 'deceased'=>'Y'); // John Locke  
$updates[] = array('Y', 'N', 'N'); // Hugo Reyes  
$updates[] = array('N', 'Y', 'N'); // James Ford  
$updates[] = array('N', 'N', 'Y'); // Sayid Jarrah  
$updates[] = array('N', 'N', 'Y'); // Jack Shephard  
$updates[] = array('N', 'N', 'Y'); // Jin & Sun Kwon  
$ids = array(4, 8, 15, 16, 23, 42);  
$db->update('lost', $updates, 'id', $ids, 'AND group_id=2');

mixed delete ( string $table, string $column, mixed $id [, string $add ] )

This method allows you to delete one, or many rows in your SQLite database.

$table The name of the table whose records you are deleting.
$column The column you're using to define what exactly should be deleted (usually the table's primary id column).
$id The id (or value) of the column you'd like to delete. If this is a multi-dimensional array of values then multiple records will be deleted.
$add Anything you might like to add to the end of the query.
Returns The number of rows deleted, or false if the query failed.
Example 
$db->delete('lost', 'deceased', 'Y');

array fetch ( [ string $return= 'num' [, bool $all= false ]] )

This allows you to fetch the results from your SELECT query statement.

$return The available values are:
'row' or 'num': Your array will be indexed by column number.
'assoc': Your array will be indexed by column name.
'both': Your array will have both numbered and named indexes.
$all If set to true (or anything besides false), then all of the results will be returned at once in one big multi-dimensional array.
Returns An array of results, or false if there are no results to fetch.
Example 
$db->query("SELECT candidate, id FROM lost ORDER BY id ASC");  
while (list($candidate, $id) = $db->fetch('row')) {  
  // do something  
}

mixed value ( string $query [, array $values ] )

This method is a quick way to get a single value you may be interested in.

$query The SQLite query.
$values An array of values that match the question marks in your query.
Returns A single value, or false if there isn't one.
Example 
$num = $db->value("SELECT COUNT(*) FROM lost");

mixed rowid ( string $table, string $unique, string $value )

This is a quick way to find out if a record exists or not.

$table The SQLite table's name.
$unique The column you are searching against.
$value The unique value of your column.
Returns Returns the primary rowid of the parameters in question, or false if there is no such record.
Example 
$id = $db->rowid('lost', 'id', 42);  
$html .= ($id) ? 'exists' : 'sorry, not here';

mixed fts ( string $action, string $table, array $fields [, string $text [, string $limit ]] )

If the built-in FTS Functionality is not available to you then this method is here to save the day, albeit not as well, fast, or efficiently.

$action The available values are:
'create': Create a new fulltext table of $fields from $table.
'upsert': Updates or inserts as the case may be the $fields into your $table.
'search': Searches the $fields in your $table for $text.
$table The table with the fields you want to search against.
$fields 'create': An array of fieldname(s).
'upsert': An array of $field => $value pairs.
'search': An array of fieldname(s).
$text 'search': The text you want to search for.
$limit 'search': Comma-separated start, duration values. eg. LIMIT 5, 10
Returns 'create': (bool) Whether or not the table was created.
'upsert': (bool) Whether or not the row was upserted.
'search': An array of search results (table rowid's) ordered by relevancy - up to 200.
Example 
$db->fts('create', 'lost', array('information'));
Click to Download the PHP Class

 Subscribe to our feed


comments powered by Disqus

PHP


Made Easy

Classes:

Page
WordPress
YUI Grid Builder
Blueprint CSS
SQLite
Database
Session
Form
Validation
Table
Lister
Pagination
Mailer
Image
RSS Feed
Atom Feed
Sitemap
Cache
Copyright © 2012 PHP-Ease.com - PHP Made Easy