\CI_DB_query_builder

__construct()

__construct(array  $params) : void

Class constructor

Parameters

initialize()

initialize() : void

Initialize Database Settings

Throws

\RuntimeException

In case of failure

db_connect()

db_connect() : mixed

DB connect

This is just a dummy method that all drivers will override.

Returns

db_pconnect()

db_pconnect() : mixed

Persistent database connection

Returns

reconnect()

reconnect() : void

Reconnect

Keep / reestablish the db connection if no queries have been sent for a length of time exceeding the server's idle timeout.

This is just a dummy method to allow drivers without such functionality to not declare it, while others will override it.

db_select()

db_select() : boolean

Select database

This is just a dummy method to allow drivers without such functionality to not declare it, while others will override it.

Returns

error()

error() : array

Last error

Returns

platform()

platform() : string

The name of the platform in use (mysql, mssql, etc.

..)

Returns

version()

version() : string

Database version number

Returns a string containing the version of the database being used. Most drivers will override this method.

Returns

query()

query(string  $sql, array  $binds = FALSE, boolean  $return_object = NULL) : mixed

Execute the query

Accepts an SQL string as input and returns a result object upon successful execution of a "read" type query. Returns boolean TRUE upon successful execution of a "write" type query. Returns boolean FALSE upon failure, and if the $db_debug variable is set to TRUE will raise an error.

Parameters

Returns

simple_query()

simple_query(  $sql) : mixed

Simple Query This is a simplified version of the query() function. Internally we only use it when running transaction commands since they do not require all the features of the main query() function.

Parameters

Returns

trans_off()

trans_off() : void

Disable Transactions This permits transactions to be disabled at run-time.

trans_strict()

trans_strict(boolean  $mode = TRUE) : void

Enable/disable Transaction Strict Mode

When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all subsequent groups will be rolled back.

If strict mode is disabled, each group is treated autonomously, meaning a failure of one group will not affect any others

Parameters

trans_start()

trans_start(boolean  $test_mode = FALSE) : boolean

Start Transaction

Parameters

Returns

trans_complete()

trans_complete() : boolean

Complete Transaction

Returns

trans_status()

trans_status() : boolean

Lets you retrieve the transaction flag to determine if it has failed

Returns

trans_begin()

trans_begin(boolean  $test_mode = FALSE) : boolean

Begin Transaction

Parameters

Returns

trans_commit()

trans_commit() : boolean

Commit Transaction

Returns

trans_rollback()

trans_rollback() : boolean

Rollback Transaction

Returns

compile_binds()

compile_binds(  $sql,   $binds) : string

Compile Bindings

Parameters

Returns

is_write_type()

is_write_type(  $sql) : boolean

Determines if a query is a "write" type.

Parameters

Returns

elapsed_time()

elapsed_time(  $decimals = 6) : string

Calculate the aggregate query elapsed time

Parameters

Returns

total_queries()

total_queries() : integer

Returns the total number of queries

Returns

last_query()

last_query() : string

Returns the last query that was executed

Returns

escape()

escape(  $str) : mixed

"Smart" Escape String

Escapes data based on type Sets boolean and null types

Parameters

Returns

escape_str()

escape_str(string|array<mixed,string>  $str, boolean  $like = FALSE) : string

Escape String

Parameters

Returns

escape_like_str()

escape_like_str(  $str) : mixed

Escape LIKE String

Calls the individual driver for platform specific escaping for LIKE conditions

Parameters

Returns

primary()

primary(string  $table) : string

Primary

Retrieves the primary key. It assumes that the row in the first position is the primary key

Parameters

Returns

count_all()

count_all(  $table = '') : integer

"Count All" query

Generates a platform-specific query string that counts all records in the specified database

Parameters

Returns

list_tables()

list_tables(string  $constrain_by_prefix = FALSE) : array

Returns an array of table names

Parameters

Returns

table_exists()

table_exists(string  $table_name) : boolean

Determine if a particular table exists

Parameters

Returns

list_fields()

list_fields(string  $table) : array

Fetch Field Names

Parameters

Returns

field_exists()

field_exists(  $field_name,   $table_name) : boolean

Determine if a particular field exists

Parameters

Returns

field_data()

field_data(string  $table) : array

Returns an object with field data

Parameters

Returns

escape_identifiers()

escape_identifiers(mixed  $item, boolean  $split = TRUE) : mixed

Escape the SQL Identifiers

This function escapes column and table names

Parameters

