File/program/lib/database/databaselib.php

Description

/program/lib/database/databaselib.php - database factory and database access routines

Functions
database_factory (line 64)

manufacture a database object

This loads (includes) a specific database access class based on the parameter $db_type. Currently 'mysql' is the only option, but support for PostgreSQL or other databases could be added in the future, see the code that is commented out

Because Website@School is not meant to be an 'enterprisy application', I decided against using an abstract class that would be extended by a specific driver class which would be instantiated via yet another factory type class; I'd like to keep this as simple as possible while retaining the necessary flexibility (and the option to add support for other databases). Toolkits like Adodb seem overkill for this application program.

This routine is called at a fairly early stage in the process. It does not rely on any regular libraries which may be include()'ed lateron. If no valid database type is specified, the function returns FALSE, otherwise a database object is returned.

Note that I did not use a singleton because I think that that pattern is simply a fancy word for a global variable. YMMV.

Note: This file can be safely included from the install.php script, allowing for database-manipulations via this abstraction layer rather than directly going to the database. There are no dependencies on other include()'s other than the actual database class files such as mysql.class.php. Also, this file does not rely on the global variable $CFG, which is also very convenient in the installer (where no CFG is available).

  • return: FALSE on error, or an instance of the $db_type database class
  • todo: perhaps add postgresql in a future version
bool|object database_factory (string $prefix, [string $db_type = 'mysql'], [bool $debug = FALSE])
  • string $prefix: the tablename prefix
  • string $db_type: (optional) which database to use, default 'mysql'
  • bool $debug: if TRUE extra information is displayed (handy for debugging the code)
db_bool_is (line 385)

check boolean field in a database-independent way

Various databases have different ways to indicate TRUE or FALSE in boolean type of fields. MySQL uses a tinyint(1) with values NULL, 0 and 1. PostgreSQL uses a lowercase 't' or 'f' etc. We already have two database-specific definitions for TRUE and FALSE: SQL_TRUE and SQL_FALSE. This routine 'converts' the database-specific boolean values back to a form that is useable in PHP. This routine is able to test for either TRUE or FALSE. Any other value is returned as NULL.

Typical use:

$user = db_select_single_record('users','is_active','user_id = 13');
if (db_bool_is(TRUE,$user['is_active'])) {
   ...
}

void db_bool_is (bool|mixed $value, mixed $variable_to_check)
  • bool|mixed $value: value to test for, could be TRUE, FALSE or anything else
  • mixed $variable_to_check: the value of the variable to check
db_delete (line 341)

delete zero or more rows in a table

  • return: FALSE on failure or the number of affected rows
  • uses: db_delete_sql()
bool|int db_delete (string $tablename, [mixed $where = ''])
  • string $tablename: the name of the table to delete from (without prefix)
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
db_delete_sql (line 353)

generate SQL to delete zero or more rows in a table

  • return: the constructed SQL statement
  • usedby: db_delete()
string db_delete_sql (string $tablename, [mixed $where = ''])
  • string $tablename: the name of the table to delete from (without prefix)
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
db_errormessage (line 463)

retrieve the latest database error from $DB

  • return: the error number and the error message of the latest error from $DB or empty string if no error
  • uses: $DB;
string db_errormessage ()
db_escape_and_quote (line 282)

conditionally quote and escape values for use with a database table

If $value is a string, it is escaped and single quotes are added at begin and end. If $value is a boolean, it is converted into the correct value for the database using SQL_FALSE/SQL_TRUE If $value is NULL, it is converted into the string 'NULL' (without quotes) Otherwise the value is not changed.

string|mixed db_escape_and_quote (mixed $value)
  • mixed $value: string, boolean, null or other value to escape and quote
db_insert_into (line 125)

execute the necessary SQL-code for an INSERT INTO statement

This excutes the SQL-statement created by db_insert_into_sql().

  • return: FALSE on error, # of affected rows otherwise
  • uses: $DB
bool db_insert_into (string $tablename, array $fields)
  • string $tablename: the name of the table to insert into (without prefix)
  • array $fields: an associative array with fieldnames and fieldvalues
db_insert_into_and_get_id (line 142)

execute the necessary SQL-code for an INSERT INTO statement and return the last_insert_id

This excutes the SQL-statement created by db_insert_into_sql(). If all goes well, the value of the last inserted id is returned.

  • return: FALSE on error, last_insert_id on success
  • uses: $DB
bool db_insert_into_and_get_id (string $tablename, array $fields, [string $key_fieldname = ''])
  • string $tablename: the name of the table to insert into (without prefix)
  • array $fields: an associative array with fieldnames and fieldvalues
  • string $key_fieldname: the name of the field that holds the primary key/serial
db_insert_into_sql (line 100)

generate the necessary SQL-code for an INSERT INTO statement

Construct an SQL-statement that inserts data into the speficied table. This routine takes care of properly escaping strings and also handles the addition of the table prefix

