Database Methods

The database class provides a wrapper of most common functions used for database access, and also others which simplify common queries. All queries performed with the database through the database abstraction layer can be analyzed for the execution time and further optimization.

`$db->query`

Executes an SQL query with your database. Note that the write_query method is now preferred.

`$db->write_query`

Similar to the query method, except write_query executes the query on the slave database in case you happen to have a multi-database server setup. write_query is now preferred over query.

`$db->simple_select`

Used to perform a simple select query (with no joins) on a table.

It receives four parameters:

table: The table name to be queried.
field(s): Comma delimited list of fields to be selected.
conditions: SQL formatted list of conditions to be matched.
options: List of options: group by, order by, order direction, limit, limit start. Returns a resource on success, or false on error.

Example

global $db;

$query = $db->simple_select("settings", "*", "name='boardclosed_reason'", array(
    "order_by" => 'name',
    "order_dir" => 'DESC',
    "limit" => 1
));

$settings = $db->fetch_array($query);

echo "<pre>";
print_r($settings);
echo "</pre>";

Output

Array
(
    [sid] => 6
    [name] => boardclosed_reason
    [title] => Board Closed Reason
    [description] => If your forum is closed, you can set a message here that your visitors will be able to see when they visit your forums.
    [optionscode] => textarea
    [value] => These forums are currently closed for maintenance. Please check back later.
    [disporder] => 2
    [gid] => 2
)

`$db->fetch_array`

Returns an array of values for the first row (can be iterated through with a while statement).

Example

$query = $db->query("SELECT * FROM table WHERE field='value'");

while($result = $db->fetch_array($query))
{
    $data1 = $result['FieldName1'];
    $data2 = $result['FieldName2'];
    // ...
}

`$db->fetch_field`

Returns the value of the field specified from the resource query. Receives three parameters:

resource: The resource query.
field: The field name to retrieve.
row: The row number to get the data from. Default is current row.

`$db->num_rows`

Returns the number of rows in the query. Receives one parameter:

query: The resource query.

It is preferable to use a count query then do $db->fetch_field to get this value.

`$db->insert_id`

Returns the insert id (The id of the primary key) of the insert query just run.

`$db->insert_query`

Runs an insert query on a table in a database. Receives two parameters:

table: The table name to perform the query on.
array: An array of fields and their values.

`$db->insert_query_multiple`

Runs an insert query on a table in the database. Multiple rows can be inserted with one query. Receives two parameters:

table: The table name to perform the query on.
array: A multidimensional array of fields and values. Each array key for the individual arrays must be the same, even if blank.

`$db->update_query`

Runs an update query on a table in a database. It receives five parameters:

table: The table name.
array: An array of fields and their values.
where: The SQL where clause.
limit: An optional limit clause.
no_quote: An option to quote incoming values of the array.

`$db->delete_query`

Used to perform a delete query on a table in a database. Receives three parameters:

table: The name of the table.
where: The where clause.
limit: The maximum amount of rows to be deleted. Default is unlimited.

`$db->escape_string`

Replaces addslashes, escapes data before being used in a query according to the SQL escape format for the database being used. Receives one parameter:

string: The string to be escaped.

`$db->free_result`

Frees the resources of an SQL query. Receives one parameter:

query: The query to destroy.

`$db->escape_string_like`

Escapse a string used within a like command. Receives one parameter:

string: The string to be escaped.

`$db->connect`

Connects a new database.

`$db->select_db`

Selects the database for the current SQL session. Receives one parameter:

database: The database name.

`$db->explain_query`

Helps to explain queries run from on database from the current session for debugging purposes. Receives two parameters:

string: The query SQL.
qtime: The time it took to perform the query.

`$db->data_seek`

Moves the internal pointer to the specified row. Receives two parameters:

query: The resource query.
row: The row to move the internal pointer to.

`$db->close`

Closes the connection to the currently open database.

`$db->error_number`

Returns the error number (if any) of the specified query resource.

`$db->error_string`

Returns the error string (if any) of the specified query resource.

`$db->error`

Output a database error. Receives one parameter:

string: The string to present as an error.