Returns

insert_string()

insert_string(  $table,   $data) : string

Generate an insert string

Parameters

Returns

update_string()

update_string(  $table,   $data,   $where) : string

Generate an update string

Parameters

Returns

call_function()

call_function(string  $function) : mixed

Enables a native PHP function to be run, using a platform agnostic wrapper.

Parameters

Returns

cache_set_path()

cache_set_path(  $path = '') : void

Set Cache Directory Path

Parameters

cache_on()

cache_on() : boolean

Enable Query Caching

Returns

cache_on value

cache_off()

cache_off() : boolean

Disable Query Caching

Returns

cache_on value

cache_delete()

cache_delete(string  $segment_one = '', string  $segment_two = '') : boolean

Delete the cache files associated with a particular URI

Parameters

Returns

cache_delete_all()

cache_delete_all() : boolean

Delete All cache files

Returns

close()

close() : void

Close DB Connection

display_error()

display_error(  $error = '',   $swap = '',   $native = FALSE) : string

Display an error message

Parameters

Returns

sends the application/views/errors/error_db.php template

protect_identifiers()

protect_identifiers(  $item,   $prefix_single = FALSE,   $protect_identifiers = NULL,   $field_exists = TRUE) : string

Protect Identifiers

This function is used extensively by the Query Builder class, and by a couple functions in this class. It takes a column or table name (optionally with an alias) and inserts the table prefix onto it. Some logic is necessary in order to deal with column names that include the path. Consider a query like this:

SELECT hostname.database.table.column AS c FROM hostname.database.table

Or a query with aliasing:

SELECT m.member_id, m.member_name FROM members AS m

Since the column name can include up to four segments (host, DB, table, column) or also have an alias prefix, we need to do a bit of work to figure this out and insert the table prefix (if it exists) in the proper position, and escape only the correct identifiers.

Parameters

Returns

select()

select(  $select = '*',   $escape = NULL) : \CI_DB_query_builder

Select

Generates the SELECT portion of the query

Parameters

Returns

select_max()

select_max(  $select = '',   $alias = '') : \CI_DB_query_builder

Select Max

Generates a SELECT MAX(field) portion of a query

Parameters

Returns

select_min()

select_min(  $select = '',   $alias = '') : \CI_DB_query_builder

Select Min

Generates a SELECT MIN(field) portion of a query

Parameters

Returns

select_avg()

select_avg(  $select = '',   $alias = '') : \CI_DB_query_builder

Select Average

Generates a SELECT AVG(field) portion of a query

Parameters

Returns

select_sum()

select_sum(  $select = '',   $alias = '') : \CI_DB_query_builder

Select Sum

Generates a SELECT SUM(field) portion of a query

Parameters

Returns

distinct()

distinct(boolean  $val = TRUE) : \CI_DB_query_builder

DISTINCT

Sets a flag which tells the query string compiler to add DISTINCT

Parameters

Returns

from()

from(mixed  $from) : \CI_DB_query_builder

From

Generates the FROM portion of the query

Parameters

Returns

join()

join(  $table,   $cond,   $type = '',   $escape = NULL) : \CI_DB_query_builder

JOIN

Generates the JOIN portion of the query

Parameters

Returns

where()

where(  $key,   $value = NULL,   $escape = NULL) : \CI_DB_query_builder

WHERE

Generates the WHERE portion of the query. Separates multiple calls with 'AND'.

Parameters

Returns

or_where()

or_where(  $key,   $value = NULL,   $escape = NULL) : \CI_DB_query_builder

OR WHERE

Generates the WHERE portion of the query. Separates multiple calls with 'OR'.

Parameters

Returns

where_in()

where_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

WHERE IN

Generates a WHERE field IN('item', 'item') SQL query, joined with 'AND' if appropriate.

Parameters

Returns

or_where_in()

or_where_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

OR WHERE IN

Generates a WHERE field IN('item', 'item') SQL query, joined with 'OR' if appropriate.

Parameters

Returns

where_not_in()

where_not_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

WHERE NOT IN

Generates a WHERE field NOT IN('item', 'item') SQL query, joined with 'AND' if appropriate.

Parameters

Returns

or_where_not_in()

or_where_not_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

OR WHERE NOT IN

Generates a WHERE field NOT IN('item', 'item') SQL query, joined with 'OR' if appropriate.

Parameters

Returns

having_in()

having_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

HAVING IN

Generates a HAVING field IN('item', 'item') SQL query, joined with 'AND' if appropriate.