string db_insert_into_sql (string $tablename, array $fields)
  • string $tablename: the name of the table to insert into (without prefix)
  • array $fields: an associative array with fieldnames and fieldvalues
db_last_insert_id (line 414)

wrapper for DB->last_insert_id()

This calls $DB->last_insert_id() in a way that should be compatible with a future PostgreSQL database class. Note that MySQL doesn't care about this. You can get away with leaving table and field parameters empty (as is the default), but for compatibility and documentation purposes you should use the correct values.

Typical use: db_insert_into('users',$fields_array); $user_id = db_last_insert_id('users','user_id');

  • return: FALSE on error, otherwise an integer identifying the inserted record
  • uses: $DB
int|bool db_last_insert_id ([string $tablename = ''], [string $fieldname = ''])
  • string $tablename: name of the table (without prefix) in which a record was inserted
  • string $fieldname: name of the serial field to examine
db_select_all_records (line 256)

fetch all selected records from the database in one array

  • return: the selected records as an array of associative arrays or FALSE on error or not found
  • uses: db_select_sql()
bool|array db_select_all_records (string $tablename, mixed $fields, [mixed $where = ''], [mixed $order = ''], [string $keyfield = ''], [int $limit = ''], [int $offset = ''])
  • string $tablename: name of the table to select from (without prefix)
  • mixed $fields: fieldname or array with fieldnames to select
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
  • mixed $order: fieldname or array with fieldnames to determine sort order (without ORDER BY keyword)
  • string $keyfield: field to use as the key in the returned array or empty for 0-based numeric array key
  • int $limit: the maximum number of records to retrieve
  • int $offset: the number of records to skip initially
db_select_single_record (line 227)

fetch a single record from the database

  • return: the selected record as an associative array or FALSE on error or not found
  • uses: db_select_sql()
bool|array db_select_single_record (string $tablename, mixed $fields, [mixed $where = ''], [mixed $order = ''])
  • string $tablename: name of the table to select from (without prefix)
  • mixed $fields: fieldname or array with fieldnames to select
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
  • mixed $order: fieldname or array with fieldnames to determine sort order (without ORDER BY keyword)
db_select_sql (line 183)

generate the necessary SQL-code for a simple SELECT statement

Construct an SQL-statement of the form: SELECT field_list FROM table WHERE where_expression ORDER BY orderby_list

The parameter $fields can be either a simple string, indicating a single field or an array when more fields are to be selected

The optional parameter $where is either a simple string with an appropriate expression (without the keyword WHERE) or an array with fieldname/value-pairs. In the latter case the clauses fieldname=value are AND'ed together. If the specified values are string-like, they are properly quoted. Boolean values are treated properly too.

The optional parameter $order is either a simple string with an appropriate list or expression (without the keyword ORDER BY) or an array with fieldnames which will be used to create a comma-delimited string.

Examples:

  1. db_select_sql('areas','title') yields 'SELECT title FROM was_areas'
2. db_select_sql('areas',array('title','theme_id'),array('is_visible' => TRUE),'sort_order') yields 'SELECT title,theme_id FROM was_areas WHERE is_visible = 1 ORDER BY sort_order' (if SQL_TRUE is '1')

string db_select_sql (string $tablename, mixed $fields, [mixed $where = ''], [mixed $order = ''])
  • string $tablename: name of the table to select from (without prefix)
  • mixed $fields: fieldname or array with fieldnames to select
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
  • mixed $order: fieldname or array with fieldnames to determine sort order (without ORDER BY keyword)
db_update (line 305)

update one or more fields in a table

  • return: FALSE on failure or the number of affected rows
  • uses: db_update_sql()
bool|int db_update (string $tablename, array $fields, [mixed $where = ''])
  • string $tablename: the name of the table to update (without prefix)
  • array $fields: an associative array with fieldnames and fieldvalues
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
db_update_sql (line 319)

generate sql to update one or more fields in a table

  • return: the constructed SQL-statement
  • usedby: db_update()
  • uses: $DB
string db_update_sql (string $tablename, array $fields, [mixed $where = ''])
  • string $tablename: the name of the table to update (without prefix)
  • array $fields: an associative array with fieldnames and fieldvalues
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)
db_where_clause (line 436)

construct a where clause from string/array, including the word WHERE

this constructs a where clause including the word 'WHERE' based on the string or array $where.

The optional parameter $where is either a simple string with an appropriate expression (without the keyword WHERE) or an array with fieldname/value-pairs. In the latter case the clauses fieldname=value are AND'ed together. If the specified values are string-like, they are properly quoted. Boolean values are treated properly too. NULL-values yield a standard 'IS NULL' type of expression.

  • return: empty string or a WHERE-clause with leading space and the word 'WHERE'
string db_where_clause ([mixed $where = ''])
  • mixed $where: a single clause or an array with fieldnames => values ((without the WHERE keyword)

Documentation generated on Tue, 28 Jun 2016 19:09:03 +0200 by phpDocumentor 1.4.0