`$db->affected_rows`

Returns the amount of affected rows from a “write” query.

`$db->num_fields`

Returns the number of fields of the specified query resource. Receives one parameter:

query: The query data.

`$db->list_tables`

Returns the tables in the current open database. Receives two parameters:

database: The database name.
prefix: Prefix of the table (optional).

`$db->table_exists`

Returns true if the specified table exists. Receives one parameter:

table: The table name.

`$db->field_exists`

Returns true if the specified field exists. Receives two parameters:

field: The field name.
table: The table name.

`$db->shutdown_query`

Runs a query thats performed when PHP is done parsing the file. Receives two parameters:

query: The query data.
name: An optional name for the query.

`$db->get_version`

Returns the version number of the database server being used.

`$db->optimize_table`

Runs an optimize query on a table. Receives one parameter:

table: The table name.

`$db->analyze_table`

Runs an analyze query on a table. Receives one parameter:

table: The table name.

`$db->show_create_table`

Return the “create table” command for a specific table. Receives one parameter:

table: The table name.

`$db->show_fields_from`

Show the “show fields from” command for a specific table. Receives one parameter:

table: The table name.

`$db->is_fulltext`

Returns whether or not the table contains a fulltext index. Receives two parameters:

table: The table name.
index: Optionally specify the name of the index.

`$db->supports_fulltext`

Returns whether or not this database engine supports fulltext indexing. Receives one parameter:

table: The table name.

`$db->supports_fulltext_boolean`

Returns whether or not this database engine supports boolean fulltext matching. Receives one parameter:

table: The table name.

`$db->index_exists`

Checks to see if an index exists on a specified table. Receives two parameters:

table: The table name.
index: The name of the index.

`$db->create_fulltext_index`

Creates a fulltext index on the specified column in the specified table with optional index name. Receives three parameters:

table: The table name.
column: Name of the column to be indexed.
name: Optional index name.

`$db->drop_index`

Drop an index with the specified name from the specified table. Receives two parameters:

table: The table name.
index: The name of the index.

`$db->add_column`

Adds a new column to the specified table. Receives three parameters:

table: The table name.
column: The column name.
definition: The new column definition.

`$db->modify_column`

Changes the definition of the specified column in the specified table. Receives three parameters:

table: The table name.
column: The column name.
definition: The new column definition.

`$db->rename_column`

Renames the specified column. Receives four parameters:

table: The table name.
old_column: The old column name.
new_column: The new column name.
definition: The new column definition.

`$db->drop_column`

Drops the specified column from the specified table. Receives two parameters:

table: The table name.
column: The column name.

`$db->rename_table`

Renames the specified table. Receives three parameters:

old_table: The old table name.
new_table: The new table name.
table_prefix: Add table prefix to query. Defaults to true.

`$db->drop_table`

Drops the specified table. Receives three parameters:

table: The table name.
hard: Option to perform a hard drop (does not check to see if table exists). Defaults to false.
table_prefix: Add table prefix to query. Defaults to true.

`$db->replace_query`

Replaces the current contents of table with new values. Receives two parameters:

table: The table name.
replacements: An array of fields and their new values.

`$db->set_table_prefix`

Sets the table prefix used by the simple select, insert, update and delete functions. Receives one parameter:

prefix: The new table prefix.

`$db->fetch_size`

Fetched the total size of all mysql tables or a specific table. Receives one parameter:

table: The table name (optional).

`$db->fetch_db_charsets`

Fetch a list of database character sets this DBMS supports.

`$db->fetch_charset_collation`

Fetch a database collation for a particular database character set. Receives one parameter:

charset: The database character set.

`$db->build_create_table_collation`

Fetch a character set/collation string for use with CREATE TABLE statements. Uses current DB encoding.

`$db->get_execution_time`

Time how long it takes for a particular piece of code to run. Place calls above & below the block of code.

`$db->escape_binary`

Escapes a binary database fields (such as IP addresses). Receives one parameter:

string: The binary value.

`$db->unescape_binary`

Unescape binary data. Receives one parameter:

string: The binary value.

Edit this page on GitHub