Parameters

Returns

or_having_in()

or_having_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

OR HAVING IN

Generates a HAVING field IN('item', 'item') SQL query, joined with 'OR' if appropriate.

Parameters

Returns

having_not_in()

having_not_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

HAVING NOT IN

Generates a HAVING field NOT IN('item', 'item') SQL query, joined with 'AND' if appropriate.

Parameters

Returns

or_having_not_in()

or_having_not_in(string  $key = NULL, array  $values = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

OR HAVING NOT IN

Generates a HAVING field NOT IN('item', 'item') SQL query, joined with 'OR' if appropriate.

Parameters

Returns

like()

like(mixed  $field, string  $match = '', string  $side = 'both', boolean  $escape = NULL) : \CI_DB_query_builder

LIKE

Generates a %LIKE% portion of the query. Separates multiple calls with 'AND'.

Parameters

Returns

not_like()

not_like(mixed  $field, string  $match = '', string  $side = 'both', boolean  $escape = NULL) : \CI_DB_query_builder

NOT LIKE

Generates a NOT LIKE portion of the query. Separates multiple calls with 'AND'.

Parameters

Returns

or_like()

or_like(mixed  $field, string  $match = '', string  $side = 'both', boolean  $escape = NULL) : \CI_DB_query_builder

OR LIKE

Generates a %LIKE% portion of the query. Separates multiple calls with 'OR'.

Parameters

Returns

or_not_like()

or_not_like(mixed  $field, string  $match = '', string  $side = 'both', boolean  $escape = NULL) : \CI_DB_query_builder

OR NOT LIKE

Generates a NOT LIKE portion of the query. Separates multiple calls with 'OR'.

Parameters

Returns

group_start()

group_start(string  $not = '', string  $type = 'AND ') : \CI_DB_query_builder

Starts a query group.

Parameters

Returns

or_group_start()

or_group_start() : \CI_DB_query_builder

Starts a query group, but ORs the group

Returns

not_group_start()

not_group_start() : \CI_DB_query_builder

Starts a query group, but NOTs the group

Returns

or_not_group_start()

or_not_group_start() : \CI_DB_query_builder

Starts a query group, but OR NOTs the group

Returns

group_end()

group_end() : \CI_DB_query_builder

Ends a query group

Returns

group_by()

group_by(string  $by, boolean  $escape = NULL) : \CI_DB_query_builder

GROUP BY

Parameters

Returns

having()

having(string  $key, string  $value = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

HAVING

Separates multiple calls with 'AND'.

Parameters

Returns

or_having()

or_having(string  $key, string  $value = NULL, boolean  $escape = NULL) : \CI_DB_query_builder

OR HAVING

Separates multiple calls with 'OR'.

Parameters

Returns

order_by()

order_by(string  $orderby, string  $direction = '', boolean  $escape = NULL) : \CI_DB_query_builder

ORDER BY

Parameters

Returns

limit()

limit(integer  $value, integer  $offset) : \CI_DB_query_builder

LIMIT

Parameters

Returns

offset()

offset(integer  $offset) : \CI_DB_query_builder

Sets the OFFSET value

Parameters

Returns

set()

set(  $key,   $value = '',   $escape = NULL) : \CI_DB_query_builder

The "set" function.

Allows key/value pairs to be set for inserting or updating

Parameters

Returns

get_compiled_select()

get_compiled_select(  $table = '',   $reset = TRUE) : string

Get SELECT query string

Compiles a SELECT query string and returns the sql.

Parameters

Returns

get()

get(  $table = '',   $limit = NULL,   $offset = NULL) : \CI_DB_result

Get

Compiles the select statement based on the other functions called and runs the query

Parameters

Returns

count_all_results()

count_all_results(  $table = '',   $reset = TRUE) : integer

"Count All Results" query

Generates a platform-specific query string that counts all records returned by an Query Builder query.

Parameters

Returns

get_where()

get_where(string  $table = '', string  $where = NULL, integer  $limit = NULL, integer  $offset = NULL) : \CI_DB_result

get_where()

Allows the where clause, limit and offset to be added directly

Parameters

Returns

insert_batch()

insert_batch(string  $table, array  $set = NULL, boolean  $escape = NULL,   $batch_size = 100) : integer

Insert_Batch

Compiles batch insert strings and runs the queries

Parameters

Returns

Number of rows inserted or FALSE on failure

set_insert_batch()

set_insert_batch(  $key,   $value = '',   $escape = NULL) : \CI_DB_query_builder

The "set_insert_batch" function. Allows key/value pairs to be set for batch inserts

Parameters

Returns

get_compiled_insert()

get_compiled_insert(  $table = '',   $reset = TRUE) : string

Get INSERT query string

Compiles an insert query and returns the sql

Parameters

Returns

insert()

insert(  $table = '',   $set = NULL, boolean  $escape = NULL) : boolean

Insert

Compiles an insert string and runs the query

Parameters

Returns

TRUE on success, FALSE on failure

replace()

replace(  $table = '',   $set = NULL) : boolean

Replace

Compiles an replace into string and runs the query

Parameters

Returns

TRUE on success, FALSE on failure

get_compiled_update()

get_compiled_update(  $table = '',   $reset = TRUE) : string

Get UPDATE query string

Compiles an update query and returns the sql

Parameters

Returns

update()

update(string  $table = '', array  $set = NULL, mixed  $where = NULL, integer  $limit = NULL) : boolean

UPDATE

Compiles an update string and runs the query.

Parameters

Returns

TRUE on success, FALSE on failure

update_batch()

update_batch(  $table,   $set = NULL,   $index = NULL,   $batch_size = 100) : integer

Update_Batch

Compiles an update string and runs the query

Parameters

Returns

number of rows affected or FALSE on failure

set_update_batch()

set_update_batch(  $key,   $index = '',   $escape = NULL) : \CI_DB_query_builder

The "set_update_batch" function. Allows key/value pairs to be set for batch updating

Parameters

Returns

empty_table()

empty_table(  $table = '') : boolean

Empty Table

Compiles a delete string and runs "DELETE FROM table"

Parameters

Returns

TRUE on success, FALSE on failure

truncate()

truncate(  $table = '') : boolean

Truncate

Compiles a truncate string and runs the query If the database does not support the truncate() command This function maps to "DELETE FROM table"

Parameters

Returns

TRUE on success, FALSE on failure

get_compiled_delete()

get_compiled_delete(  $table = '',   $reset = TRUE) : string

Get DELETE query string

Compiles a delete query string and returns the sql

Parameters

Returns

delete()

delete(  $table = '',   $where = '',   $limit = NULL,   $reset_data = TRUE) : mixed

Delete

Compiles a delete string and runs the query

Parameters

Returns

dbprefix()

dbprefix(  $table = '') : string

DB Prefix

Prepends a database prefix if one exists in configuration

Parameters

Returns

set_dbprefix()

set_dbprefix(  $prefix = '') : string

Set DB Prefix

Set's the DB Prefix to something new without needing to reconnect

Parameters

Returns

start_cache()

start_cache() : \CI_DB_query_builder

Start Cache

Starts QB caching

Returns

stop_cache()

stop_cache() : \CI_DB_query_builder

Stop Cache

Stops QB caching

Returns

flush_cache()

flush_cache() : \CI_DB_query_builder

Flush Cache

Empties the QB cache

Returns

reset_query()

reset_query() : \CI_DB_query_builder

Reset Query Builder values.

Publicly-visible method to reset the QB values.

Returns

_version()

_version() : string

Version number query string

Returns

_escape_str()

_escape_str(  $str) : string

Platform-dependent string escape

Parameters

Returns

_insert()

_insert(  $table,   $keys,   $values) : string

Insert statement

Generates a platform-specific insert string from the supplied data

Parameters

Returns

_update()

_update(  $table,   $values) : string

Update statement

Generates a platform-specific update string from the supplied data

Parameters

Returns

_has_operator()

_has_operator(  $str) : boolean

Tests whether the string has an SQL operator

Parameters

Returns

_get_operator()

_get_operator(  $str) : string

Returns the SQL string operator

Parameters

Returns

_cache_init()

_cache_init() : boolean

Initialize the Cache Class

Returns

_close()

_close() : void

Close DB Connection

This method would be overridden by most of the drivers.

_reset_select()

_reset_select() : void

Resets the query builder values. Called by the get() function

_max_min_avg_sum()

_max_min_avg_sum(string  $select = '', string  $alias = '', string  $type = 'MAX') : \CI_DB_query_builder

SELECT [MAX|MIN|AVG|SUM]()

Parameters

Returns

_create_alias_from_table()

_create_alias_from_table(string  $item) : string

Determines the alias name based on the table

Parameters

Returns

_wh()

_wh(string  $qb_key, mixed  $key, mixed  $value = NULL, string  $type = 'AND ', boolean  $escape = NULL) : \CI_DB_query_builder

WHERE, HAVING

Parameters

Returns

_wh_in()

_wh_in(string  $qb_key, string  $key = NULL, array  $values = NULL, boolean  $not = FALSE, string  $type = 'AND ', boolean  $escape = NULL) : \CI_DB_query_builder

Internal WHERE/HAVING IN

Parameters

Returns

_like()

_like(mixed  $field, string  $match = '', string  $type = 'AND ', string  $side = 'both', string  $not = '', boolean  $escape = NULL) : \CI_DB_query_builder

Internal LIKE

Parameters

Returns

_group_get_type()

_group_get_type(string  $type) : string

Group_get_type

Parameters

Returns

_limit()

_limit(string  $sql) : string

LIMIT string

Generates a platform-specific LIMIT clause.

Parameters

Returns

_insert_batch()

_insert_batch(string  $table, array  $keys, array  $values) : string

Insert batch statement

Generates a platform-specific insert string from the supplied data.

Parameters

Returns

_validate_insert()

_validate_insert(  $table = '') : string

Validate Insert

This method is used by both insert() and get_compiled_insert() to validate that the there data is actually being set and that table has been chosen to be inserted into.

Parameters

Returns

_replace()

_replace(  $table,   $keys,   $values) : string

Replace statement

Generates a platform-specific replace string from the supplied data

Parameters

Returns

_from_tables()

_from_tables() : string

FROM tables

Groups tables in FROM clauses if needed, so there is no confusion about operator precedence.

Note: This is only used (and overridden) by MySQL and CUBRID.

Returns

_validate_update()

_validate_update(  $table) : boolean

Validate Update

This method is used by both update() and get_compiled_update() to validate that data is actually being set and that a table has been chosen to be update.

Parameters

Returns

_update_batch()

_update_batch(string  $table, array  $values, string  $index) : string

Update_Batch statement

Generates a platform-specific batch update string from the supplied data

Parameters

Returns

_truncate()

_truncate(  $table) : string

Truncate statement

Generates a platform-specific truncate string from the supplied data

If the database does not support the truncate() command, then this method maps to 'DELETE FROM table'

Parameters

Returns

_delete()

_delete(  $table) : string

Delete statement

Generates a platform-specific delete string from the supplied data

Parameters

Returns

_track_aliases()

_track_aliases(  $table) : string

Track Aliases

Used to track SQL statements written with aliased tables.

Parameters

Returns

_compile_select()

_compile_select(boolean  $select_override = FALSE) : string

Compile the SELECT statement

Generates a query string based on which functions were used. Should not be called directly.

Parameters

Returns

_compile_wh()

_compile_wh(string  $qb_key) : string

Compile WHERE, HAVING statements

Escapes identifiers in WHERE and HAVING statements at execution time.

Required so that aliases are tracked properly, regardless of whether where(), or_where(), having(), or_having are called prior to from(), join() and dbprefix is added only if needed.

Parameters

Returns

SQL statement

_compile_group_by()

_compile_group_by() : string

Compile GROUP BY

Escapes identifiers in GROUP BY statements at execution time.

Required so that aliases are tracked properly, regardless of whether group_by() is called prior to from(), join() and dbprefix is added only if needed.

Returns

SQL statement

_compile_order_by()

_compile_order_by() : string

Compile ORDER BY

Escapes identifiers in ORDER BY statements at execution time.

Required so that aliases are tracked properly, regardless of whether order_by() is called prior to from(), join() and dbprefix is added only if needed.

Returns

SQL statement

_object_to_array()

_object_to_array(  $object) : array

Object to Array

Takes an object as input and converts the class variables to array key/vals

Parameters

Returns

_object_to_array_batch()

_object_to_array_batch(  $object) : array

Object to Array

Takes an object as input and converts the class variables to array key/vals

Parameters

Returns

_merge_cache()

_merge_cache() : void

Merge Cache

When called, this function merges any cached QB arrays with locally called ones.

_is_literal()

_is_literal(string  $str) : boolean

Is literal

Determines if a string represents a literal value or a field name

Parameters

Returns

_reset_run()

_reset_run(  $qb_reset_items) : void

Resets the query builder values. Called by the get() function

Parameters

_reset_write()

_reset_write() : void

Resets the query builder "write" values.

Called by the insert() update() insert_batch() update_batch() and delete() functions