diff options
| author | Andrey Andreev <narf@devilix.net> | 2018-03-22 15:48:55 +0100 |
|---|---|---|
| committer | Andrey Andreev <narf@devilix.net> | 2018-03-22 15:48:55 +0100 |
| commit | 71e647782764184e3aab4faffe6d99176758979f (patch) | |
| tree | 74dbdd50a61cd5674915c144aca9ebbc00b235b8 /user_guide_src/source/database | |
| parent | b12fbad77bd69ca0c7624a9094c29b7691ea6107 (diff) | |
[ci skip] 3.1.8 release
Diffstat (limited to 'user_guide_src/source/database')
| -rw-r--r-- | user_guide_src/source/database/caching.rst | 162 | ||||
| -rw-r--r-- | user_guide_src/source/database/call_function.rst | 39 | ||||
| -rw-r--r-- | user_guide_src/source/database/configuration.rst | 207 | ||||
| -rw-r--r-- | user_guide_src/source/database/connecting.rst | 155 | ||||
| -rw-r--r-- | user_guide_src/source/database/db_driver_reference.rst | 439 | ||||
| -rw-r--r-- | user_guide_src/source/database/examples.rst | 119 | ||||
| -rw-r--r-- | user_guide_src/source/database/forge.rst | 412 | ||||
| -rw-r--r-- | user_guide_src/source/database/helpers.rst | 100 | ||||
| -rw-r--r-- | user_guide_src/source/database/index.rst | 25 | ||||
| -rw-r--r-- | user_guide_src/source/database/metadata.rst | 130 | ||||
| -rw-r--r-- | user_guide_src/source/database/queries.rst | 176 | ||||
| -rw-r--r-- | user_guide_src/source/database/query_builder.rst | 1570 | ||||
| -rw-r--r-- | user_guide_src/source/database/results.rst | 507 | ||||
| -rw-r--r-- | user_guide_src/source/database/transactions.rst | 127 | ||||
| -rw-r--r-- | user_guide_src/source/database/utilities.rst | 316 |
15 files changed, 0 insertions, 4484 deletions
diff --git a/user_guide_src/source/database/caching.rst b/user_guide_src/source/database/caching.rst deleted file mode 100644 index d73120a93..000000000 --- a/user_guide_src/source/database/caching.rst +++ /dev/null @@ -1,162 +0,0 @@ -###################### -Database Caching Class -###################### - -The Database Caching Class permits you to cache your queries as text -files for reduced database load. - -.. important:: This class is initialized automatically by the database - driver when caching is enabled. Do NOT load this class manually. - -.. important:: Not all query result functions are available when you - use caching. Please read this page carefully. - -Enabling Caching -================ - -Caching is enabled in three steps: - -- Create a writable directory on your server where the cache files can - be stored. -- Set the path to your cache folder in your - application/config/database.php file. -- Enable the caching feature, either globally by setting the preference - in your application/config/database.php file, or manually as - described below. - -Once enabled, caching will happen automatically whenever a page is -loaded that contains database queries. - -How Does Caching Work? -====================== - -CodeIgniter's query caching system happens dynamically when your pages -are viewed. When caching is enabled, the first time a web page is -loaded, the query result object will be serialized and stored in a text -file on your server. The next time the page is loaded the cache file -will be used instead of accessing your database. Your database usage can -effectively be reduced to zero for any pages that have been cached. - -Only read-type (SELECT) queries can be cached, since these are the only -type of queries that produce a result. Write-type (INSERT, UPDATE, etc.) -queries, since they don't generate a result, will not be cached by the -system. - -Cache files DO NOT expire. Any queries that have been cached will remain -cached until you delete them. The caching system permits you clear -caches associated with individual pages, or you can delete the entire -collection of cache files. Typically you'll want to use the housekeeping -functions described below to delete cache files after certain events -take place, like when you've added new information to your database. - -Will Caching Improve Your Site's Performance? -============================================= - -Getting a performance gain as a result of caching depends on many -factors. If you have a highly optimized database under very little load, -you probably won't see a performance boost. If your database is under -heavy use you probably will see an improved response, assuming your -file-system is not overly taxed. Remember that caching simply changes -how your information is retrieved, shifting it from being a database -operation to a file-system one. - -In some clustered server environments, for example, caching may be -detrimental since file-system operations are so intense. On single -servers in shared environments, caching will probably be beneficial. -Unfortunately there is no single answer to the question of whether you -should cache your database. It really depends on your situation. - -How are Cache Files Stored? -=========================== - -CodeIgniter places the result of EACH query into its own cache file. -Sets of cache files are further organized into sub-folders corresponding -to your controller functions. To be precise, the sub-folders are named -identically to the first two segments of your URI (the controller class -name and function name). - -For example, let's say you have a controller called blog with a function -called comments that contains three queries. The caching system will -create a cache folder called blog+comments, into which it will write -three cache files. - -If you use dynamic queries that change based on information in your URI -(when using pagination, for example), each instance of the query will -produce its own cache file. It's possible, therefore, to end up with -many times more cache files than you have queries. - -Managing your Cache Files -========================= - -Since cache files do not expire, you'll need to build deletion routines -into your application. For example, let's say you have a blog that -allows user commenting. Whenever a new comment is submitted you'll want -to delete the cache files associated with the controller function that -serves up your comments. You'll find two delete functions described -below that help you clear data. - -Not All Database Functions Work with Caching -============================================ - -Lastly, we need to point out that the result object that is cached is a -simplified version of the full result object. For that reason, some of -the query result functions are not available for use. - -The following functions ARE NOT available when using a cached result -object: - -- num_fields() -- field_names() -- field_data() -- free_result() - -Also, the two database resources (result_id and conn_id) are not -available when caching, since result resources only pertain to run-time -operations. - -****************** -Function Reference -****************** - -$this->db->cache_on() / $this->db->cache_off() -================================================ - -Manually enables/disables caching. This can be useful if you want to -keep certain queries from being cached. Example:: - - // Turn caching on - $this->db->cache_on(); - $query = $this->db->query("SELECT * FROM mytable"); - - // Turn caching off for this one query - $this->db->cache_off(); - $query = $this->db->query("SELECT * FROM members WHERE member_id = '$current_user'"); - - // Turn caching back on - $this->db->cache_on(); - $query = $this->db->query("SELECT * FROM another_table"); - -$this->db->cache_delete() -========================== - -Deletes the cache files associated with a particular page. This is -useful if you need to clear caching after you update your database. - -The caching system saves your cache files to folders that correspond to -the URI of the page you are viewing. For example, if you are viewing a -page at example.com/index.php/blog/comments, the caching system will put -all cache files associated with it in a folder called blog+comments. To -delete those particular cache files you will use:: - - $this->db->cache_delete('blog', 'comments'); - -If you do not use any parameters the current URI will be used when -determining what should be cleared. - -$this->db->cache_delete_all() -=============================== - -Clears all existing cache files. Example:: - - $this->db->cache_delete_all(); - diff --git a/user_guide_src/source/database/call_function.rst b/user_guide_src/source/database/call_function.rst deleted file mode 100644 index 83fc870d0..000000000 --- a/user_guide_src/source/database/call_function.rst +++ /dev/null @@ -1,39 +0,0 @@ -##################### -Custom Function Calls -##################### - -$this->db->call_function(); -============================ - -This function enables you to call PHP database functions that are not -natively included in CodeIgniter, in a platform independent manner. For -example, let's say you want to call the mysql_get_client_info() -function, which is **not** natively supported by CodeIgniter. You could -do so like this:: - - $this->db->call_function('get_client_info'); - -You must supply the name of the function, **without** the mysql\_ -prefix, in the first parameter. The prefix is added automatically based -on which database driver is currently being used. This permits you to -run the same function on different database platforms. Obviously not all -function calls are identical between platforms, so there are limits to -how useful this function can be in terms of portability. - -Any parameters needed by the function you are calling will be added to -the second parameter. - -:: - - $this->db->call_function('some_function', $param1, $param2, etc..); - -Often, you will either need to supply a database connection ID or a -database result ID. The connection ID can be accessed using:: - - $this->db->conn_id; - -The result ID can be accessed from within your result object, like this:: - - $query = $this->db->query("SOME QUERY"); - - $query->result_id;
\ No newline at end of file diff --git a/user_guide_src/source/database/configuration.rst b/user_guide_src/source/database/configuration.rst deleted file mode 100644 index a9bf7dcb6..000000000 --- a/user_guide_src/source/database/configuration.rst +++ /dev/null @@ -1,207 +0,0 @@ -###################### -Database Configuration -###################### - -CodeIgniter has a config file that lets you store your database -connection values (username, password, database name, etc.). The config -file is located at application/config/database.php. You can also set -database connection values for specific -:doc:`environments <../libraries/config>` by placing **database.php** -in the respective environment config folder. - -The config settings are stored in a multi-dimensional array with this -prototype:: - - $db['default'] = array( - 'dsn' => '', - 'hostname' => 'localhost', - 'username' => 'root', - 'password' => '', - 'database' => 'database_name', - 'dbdriver' => 'mysqli', - 'dbprefix' => '', - 'pconnect' => TRUE, - 'db_debug' => TRUE, - 'cache_on' => FALSE, - 'cachedir' => '', - 'char_set' => 'utf8', - 'dbcollat' => 'utf8_general_ci', - 'swap_pre' => '', - 'encrypt' => FALSE, - 'compress' => FALSE, - 'stricton' => FALSE, - 'failover' => array() - ); - -Some database drivers (such as PDO, PostgreSQL, Oracle, ODBC) might -require a full DSN string to be provided. If that is the case, you -should use the 'dsn' configuration setting, as if you're using the -driver's underlying native PHP extension, like this:: - - // PDO - $db['default']['dsn'] = 'pgsql:host=localhost;port=5432;dbname=database_name'; - - // Oracle - $db['default']['dsn'] = '//localhost/XE'; - -.. note:: If you do not specify a DSN string for a driver that requires it, CodeIgniter - will try to build it with the rest of the provided settings. - -.. note:: If you provide a DSN string and it is missing some valid settings (e.g. the - database character set), which are present in the rest of the configuration - fields, CodeIgniter will append them. - -You can also specify failovers for the situation when the main connection cannot connect for some reason. -These failovers can be specified by setting the failover for a connection like this:: - - $db['default']['failover'] = array( - array( - 'hostname' => 'localhost1', - 'username' => '', - 'password' => '', - 'database' => '', - 'dbdriver' => 'mysqli', - 'dbprefix' => '', - 'pconnect' => TRUE, - 'db_debug' => TRUE, - 'cache_on' => FALSE, - 'cachedir' => '', - 'char_set' => 'utf8', - 'dbcollat' => 'utf8_general_ci', - 'swap_pre' => '', - 'encrypt' => FALSE, - 'compress' => FALSE, - 'stricton' => FALSE - ), - array( - 'hostname' => 'localhost2', - 'username' => '', - 'password' => '', - 'database' => '', - 'dbdriver' => 'mysqli', - 'dbprefix' => '', - 'pconnect' => TRUE, - 'db_debug' => TRUE, - 'cache_on' => FALSE, - 'cachedir' => '', - 'char_set' => 'utf8', - 'dbcollat' => 'utf8_general_ci', - 'swap_pre' => '', - 'encrypt' => FALSE, - 'compress' => FALSE, - 'stricton' => FALSE - ) - ); - -You can specify as many failovers as you like. - -The reason we use a multi-dimensional array rather than a more simple -one is to permit you to optionally store multiple sets of connection -values. If, for example, you run multiple environments (development, -production, test, etc.) under a single installation, you can set up a -connection group for each, then switch between groups as needed. For -example, to set up a "test" environment you would do this:: - - $db['test'] = array( - 'dsn' => '', - 'hostname' => 'localhost', - 'username' => 'root', - 'password' => '', - 'database' => 'database_name', - 'dbdriver' => 'mysqli', - 'dbprefix' => '', - 'pconnect' => TRUE, - 'db_debug' => TRUE, - 'cache_on' => FALSE, - 'cachedir' => '', - 'char_set' => 'utf8', - 'dbcollat' => 'utf8_general_ci', - 'swap_pre' => '', - 'compress' => FALSE, - 'encrypt' => FALSE, - 'stricton' => FALSE, - 'failover' => array() - ); - -Then, to globally tell the system to use that group you would set this -variable located in the config file:: - - $active_group = 'test'; - -.. note:: The name 'test' is arbitrary. It can be anything you want. By - default we've used the word "default" for the primary connection, - but it too can be renamed to something more relevant to your project. - -Query Builder -------------- - -The :doc:`Query Builder Class <query_builder>` is globally enabled or -disabled by setting the $query_builder variable in the database -configuration file to TRUE/FALSE (boolean). The default setting is TRUE. -If you are not using the -query builder class, setting it to FALSE will utilize fewer resources -when the database classes are initialized. - -:: - - $query_builder = TRUE; - -.. note:: that some CodeIgniter classes such as Sessions require Query - Builder to be enabled to access certain functionality. - -Explanation of Values: ----------------------- - -====================== =========================================================================================================== - Name Config Description -====================== =========================================================================================================== -**dsn** The DSN connect string (an all-in-one configuration sequence). -**hostname** The hostname of your database server. Often this is 'localhost'. -**username** The username used to connect to the database. -**password** The password used to connect to the database. -**database** The name of the database you want to connect to. -**dbdriver** The database type. ie: mysqli, postgre, odbc, etc. Must be specified in lower case. -**dbprefix** An optional table prefix which will added to the table name when running - :doc:`Query Builder <query_builder>` queries. This permits multiple CodeIgniter - installations to share one database. -**pconnect** TRUE/FALSE (boolean) - Whether to use a persistent connection. -**db_debug** TRUE/FALSE (boolean) - Whether database errors should be displayed. -**cache_on** TRUE/FALSE (boolean) - Whether database query caching is enabled, - see also :doc:`Database Caching Class <caching>`. -**cachedir** The absolute server path to your database query cache directory. -**char_set** The character set used in communicating with the database. -**dbcollat** The character collation used in communicating with the database - - .. note:: Only used in the 'mysql' and 'mysqli' drivers. - -**swap_pre** A default table prefix that should be swapped with dbprefix. This is useful for distributed - applications where you might run manually written queries, and need the prefix to still be - customizable by the end user. -**schema** The database schema, defaults to 'public'. Used by PostgreSQL and ODBC drivers. -**encrypt** Whether or not to use an encrypted connection. - - - 'mysql' (deprecated), 'sqlsrv' and 'pdo/sqlsrv' drivers accept TRUE/FALSE - - 'mysqli' and 'pdo/mysql' drivers accept an array with the following options: - - - 'ssl_key' - Path to the private key file - - 'ssl_cert' - Path to the public key certificate file - - 'ssl_ca' - Path to the certificate authority file - - 'ssl_capath' - Path to a directory containing trusted CA certificates in PEM format - - 'ssl_cipher' - List of *allowed* ciphers to be used for the encryption, separated by colons (':') - - 'ssl_verify' - TRUE/FALSE; Whether to verify the server certificate or not ('mysqli' only) - -**compress** Whether or not to use client compression (MySQL only). -**stricton** TRUE/FALSE (boolean) - Whether to force "Strict Mode" connections, good for ensuring strict SQL - while developing an application. -**port** The database port number. To use this value you have to add a line to the database config array. - :: - - $db['default']['port'] = 5432; - -====================== =========================================================================================================== - -.. note:: Depending on what database platform you are using (MySQL, PostgreSQL, - etc.) not all values will be needed. For example, when using SQLite you - will not need to supply a username or password, and the database name - will be the path to your database file. The information above assumes - you are using MySQL. diff --git a/user_guide_src/source/database/connecting.rst b/user_guide_src/source/database/connecting.rst deleted file mode 100644 index 9b8117076..000000000 --- a/user_guide_src/source/database/connecting.rst +++ /dev/null @@ -1,155 +0,0 @@ -########################### -Connecting to your Database -########################### - -There are two ways to connect to a database: - -Automatically Connecting -======================== - -The "auto connect" feature will load and instantiate the database class -with every page load. To enable "auto connecting", add the word database -to the library array, as indicated in the following file: - -application/config/autoload.php - -Manually Connecting -=================== - -If only some of your pages require database connectivity you can -manually connect to your database by adding this line of code in any -function where it is needed, or in your class constructor to make the -database available globally in that class. - -:: - - $this->load->database(); - -If the above function does **not** contain any information in the first -parameter it will connect to the group specified in your database config -file. For most people, this is the preferred method of use. - -Available Parameters --------------------- - -#. The database connection values, passed either as an array or a DSN - string. -#. TRUE/FALSE (boolean). Whether to return the connection ID (see - Connecting to Multiple Databases below). -#. TRUE/FALSE (boolean). Whether to enable the Query Builder class. Set - to TRUE by default. - -Manually Connecting to a Database ---------------------------------- - -The first parameter of this function can **optionally** be used to -specify a particular database group from your config file, or you can -even submit connection values for a database that is not specified in -your config file. Examples: - -To choose a specific group from your config file you can do this:: - - $this->load->database('group_name'); - -Where group_name is the name of the connection group from your config -file. - -To connect manually to a desired database you can pass an array of -values:: - - $config['hostname'] = 'localhost'; - $config['username'] = 'myusername'; - $config['password'] = 'mypassword'; - $config['database'] = 'mydatabase'; - $config['dbdriver'] = 'mysqli'; - $config['dbprefix'] = ''; - $config['pconnect'] = FALSE; - $config['db_debug'] = TRUE; - $config['cache_on'] = FALSE; - $config['cachedir'] = ''; - $config['char_set'] = 'utf8'; - $config['dbcollat'] = 'utf8_general_ci'; - $this->load->database($config); - -For information on each of these values please see the :doc:`configuration -page <configuration>`. - -.. note:: For the PDO driver, you should use the $config['dsn'] setting - instead of 'hostname' and 'database': - - | - | $config['dsn'] = 'mysql:host=localhost;dbname=mydatabase'; - -Or you can submit your database values as a Data Source Name. DSNs must -have this prototype:: - - $dsn = 'dbdriver://username:password@hostname/database'; - $this->load->database($dsn); - -To override default config values when connecting with a DSN string, add -the config variables as a query string. - -:: - - $dsn = 'dbdriver://username:password@hostname/database?char_set=utf8&dbcollat=utf8_general_ci&cache_on=true&cachedir=/path/to/cache'; - $this->load->database($dsn); - -Connecting to Multiple Databases -================================ - -If you need to connect to more than one database simultaneously you can -do so as follows:: - - $DB1 = $this->load->database('group_one', TRUE); - $DB2 = $this->load->database('group_two', TRUE); - -Note: Change the words "group_one" and "group_two" to the specific -group names you are connecting to (or you can pass the connection values -as indicated above). - -By setting the second parameter to TRUE (boolean) the function will -return the database object. - -.. note:: When you connect this way, you will use your object name to issue - commands rather than the syntax used throughout this guide. In other - words, rather than issuing commands with: - - | - | $this->db->query(); - | $this->db->result(); - | etc... - | - | You will instead use: - | - | $DB1->query(); - | $DB1->result(); - | etc... - -.. note:: You don't need to create separate database configurations if you - only need to use a different database on the same connection. You - can switch to a different database when you need to, like this: - - | $this->db->db_select($database2_name); - -Reconnecting / Keeping the Connection Alive -=========================================== - -If the database server's idle timeout is exceeded while you're doing -some heavy PHP lifting (processing an image, for instance), you should -consider pinging the server by using the reconnect() method before -sending further queries, which can gracefully keep the connection alive -or re-establish it. - -:: - - $this->db->reconnect(); - -Manually closing the Connection -=============================== - -While CodeIgniter intelligently takes care of closing your database -connections, you can explicitly close the connection. - -:: - - $this->db->close();
\ No newline at end of file diff --git a/user_guide_src/source/database/db_driver_reference.rst b/user_guide_src/source/database/db_driver_reference.rst deleted file mode 100644 index 1f036cd77..000000000 --- a/user_guide_src/source/database/db_driver_reference.rst +++ /dev/null @@ -1,439 +0,0 @@ -################### -DB Driver Reference -################### - -This is the platform-independent base DB implementation class. -This class will not be called directly. Rather, the adapter -class for the specific database will extend and instantiate it. - -The how-to material for this has been split over several articles. -This article is intended to be a reference for them. - -.. important:: Not all methods are supported by all database drivers, - some of them may fail (and return FALSE) if the underlying - driver does not support them. - -.. php:class:: CI_DB_driver - - .. php:method:: initialize() - - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Initialize database settings, establish a connection to - the database. - - .. php:method:: db_connect($persistent = TRUE) - - :param bool $persistent: Whether to establish a persistent connection or a regular one - :returns: Database connection resource/object or FALSE on failure - :rtype: mixed - - Establish a connection with the database. - - .. note:: The returned value depends on the underlying - driver in use. For example, a ``mysqli`` instance - will be returned with the 'mysqli' driver. - - .. php:method:: db_pconnect() - - :returns: Database connection resource/object or FALSE on failure - :rtype: mixed - - Establish a persistent connection with the database. - - .. note:: This method is just an alias for ``db_connect(TRUE)``. - - .. php:method:: reconnect() - - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Keep / reestablish the database connection if no queries - have been sent for a length of time exceeding the - server's idle timeout. - - .. php:method:: db_select([$database = '']) - - :param string $database: Database name - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Select / switch the current database. - - .. php:method:: db_set_charset($charset) - - :param string $charset: Character set name - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Set client character set. - - .. php:method:: platform() - - :returns: Platform name - :rtype: string - - The name of the platform in use (mysql, mssql, etc...). - - .. php:method:: version() - - :returns: The version of the database being used - :rtype: string - - Database version number. - - .. php:method:: query($sql[, $binds = FALSE[, $return_object = NULL]]) - - :param string $sql: The SQL statement to execute - :param array $binds: An array of binding data - :param bool $return_object: Whether to return a result object or not - :returns: TRUE for successful "write-type" queries, CI_DB_result instance (method chaining) on "query" success, FALSE on failure - :rtype: mixed - - Execute an SQL 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" queries - - Boolean FALSE upon failure - - ``CI_DB_result`` object for "read type" queries - - .. note: If 'db_debug' setting is set to TRUE, an error - page will be displayed instead of returning FALSE - on failures and script execution will stop. - - .. php:method:: simple_query($sql) - - :param string $sql: The SQL statement to execute - :returns: Whatever the underlying driver's "query" function returns - :rtype: mixed - - A simplified version of the ``query()`` method, appropriate - for use when you don't need to get a result object or to - just send a query to the database and not care for the result. - - .. php:method:: affected_rows() - - :returns: Number of rows affected - :rtype: int - - Returns the number of rows *changed* by the last executed query. - - Useful for checking how much rows were created, updated or deleted - during the last executed query. - - .. php:method:: trans_strict([$mode = TRUE]) - - :param bool $mode: Strict mode flag - :rtype: void - - Enable/disable transaction "strict" mode. - - When strict mode is enabled, if you are running multiple - groups of transactions and 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. - - .. php:method:: trans_off() - - :rtype: void - - Disables transactions at run-time. - - .. php:method:: trans_start([$test_mode = FALSE]) - - :param bool $test_mode: Test mode flag - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Start a transaction. - - .. php:method:: trans_complete() - - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Complete Transaction. - - .. php:method:: trans_status() - - :returns: TRUE if the transaction succeeded, FALSE if it failed - :rtype: bool - - Lets you retrieve the transaction status flag to - determine if it has failed. - - .. php:method:: compile_binds($sql, $binds) - - :param string $sql: The SQL statement - :param array $binds: An array of binding data - :returns: The updated SQL statement - :rtype: string - - Compiles an SQL query with the bind values passed for it. - - .. php:method:: is_write_type($sql) - - :param string $sql: The SQL statement - :returns: TRUE if the SQL statement is of "write type", FALSE if not - :rtype: bool - - Determines if a query is of a "write" type (such as - INSERT, UPDATE, DELETE) or "read" type (i.e. SELECT). - - .. php:method:: elapsed_time([$decimals = 6]) - - :param int $decimals: The number of decimal places - :returns: The aggregate query elapsed time, in microseconds - :rtype: string - - Calculate the aggregate query elapsed time. - - .. php:method:: total_queries() - - :returns: The total number of queries executed - :rtype: int - - Returns the total number of queries that have been - executed so far. - - .. php:method:: last_query() - - :returns: The last query executed - :rtype: string - - Returns the last query that was executed. - - .. php:method:: escape($str) - - :param mixed $str: The value to escape, or an array of multiple ones - :returns: The escaped value(s) - :rtype: mixed - - Escapes input data based on type, including boolean and - NULLs. - - .. php:method:: escape_str($str[, $like = FALSE]) - - :param mixed $str: A string value or array of multiple ones - :param bool $like: Whether or not the string will be used in a LIKE condition - :returns: The escaped string(s) - :rtype: mixed - - Escapes string values. - - .. warning:: The returned strings do NOT include quotes - around them. - - .. php:method:: escape_like_str($str) - - :param mixed $str: A string value or array of multiple ones - :returns: The escaped string(s) - :rtype: mixed - - Escape LIKE strings. - - Similar to ``escape_str()``, but will also escape the ``%`` - and ``_`` wildcard characters, so that they don't cause - false-positives in LIKE conditions. - - .. important:: The ``escape_like_str()`` method uses '!' (exclamation mark) - to escape special characters for *LIKE* conditions. Because this - method escapes partial strings that you would wrap in quotes - yourself, it cannot automatically add the ``ESCAPE '!'`` - condition for you, and so you'll have to manually do that. - - - .. php:method:: primary($table) - - :param string $table: Table name - :returns: The primary key name, FALSE if none - :rtype: string - - Retrieves the primary key of a table. - - .. note:: If the database platform does not support primary - key detection, the first column name may be assumed - as the primary key. - - .. php:method:: count_all([$table = '']) - - :param string $table: Table name - :returns: Row count for the specified table - :rtype: int - - Returns the total number of rows in a table, or 0 if no - table was provided. - - .. php:method:: list_tables([$constrain_by_prefix = FALSE]) - - :param bool $constrain_by_prefix: TRUE to match table names by the configured dbprefix - :returns: Array of table names or FALSE on failure - :rtype: array - - Gets a list of the tables in the current database. - - .. php:method:: table_exists($table_name) - - :param string $table_name: The table name - :returns: TRUE if that table exists, FALSE if not - :rtype: bool - - Determine if a particular table exists. - - .. php:method:: list_fields($table) - - :param string $table: The table name - :returns: Array of field names or FALSE on failure - :rtype: array - - Gets a list of the field names in a table. - - .. php:method:: field_exists($field_name, $table_name) - - :param string $table_name: The table name - :param string $field_name: The field name - :returns: TRUE if that field exists in that table, FALSE if not - :rtype: bool - - Determine if a particular field exists. - - .. php:method:: field_data($table) - - :param string $table: The table name - :returns: Array of field data items or FALSE on failure - :rtype: array - - Gets a list containing field data about a table. - - .. php:method:: escape_identifiers($item) - - :param mixed $item: The item or array of items to escape - :returns: The input item(s), escaped - :rtype: mixed - - Escape SQL identifiers, such as column, table and names. - - .. php:method:: insert_string($table, $data) - - :param string $table: The target table - :param array $data: An associative array of key/value pairs - :returns: The SQL INSERT statement, as a string - :rtype: string - - Generate an INSERT statement string. - - .. php:method:: update_string($table, $data, $where) - - :param string $table: The target table - :param array $data: An associative array of key/value pairs - :param mixed $where: The WHERE statement conditions - :returns: The SQL UPDATE statement, as a string - :rtype: string - - Generate an UPDATE statement string. - - .. php:method:: call_function($function) - - :param string $function: Function name - :returns: The function result - :rtype: string - - Runs a native PHP function , using a platform agnostic - wrapper. - - .. php:method:: cache_set_path([$path = '']) - - :param string $path: Path to the cache directory - :rtype: void - - Sets the directory path to use for caching storage. - - .. php:method:: cache_on() - - :returns: TRUE if caching is on, FALSE if not - :rtype: bool - - Enable database results caching. - - .. php:method:: cache_off() - - :returns: TRUE if caching is on, FALSE if not - :rtype: bool - - Disable database results caching. - - .. php:method:: cache_delete([$segment_one = ''[, $segment_two = '']]) - - :param string $segment_one: First URI segment - :param string $segment_two: Second URI segment - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Delete the cache files associated with a particular URI. - - .. php:method:: cache_delete_all() - - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Delete all cache files. - - .. php:method:: close() - - :rtype: void - - Close the DB Connection. - - .. php:method:: display_error([$error = ''[, $swap = ''[, $native = FALSE]]]) - - :param string $error: The error message - :param string $swap: Any "swap" values - :param bool $native: Whether to localize the message - :rtype: void - - :returns: Displays the DB error screensends the application/views/errors/error_db.php template - :rtype: string - - Display an error message and stop script execution. - - The message is displayed using the - *application/views/errors/error_db.php* template. - - .. php:method:: protect_identifiers($item[, $prefix_single = FALSE[, $protect_identifiers = NULL[, $field_exists = TRUE]]]) - - :param string $item: The item to work with - :param bool $prefix_single: Whether to apply the dbprefix even if the input item is a single identifier - :param bool $protect_identifiers: Whether to quote identifiers - :param bool $field_exists: Whether the supplied item contains a field name or not - :returns: The modified item - :rtype: string - - Takes a column or table name (optionally with an alias) - and applies the configured *dbprefix* to it. - - Some logic is necessary in order to deal with - column names that include the path. - - Consider a query like this:: - - SELECT * FROM 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. - - This method is used extensively by the Query Builder class.
\ No newline at end of file diff --git a/user_guide_src/source/database/examples.rst b/user_guide_src/source/database/examples.rst deleted file mode 100644 index 5fd7fccfa..000000000 --- a/user_guide_src/source/database/examples.rst +++ /dev/null @@ -1,119 +0,0 @@ -################################## -Database Quick Start: Example Code -################################## - -The following page contains example code showing how the database class -is used. For complete details please read the individual pages -describing each function. - -Initializing the Database Class -=============================== - -The following code loads and initializes the database class based on -your :doc:`configuration <configuration>` settings:: - - $this->load->database(); - -Once loaded the class is ready to be used as described below. - -Note: If all your pages require database access you can connect -automatically. See the :doc:`connecting <connecting>` page for details. - -Standard Query With Multiple Results (Object Version) -===================================================== - -:: - - $query = $this->db->query('SELECT name, title, email FROM my_table'); - - foreach ($query->result() as $row) - { - echo $row->title; - echo $row->name; - echo $row->email; - } - - echo 'Total Results: ' . $query->num_rows(); - -The above result() function returns an array of **objects**. Example: -$row->title - -Standard Query With Multiple Results (Array Version) -==================================================== - -:: - - $query = $this->db->query('SELECT name, title, email FROM my_table'); - - foreach ($query->result_array() as $row) - { - echo $row['title']; - echo $row['name']; - echo $row['email']; - } - -The above result_array() function returns an array of standard array -indexes. Example: $row['title'] - -Standard Query With Single Result -================================= - -:: - - $query = $this->db->query('SELECT name FROM my_table LIMIT 1'); - $row = $query->row(); - echo $row->name; - -The above row() function returns an **object**. Example: $row->name - -Standard Query With Single Result (Array version) -================================================= - -:: - - $query = $this->db->query('SELECT name FROM my_table LIMIT 1'); - $row = $query->row_array(); - echo $row['name']; - -The above row_array() function returns an **array**. Example: -$row['name'] - -Standard Insert -=============== - -:: - - $sql = "INSERT INTO mytable (title, name) VALUES (".$this->db->escape($title).", ".$this->db->escape($name).")"; - $this->db->query($sql); - echo $this->db->affected_rows(); - -Query Builder Query -=================== - -The :doc:`Query Builder Pattern <query_builder>` gives you a simplified -means of retrieving data:: - - $query = $this->db->get('table_name'); - - foreach ($query->result() as $row) - { - echo $row->title; - } - -The above get() function retrieves all the results from the supplied -table. The :doc:`Query Builder <query_builder>` class contains a full -compliment of functions for working with data. - -Query Builder Insert -==================== - -:: - - $data = array( - 'title' => $title, - 'name' => $name, - 'date' => $date - ); - - $this->db->insert('mytable', $data); // Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}') - diff --git a/user_guide_src/source/database/forge.rst b/user_guide_src/source/database/forge.rst deleted file mode 100644 index 5af4f2248..000000000 --- a/user_guide_src/source/database/forge.rst +++ /dev/null @@ -1,412 +0,0 @@ -#################### -Database Forge Class -#################### - -The Database Forge Class contains methods that help you manage your -database. - -.. contents:: Table of Contents - :depth: 3 - -**************************** -Initializing the Forge Class -**************************** - -.. important:: In order to initialize the Forge class, your database - driver must already be running, since the forge class relies on it. - -Load the Forge Class as follows:: - - $this->load->dbforge() - -You can also pass another database object to the DB Forge loader, in case -the database you want to manage isn't the default one:: - - $this->myforge = $this->load->dbforge($this->other_db, TRUE); - -In the above example, we're passing a custom database object as the first -parameter and then tell it to return the dbforge object, instead of -assigning it directly to ``$this->dbforge``. - -.. note:: Both of the parameters can be used individually, just pass an empty - value as the first one if you wish to skip it. - -Once initialized you will access the methods using the ``$this->dbforge`` -object:: - - $this->dbforge->some_method(); - -******************************* -Creating and Dropping Databases -******************************* - -**$this->dbforge->create_database('db_name')** - -Permits you to create the database specified in the first parameter. -Returns TRUE/FALSE based on success or failure:: - - if ($this->dbforge->create_database('my_db')) - { - echo 'Database created!'; - } - -**$this->dbforge->drop_database('db_name')** - -Permits you to drop the database specified in the first parameter. -Returns TRUE/FALSE based on success or failure:: - - if ($this->dbforge->drop_database('my_db')) - { - echo 'Database deleted!'; - } - - -**************************** -Creating and Dropping Tables -**************************** - -There are several things you may wish to do when creating tables. Add -fields, add keys to the table, alter columns. CodeIgniter provides a -mechanism for this. - -Adding fields -============= - -Fields are created via an associative array. Within the array you must -include a 'type' key that relates to the datatype of the field. For -example, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR) -also require a 'constraint' key. - -:: - - $fields = array( - 'users' => array( - 'type' => 'VARCHAR', - 'constraint' => '100', - ), - ); - // will translate to "users VARCHAR(100)" when the field is added. - - -Additionally, the following key/values can be used: - -- unsigned/true : to generate "UNSIGNED" in the field definition. -- default/value : to generate a default value in the field definition. -- null/true : to generate "NULL" in the field definition. Without this, - the field will default to "NOT NULL". -- auto_increment/true : generates an auto_increment flag on the - field. Note that the field type must be a type that supports this, - such as integer. -- unique/true : to generate a unique key for the field definition. - -:: - - $fields = array( - 'blog_id' => array( - 'type' => 'INT', - 'constraint' => 5, - 'unsigned' => TRUE, - 'auto_increment' => TRUE - ), - 'blog_title' => array( - 'type' => 'VARCHAR', - 'constraint' => '100', - 'unique' => TRUE, - ), - 'blog_author' => array( - 'type' =>'VARCHAR', - 'constraint' => '100', - 'default' => 'King of Town', - ), - 'blog_description' => array( - 'type' => 'TEXT', - 'null' => TRUE, - ), - ); - - -After the fields have been defined, they can be added using -``$this->dbforge->add_field($fields);`` followed by a call to the -``create_table()`` method. - -**$this->dbforge->add_field()** - -The add fields method will accept the above array. - - -Passing strings as fields -------------------------- - -If you know exactly how you want a field to be created, you can pass the -string into the field definitions with add_field() - -:: - - $this->dbforge->add_field("label varchar(100) NOT NULL DEFAULT 'default label'"); - - -.. note:: Passing raw strings as fields cannot be followed by ``add_key()`` calls on those fields. - -.. note:: Multiple calls to add_field() are cumulative. - -Creating an id field --------------------- - -There is a special exception for creating id fields. A field with type -id will automatically be assigned as an INT(9) auto_incrementing -Primary Key. - -:: - - $this->dbforge->add_field('id'); - // gives id INT(9) NOT NULL AUTO_INCREMENT - - -Adding Keys -=========== - -Generally speaking, you'll want your table to have Keys. This is -accomplished with $this->dbforge->add_key('field'). An optional second -parameter set to TRUE will make it a primary key. Note that add_key() -must be followed by a call to create_table(). - -Multiple column non-primary keys must be sent as an array. Sample output -below is for MySQL. - -:: - - $this->dbforge->add_key('blog_id', TRUE); - // gives PRIMARY KEY `blog_id` (`blog_id`) - - $this->dbforge->add_key('blog_id', TRUE); - $this->dbforge->add_key('site_id', TRUE); - // gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`) - - $this->dbforge->add_key('blog_name'); - // gives KEY `blog_name` (`blog_name`) - - $this->dbforge->add_key(array('blog_name', 'blog_label')); - // gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`) - - -Creating a table -================ - -After fields and keys have been declared, you can create a new table -with - -:: - - $this->dbforge->create_table('table_name'); - // gives CREATE TABLE table_name - - -An optional second parameter set to TRUE adds an "IF NOT EXISTS" clause -into the definition - -:: - - $this->dbforge->create_table('table_name', TRUE); - // gives CREATE TABLE IF NOT EXISTS table_name - -You could also pass optional table attributes, such as MySQL's ``ENGINE``:: - - $attributes = array('ENGINE' => 'InnoDB'); - $this->dbforge->create_table('table_name', FALSE, $attributes); - // produces: CREATE TABLE `table_name` (...) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci - -.. note:: Unless you specify the ``CHARACTER SET`` and/or ``COLLATE`` attributes, - ``create_table()`` will always add them with your configured *char_set* - and *dbcollat* values, as long as they are not empty (MySQL only). - - -Dropping a table -================ - -Execute a DROP TABLE statement and optionally add an IF EXISTS clause. - -:: - - // Produces: DROP TABLE table_name - $this->dbforge->drop_table('table_name'); - - // Produces: DROP TABLE IF EXISTS table_name - $this->dbforge->drop_table('table_name',TRUE); - - -Renaming a table -================ - -Executes a TABLE rename - -:: - - $this->dbforge->rename_table('old_table_name', 'new_table_name'); - // gives ALTER TABLE old_table_name RENAME TO new_table_name - - -**************** -Modifying Tables -**************** - -Adding a Column to a Table -========================== - -**$this->dbforge->add_column()** - -The ``add_column()`` method is used to modify an existing table. It -accepts the same field array as above, and can be used for an unlimited -number of additional fields. - -:: - - $fields = array( - 'preferences' => array('type' => 'TEXT') - ); - $this->dbforge->add_column('table_name', $fields); - // Executes: ALTER TABLE table_name ADD preferences TEXT - -If you are using MySQL or CUBIRD, then you can take advantage of their -AFTER and FIRST clauses to position the new column. - -Examples:: - - // Will place the new column after the `another_field` column: - $fields = array( - 'preferences' => array('type' => 'TEXT', 'after' => 'another_field') - ); - - // Will place the new column at the start of the table definition: - $fields = array( - 'preferences' => array('type' => 'TEXT', 'first' => TRUE) - ); - - -Dropping a Column From a Table -============================== - -**$this->dbforge->drop_column()** - -Used to remove a column from a table. - -:: - - $this->dbforge->drop_column('table_name', 'column_to_drop'); - - - -Modifying a Column in a Table -============================= - -**$this->dbforge->modify_column()** - -The usage of this method is identical to ``add_column()``, except it -alters an existing column rather than adding a new one. In order to -change the name you can add a "name" key into the field defining array. - -:: - - $fields = array( - 'old_name' => array( - 'name' => 'new_name', - 'type' => 'TEXT', - ), - ); - $this->dbforge->modify_column('table_name', $fields); - // gives ALTER TABLE table_name CHANGE old_name new_name TEXT - - -*************** -Class Reference -*************** - -.. php:class:: CI_DB_forge - - .. php:method:: add_column($table[, $field = array()[, $_after = NULL]]) - - :param string $table: Table name to add the column to - :param array $field: Column definition(s) - :param string $_after: Column for AFTER clause (deprecated) - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Adds a column to a table. Usage: See `Adding a Column to a Table`_. - - .. php:method:: add_field($field) - - :param array $field: Field definition to add - :returns: CI_DB_forge instance (method chaining) - :rtype: CI_DB_forge - - Adds a field to the set that will be used to create a table. Usage: See `Adding fields`_. - - .. php:method:: add_key($key[, $primary = FALSE]) - - :param array $key: Name of a key field - :param bool $primary: Set to TRUE if it should be a primary key or a regular one - :returns: CI_DB_forge instance (method chaining) - :rtype: CI_DB_forge - - Adds a key to the set that will be used to create a table. Usage: See `Adding Keys`_. - - .. php:method:: create_database($db_name) - - :param string $db_name: Name of the database to create - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Creates a new database. Usage: See `Creating and Dropping Databases`_. - - .. php:method:: create_table($table[, $if_not_exists = FALSE[, array $attributes = array()]]) - - :param string $table: Name of the table to create - :param string $if_not_exists: Set to TRUE to add an 'IF NOT EXISTS' clause - :param string $attributes: An associative array of table attributes - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Creates a new table. Usage: See `Creating a table`_. - - .. php:method:: drop_column($table, $column_name) - - :param string $table: Table name - :param array $column_name: The column name to drop - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Drops a column from a table. Usage: See `Dropping a Column From a Table`_. - - .. php:method:: drop_database($db_name) - - :param string $db_name: Name of the database to drop - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Drops a database. Usage: See `Creating and Dropping Databases`_. - - .. php:method:: drop_table($table_name[, $if_exists = FALSE]) - - :param string $table: Name of the table to drop - :param string $if_exists: Set to TRUE to add an 'IF EXISTS' clause - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Drops a table. Usage: See `Dropping a table`_. - - .. php:method:: modify_column($table, $field) - - :param string $table: Table name - :param array $field: Column definition(s) - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Modifies a table column. Usage: See `Modifying a Column in a Table`_. - - .. php:method:: rename_table($table_name, $new_table_name) - - :param string $table: Current of the table - :param string $new_table_name: New name of the table - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Renames a table. Usage: See `Renaming a table`_. diff --git a/user_guide_src/source/database/helpers.rst b/user_guide_src/source/database/helpers.rst deleted file mode 100644 index 2d997a9e0..000000000 --- a/user_guide_src/source/database/helpers.rst +++ /dev/null @@ -1,100 +0,0 @@ -#################### -Query Helper Methods -#################### - -Information From Executing a Query -================================== - -**$this->db->insert_id()** - -The insert ID number when performing database inserts. - -.. note:: If using the PDO driver with PostgreSQL, or using the Interbase - driver, this function requires a $name parameter, which specifies the - appropriate sequence to check for the insert id. - -**$this->db->affected_rows()** - -Displays the number of affected rows, when doing "write" type queries -(insert, update, etc.). - -.. note:: In MySQL "DELETE FROM TABLE" returns 0 affected rows. The database - class has a small hack that allows it to return the correct number of - affected rows. By default this hack is enabled but it can be turned off - in the database driver file. - -**$this->db->last_query()** - -Returns the last query that was run (the query string, not the result). -Example:: - - $str = $this->db->last_query(); - - // Produces: SELECT * FROM sometable.... - - -.. note:: Disabling the **save_queries** setting in your database - configuration will render this function useless. - -Information About Your Database -=============================== - -**$this->db->count_all()** - -Permits you to determine the number of rows in a particular table. -Submit the table name in the first parameter. Example:: - - echo $this->db->count_all('my_table'); - - // Produces an integer, like 25 - -**$this->db->platform()** - -Outputs the database platform you are running (MySQL, MS SQL, Postgres, -etc...):: - - echo $this->db->platform(); - -**$this->db->version()** - -Outputs the database version you are running:: - - echo $this->db->version(); - -Making Your Queries Easier -========================== - -**$this->db->insert_string()** - -This function simplifies the process of writing database inserts. It -returns a correctly formatted SQL insert string. Example:: - - $data = array('name' => $name, 'email' => $email, 'url' => $url); - - $str = $this->db->insert_string('table_name', $data); - -The first parameter is the table name, the second is an associative -array with the data to be inserted. The above example produces:: - - INSERT INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com') - -.. note:: Values are automatically escaped, producing safer queries. - -**$this->db->update_string()** - -This function simplifies the process of writing database updates. It -returns a correctly formatted SQL update string. Example:: - - $data = array('name' => $name, 'email' => $email, 'url' => $url); - - $where = "author_id = 1 AND status = 'active'"; - - $str = $this->db->update_string('table_name', $data, $where); - -The first parameter is the table name, the second is an associative -array with the data to be updated, and the third parameter is the -"where" clause. The above example produces:: - - UPDATE table_name SET name = 'Rick', email = 'rick@example.com', url = 'example.com' WHERE author_id = 1 AND status = 'active' - -.. note:: Values are automatically escaped, producing safer queries.
\ No newline at end of file diff --git a/user_guide_src/source/database/index.rst b/user_guide_src/source/database/index.rst deleted file mode 100644 index 2fb50f9be..000000000 --- a/user_guide_src/source/database/index.rst +++ /dev/null @@ -1,25 +0,0 @@ -################## -Database Reference -################## - -CodeIgniter comes with a full-featured and very fast abstracted database -class that supports both traditional structures and Query Builder -patterns. The database functions offer clear, simple syntax. - -.. toctree:: - :titlesonly: - - Quick Start: Usage Examples <examples> - Database Configuration <configuration> - Connecting to a Database <connecting> - Running Queries <queries> - Generating Query Results <results> - Query Helper Functions <helpers> - Query Builder Class <query_builder> - Transactions <transactions> - Getting MetaData <metadata> - Custom Function Calls <call_function> - Query Caching <caching> - Database Manipulation with Database Forge <forge> - Database Utilities Class <utilities> - Database Driver Reference <db_driver_reference>
\ No newline at end of file diff --git a/user_guide_src/source/database/metadata.rst b/user_guide_src/source/database/metadata.rst deleted file mode 100644 index b8be809b6..000000000 --- a/user_guide_src/source/database/metadata.rst +++ /dev/null @@ -1,130 +0,0 @@ -################# -Database Metadata -################# - -************** -Table MetaData -************** - -These functions let you fetch table information. - -List the Tables in Your Database -================================ - -**$this->db->list_tables();** - -Returns an array containing the names of all the tables in the database -you are currently connected to. Example:: - - $tables = $this->db->list_tables(); - - foreach ($tables as $table) - { - echo $table; - } - - -Determine If a Table Exists -=========================== - -**$this->db->table_exists();** - -Sometimes it's helpful to know whether a particular table exists before -running an operation on it. Returns a boolean TRUE/FALSE. Usage example:: - - if ($this->db->table_exists('table_name')) - { - // some code... - } - -.. note:: Replace *table_name* with the name of the table you are looking for. - - -************** -Field MetaData -************** - -List the Fields in a Table -========================== - -**$this->db->list_fields()** - -Returns an array containing the field names. This query can be called -two ways: - -1. You can supply the table name and call it from the $this->db-> -object:: - - $fields = $this->db->list_fields('table_name'); - - foreach ($fields as $field) - { - echo $field; - } - -2. You can gather the field names associated with any query you run by -calling the function from your query result object:: - - $query = $this->db->query('SELECT * FROM some_table'); - - foreach ($query->list_fields() as $field) - { - echo $field; - } - - -Determine If a Field is Present in a Table -========================================== - -**$this->db->field_exists()** - -Sometimes it's helpful to know whether a particular field exists before -performing an action. Returns a boolean TRUE/FALSE. Usage example:: - - if ($this->db->field_exists('field_name', 'table_name')) - { - // some code... - } - -.. note:: Replace *field_name* with the name of the column you are looking - for, and replace *table_name* with the name of the table you are - looking for. - - -Retrieve Field Metadata -======================= - -**$this->db->field_data()** - -Returns an array of objects containing field information. - -Sometimes it's helpful to gather the field names or other metadata, like -the column type, max length, etc. - -.. note:: Not all databases provide meta-data. - -Usage example:: - - $fields = $this->db->field_data('table_name'); - - foreach ($fields as $field) - { - echo $field->name; - echo $field->type; - echo $field->max_length; - echo $field->primary_key; - } - -If you have run a query already you can use the result object instead of -supplying the table name:: - - $query = $this->db->query("YOUR QUERY"); - $fields = $query->field_data(); - -The following data is available from this function if supported by your -database: - -- name - column name -- max_length - maximum length of the column -- primary_key - 1 if the column is a primary key -- type - the type of the column diff --git a/user_guide_src/source/database/queries.rst b/user_guide_src/source/database/queries.rst deleted file mode 100644 index 710ababb0..000000000 --- a/user_guide_src/source/database/queries.rst +++ /dev/null @@ -1,176 +0,0 @@ -####### -Queries -####### - -************ -Query Basics -************ - -Regular Queries -=============== - -To submit a query, use the **query** function:: - - $this->db->query('YOUR QUERY HERE'); - -The query() function returns a database result **object** when "read" -type queries are run, which you can use to :doc:`show your -results <results>`. When "write" type queries are run it simply -returns TRUE or FALSE depending on success or failure. When retrieving -data you will typically assign the query to your own variable, like -this:: - - $query = $this->db->query('YOUR QUERY HERE'); - -Simplified Queries -================== - -The **simple_query** method is a simplified version of the -$this->db->query() method. It DOES -NOT return a database result set, nor does it set the query timer, or -compile bind data, or store your query for debugging. It simply lets you -submit a query. Most users will rarely use this function. - -It returns whatever the database drivers' "execute" function returns. -That typically is TRUE/FALSE on success or failure for write type queries -such as INSERT, DELETE or UPDATE statements (which is what it really -should be used for) and a resource/object on success for queries with -fetchable results. - -:: - - if ($this->db->simple_query('YOUR QUERY')) - { - echo "Success!"; - } - else - { - echo "Query failed!"; - } - -.. note:: PostgreSQL's ``pg_exec()`` function (for example) always - returns a resource on success, even for write type queries. - So take that in mind if you're looking for a boolean value. - -*************************************** -Working with Database prefixes manually -*************************************** - -If you have configured a database prefix and would like to prepend it to -a table name for use in a native SQL query for example, then you can use -the following:: - - $this->db->dbprefix('tablename'); // outputs prefix_tablename - - -If for any reason you would like to change the prefix programatically -without needing to create a new connection, you can use this method:: - - $this->db->set_dbprefix('newprefix_'); - $this->db->dbprefix('tablename'); // outputs newprefix_tablename - - -********************** -Protecting identifiers -********************** - -In many databases it is advisable to protect table and field names - for -example with backticks in MySQL. **Query Builder queries are -automatically protected**, however if you need to manually protect an -identifier you can use:: - - $this->db->protect_identifiers('table_name'); - -.. important:: Although the Query Builder will try its best to properly - quote any field and table names that you feed it, note that it - is NOT designed to work with arbitrary user input. DO NOT feed it - with unsanitized user data. - -This function will also add a table prefix to your table, assuming you -have a prefix specified in your database config file. To enable the -prefixing set TRUE (boolean) via the second parameter:: - - $this->db->protect_identifiers('table_name', TRUE); - - -**************** -Escaping Queries -**************** - -It's a very good security practice to escape your data before submitting -it into your database. CodeIgniter has three methods that help you do -this: - -#. **$this->db->escape()** This function determines the data type so - that it can escape only string data. It also automatically adds - single quotes around the data so you don't have to: - :: - - $sql = "INSERT INTO table (title) VALUES(".$this->db->escape($title).")"; - -#. **$this->db->escape_str()** This function escapes the data passed to - it, regardless of type. Most of the time you'll use the above - function rather than this one. Use the function like this: - :: - - $sql = "INSERT INTO table (title) VALUES('".$this->db->escape_str($title)."')"; - -#. **$this->db->escape_like_str()** This method should be used when - strings are to be used in LIKE conditions so that LIKE wildcards - ('%', '\_') in the string are also properly escaped. - -:: - - $search = '20% raise'; - $sql = "SELECT id FROM table WHERE column LIKE '%" . - $this->db->escape_like_str($search)."%' ESCAPE '!'"; - -.. important:: The ``escape_like_str()`` method uses '!' (exclamation mark) - to escape special characters for *LIKE* conditions. Because this - method escapes partial strings that you would wrap in quotes - yourself, it cannot automatically add the ``ESCAPE '!'`` - condition for you, and so you'll have to manually do that. - - -************** -Query Bindings -************** - -Bindings enable you to simplify your query syntax by letting the system -put the queries together for you. Consider the following example:: - - $sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"; - $this->db->query($sql, array(3, 'live', 'Rick')); - -The question marks in the query are automatically replaced with the -values in the array in the second parameter of the query function. - -Binding also work with arrays, which will be transformed to IN sets:: - - $sql = "SELECT * FROM some_table WHERE id IN ? AND status = ? AND author = ?"; - $this->db->query($sql, array(array(3, 6), 'live', 'Rick')); - -The resulting query will be:: - - SELECT * FROM some_table WHERE id IN (3,6) AND status = 'live' AND author = 'Rick' - -The secondary benefit of using binds is that the values are -automatically escaped, producing safer queries. You don't have to -remember to manually escape data; the engine does it automatically for -you. - -*************** -Handling Errors -*************** - -**$this->db->error();** - -If you need to get the last error that has occurred, the error() method -will return an array containing its code and message. Here's a quick -example:: - - if ( ! $this->db->simple_query('SELECT `example_field` FROM `example_table`')) - { - $error = $this->db->error(); // Has keys 'code' and 'message' - } - diff --git a/user_guide_src/source/database/query_builder.rst b/user_guide_src/source/database/query_builder.rst deleted file mode 100644 index 38bc7fcff..000000000 --- a/user_guide_src/source/database/query_builder.rst +++ /dev/null @@ -1,1570 +0,0 @@ -################### -Query Builder Class -################### - -CodeIgniter gives you access to a Query Builder class. This pattern -allows information to be retrieved, inserted, and updated in your -database with minimal scripting. In some cases only one or two lines -of code are necessary to perform a database action. -CodeIgniter does not require that each database table be its own class -file. It instead provides a more simplified interface. - -Beyond simplicity, a major benefit to using the Query Builder features -is that it allows you to create database independent applications, since -the query syntax is generated by each database adapter. It also allows -for safer queries, since the values are escaped automatically by the -system. - -.. note:: If you intend to write your own queries you can disable this - class in your database config file, allowing the core database library - and adapter to utilize fewer resources. - -.. contents:: - :local: - :depth: 1 - -************** -Selecting Data -************** - -The following functions allow you to build SQL **SELECT** statements. - -**$this->db->get()** - -Runs the selection query and returns the result. Can be used by itself -to retrieve all records from a table:: - - $query = $this->db->get('mytable'); // Produces: SELECT * FROM mytable - -The second and third parameters enable you to set a limit and offset -clause:: - - $query = $this->db->get('mytable', 10, 20); - - // Executes: SELECT * FROM mytable LIMIT 20, 10 - // (in MySQL. Other databases have slightly different syntax) - -You'll notice that the above function is assigned to a variable named -$query, which can be used to show the results:: - - $query = $this->db->get('mytable'); - - foreach ($query->result() as $row) - { - echo $row->title; - } - -Please visit the :doc:`result functions <results>` page for a full -discussion regarding result generation. - -**$this->db->get_compiled_select()** - -Compiles the selection query just like **$this->db->get()** but does not *run* -the query. This method simply returns the SQL query as a string. - -Example:: - - $sql = $this->db->get_compiled_select('mytable'); - echo $sql; - - // Prints string: SELECT * FROM mytable - -The second parameter enables you to set whether or not the query builder query -will be reset (by default it will be reset, just like when using `$this->db->get()`):: - - echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE); - - // Prints string: SELECT * FROM mytable LIMIT 20, 10 - // (in MySQL. Other databases have slightly different syntax) - - echo $this->db->select('title, content, date')->get_compiled_select(); - - // Prints string: SELECT title, content, date FROM mytable LIMIT 20, 10 - -The key thing to notice in the above example is that the second query did not -utilize **$this->db->from()** and did not pass a table name into the first -parameter. The reason for this outcome is because the query has not been -executed using **$this->db->get()** which resets values or reset directly -using **$this->db->reset_query()**. - -**$this->db->get_where()** - -Identical to the above function except that it permits you to add a -"where" clause in the second parameter, instead of using the db->where() -function:: - - $query = $this->db->get_where('mytable', array('id' => $id), $limit, $offset); - -Please read the about the where function below for more information. - -.. note:: get_where() was formerly known as getwhere(), which has been removed - -**$this->db->select()** - -Permits you to write the SELECT portion of your query:: - - $this->db->select('title, content, date'); - $query = $this->db->get('mytable'); - - // Executes: SELECT title, content, date FROM mytable - -.. note:: If you are selecting all (\*) from a table you do not need to - use this function. When omitted, CodeIgniter assumes that you wish - to select all fields and automatically adds 'SELECT \*'. - -``$this->db->select()`` accepts an optional second parameter. If you set it -to FALSE, CodeIgniter will not try to protect your field or table names. -This is useful if you need a compound select statement where automatic -escaping of fields may break them. - -:: - - $this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid', FALSE); - $query = $this->db->get('mytable'); - -**$this->db->select_max()** - -Writes a ``SELECT MAX(field)`` portion for your query. You can optionally -include a second parameter to rename the resulting field. - -:: - - $this->db->select_max('age'); - $query = $this->db->get('members'); // Produces: SELECT MAX(age) as age FROM members - - $this->db->select_max('age', 'member_age'); - $query = $this->db->get('members'); // Produces: SELECT MAX(age) as member_age FROM members - - -**$this->db->select_min()** - -Writes a "SELECT MIN(field)" portion for your query. As with -select_max(), You can optionally include a second parameter to rename -the resulting field. - -:: - - $this->db->select_min('age'); - $query = $this->db->get('members'); // Produces: SELECT MIN(age) as age FROM members - - -**$this->db->select_avg()** - -Writes a "SELECT AVG(field)" portion for your query. As with -select_max(), You can optionally include a second parameter to rename -the resulting field. - -:: - - $this->db->select_avg('age'); - $query = $this->db->get('members'); // Produces: SELECT AVG(age) as age FROM members - - -**$this->db->select_sum()** - -Writes a "SELECT SUM(field)" portion for your query. As with -select_max(), You can optionally include a second parameter to rename -the resulting field. - -:: - - $this->db->select_sum('age'); - $query = $this->db->get('members'); // Produces: SELECT SUM(age) as age FROM members - -**$this->db->from()** - -Permits you to write the FROM portion of your query:: - - $this->db->select('title, content, date'); - $this->db->from('mytable'); - $query = $this->db->get(); // Produces: SELECT title, content, date FROM mytable - -.. note:: As shown earlier, the FROM portion of your query can be specified - in the $this->db->get() function, so use whichever method you prefer. - -**$this->db->join()** - -Permits you to write the JOIN portion of your query:: - - $this->db->select('*'); - $this->db->from('blogs'); - $this->db->join('comments', 'comments.id = blogs.id'); - $query = $this->db->get(); - - // Produces: - // SELECT * FROM blogs JOIN comments ON comments.id = blogs.id - -Multiple function calls can be made if you need several joins in one -query. - -If you need a specific type of JOIN you can specify it via the third -parameter of the function. Options are: left, right, outer, inner, left -outer, and right outer. - -:: - - $this->db->join('comments', 'comments.id = blogs.id', 'left'); - // Produces: LEFT JOIN comments ON comments.id = blogs.id - -************************* -Looking for Specific Data -************************* - -**$this->db->where()** - -This function enables you to set **WHERE** clauses using one of four -methods: - -.. note:: All values passed to this function are escaped automatically, - producing safer queries. - -#. **Simple key/value method:** - - :: - - $this->db->where('name', $name); // Produces: WHERE name = 'Joe' - - Notice that the equal sign is added for you. - - If you use multiple function calls they will be chained together with - AND between them: - - :: - - $this->db->where('name', $name); - $this->db->where('title', $title); - $this->db->where('status', $status); - // WHERE name = 'Joe' AND title = 'boss' AND status = 'active' - -#. **Custom key/value method:** - - You can include an operator in the first parameter in order to - control the comparison: - - :: - - $this->db->where('name !=', $name); - $this->db->where('id <', $id); // Produces: WHERE name != 'Joe' AND id < 45 - -#. **Associative array method:** - - :: - - $array = array('name' => $name, 'title' => $title, 'status' => $status); - $this->db->where($array); - // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active' - - You can include your own operators using this method as well: - - :: - - $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date); - $this->db->where($array); - -#. **Custom string:** - You can write your own clauses manually:: - - $where = "name='Joe' AND status='boss' OR status='active'"; - $this->db->where($where); - - -``$this->db->where()`` accepts an optional third parameter. If you set it to -FALSE, CodeIgniter will not try to protect your field or table names. - -:: - - $this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE); - -**$this->db->or_where()** - -This function is identical to the one above, except that multiple -instances are joined by OR:: - - $this->db->where('name !=', $name); - $this->db->or_where('id >', $id); // Produces: WHERE name != 'Joe' OR id > 50 - -.. note:: or_where() was formerly known as orwhere(), which has been - removed. - -**$this->db->where_in()** - -Generates a WHERE field IN ('item', 'item') SQL query joined with AND if -appropriate - -:: - - $names = array('Frank', 'Todd', 'James'); - $this->db->where_in('username', $names); - // Produces: WHERE username IN ('Frank', 'Todd', 'James') - - -**$this->db->or_where_in()** - -Generates a WHERE field IN ('item', 'item') SQL query joined with OR if -appropriate - -:: - - $names = array('Frank', 'Todd', 'James'); - $this->db->or_where_in('username', $names); - // Produces: OR username IN ('Frank', 'Todd', 'James') - -**$this->db->where_not_in()** - -Generates a WHERE field NOT IN ('item', 'item') SQL query joined with -AND if appropriate - -:: - - $names = array('Frank', 'Todd', 'James'); - $this->db->where_not_in('username', $names); - // Produces: WHERE username NOT IN ('Frank', 'Todd', 'James') - - -**$this->db->or_where_not_in()** - -Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR -if appropriate - -:: - - $names = array('Frank', 'Todd', 'James'); - $this->db->or_where_not_in('username', $names); - // Produces: OR username NOT IN ('Frank', 'Todd', 'James') - -************************ -Looking for Similar Data -************************ - -**$this->db->like()** - -This method enables you to generate **LIKE** clauses, useful for doing -searches. - -.. note:: All values passed to this method are escaped automatically. - -#. **Simple key/value method:** - - :: - - $this->db->like('title', 'match'); - // Produces: WHERE `title` LIKE '%match%' ESCAPE '!' - - If you use multiple method calls they will be chained together with - AND between them:: - - $this->db->like('title', 'match'); - $this->db->like('body', 'match'); - // WHERE `title` LIKE '%match%' ESCAPE '!' AND `body` LIKE '%match% ESCAPE '!' - - If you want to control where the wildcard (%) is placed, you can use - an optional third argument. Your options are 'before', 'after' and - 'both' (which is the default). - - :: - - $this->db->like('title', 'match', 'before'); // Produces: WHERE `title` LIKE '%match' ESCAPE '!' - $this->db->like('title', 'match', 'after'); // Produces: WHERE `title` LIKE 'match%' ESCAPE '!' - $this->db->like('title', 'match', 'both'); // Produces: WHERE `title` LIKE '%match%' ESCAPE '!' - -#. **Associative array method:** - - :: - - $array = array('title' => $match, 'page1' => $match, 'page2' => $match); - $this->db->like($array); - // WHERE `title` LIKE '%match%' ESCAPE '!' AND `page1` LIKE '%match%' ESCAPE '!' AND `page2` LIKE '%match%' ESCAPE '!' - - -**$this->db->or_like()** - -This method is identical to the one above, except that multiple -instances are joined by OR:: - - $this->db->like('title', 'match'); $this->db->or_like('body', $match); - // WHERE `title` LIKE '%match%' ESCAPE '!' OR `body` LIKE '%match%' ESCAPE '!' - -.. note:: ``or_like()`` was formerly known as ``orlike()``, which has been removed. - -**$this->db->not_like()** - -This method is identical to ``like()``, except that it generates -NOT LIKE statements:: - - $this->db->not_like('title', 'match'); // WHERE `title` NOT LIKE '%match% ESCAPE '!' - -**$this->db->or_not_like()** - -This method is identical to ``not_like()``, except that multiple -instances are joined by OR:: - - $this->db->like('title', 'match'); - $this->db->or_not_like('body', 'match'); - // WHERE `title` LIKE '%match% OR `body` NOT LIKE '%match%' ESCAPE '!' - -**$this->db->group_by()** - -Permits you to write the GROUP BY portion of your query:: - - $this->db->group_by("title"); // Produces: GROUP BY title - -You can also pass an array of multiple values as well:: - - $this->db->group_by(array("title", "date")); // Produces: GROUP BY title, date - -.. note:: group_by() was formerly known as groupby(), which has been - removed. - -**$this->db->distinct()** - -Adds the "DISTINCT" keyword to a query - -:: - - $this->db->distinct(); - $this->db->get('table'); // Produces: SELECT DISTINCT * FROM table - -**$this->db->having()** - -Permits you to write the HAVING portion of your query. There are 2 -possible syntaxes, 1 argument or 2:: - - $this->db->having('user_id = 45'); // Produces: HAVING user_id = 45 - $this->db->having('user_id', 45); // Produces: HAVING user_id = 45 - -You can also pass an array of multiple values as well:: - - $this->db->having(array('title =' => 'My Title', 'id <' => $id)); - // Produces: HAVING title = 'My Title', id < 45 - - -If you are using a database that CodeIgniter escapes queries for, you -can prevent escaping content by passing an optional third argument, and -setting it to FALSE. - -:: - - $this->db->having('user_id', 45); // Produces: HAVING `user_id` = 45 in some databases such as MySQL - $this->db->having('user_id', 45, FALSE); // Produces: HAVING user_id = 45 - - -**$this->db->or_having()** - -Identical to having(), only separates multiple clauses with "OR". - -**************** -Ordering results -**************** - -**$this->db->order_by()** - -Lets you set an ORDER BY clause. - -The first parameter contains the name of the column you would like to order by. - -The second parameter lets you set the direction of the result. -Options are **ASC**, **DESC** AND **RANDOM**. - -:: - - $this->db->order_by('title', 'DESC'); - // Produces: ORDER BY `title` DESC - -You can also pass your own string in the first parameter:: - - $this->db->order_by('title DESC, name ASC'); - // Produces: ORDER BY `title` DESC, `name` ASC - -Or multiple function calls can be made if you need multiple fields. - -:: - - $this->db->order_by('title', 'DESC'); - $this->db->order_by('name', 'ASC'); - // Produces: ORDER BY `title` DESC, `name` ASC - -If you choose the **RANDOM** direction option, then the first parameters will -be ignored, unless you specify a numeric seed value. - -:: - - $this->db->order_by('title', 'RANDOM'); - // Produces: ORDER BY RAND() - - $this->db->order_by(42, 'RANDOM'); - // Produces: ORDER BY RAND(42) - -.. note:: order_by() was formerly known as orderby(), which has been - removed. - -.. note:: Random ordering is not currently supported in Oracle and - will default to ASC instead. - -**************************** -Limiting or Counting Results -**************************** - -**$this->db->limit()** - -Lets you limit the number of rows you would like returned by the query:: - - $this->db->limit(10); // Produces: LIMIT 10 - -The second parameter lets you set a result offset. - -:: - - $this->db->limit(10, 20); // Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) - -**$this->db->count_all_results()** - -Permits you to determine the number of rows in a particular Active -Record query. Queries will accept Query Builder restrictors such as -``where()``, ``or_where()``, ``like()``, ``or_like()``, etc. Example:: - - echo $this->db->count_all_results('my_table'); // Produces an integer, like 25 - $this->db->like('title', 'match'); - $this->db->from('my_table'); - echo $this->db->count_all_results(); // Produces an integer, like 17 - -However, this method also resets any field values that you may have passed -to ``select()``. If you need to keep them, you can pass ``FALSE`` as the -second parameter:: - - echo $this->db->count_all_results('my_table', FALSE); - -**$this->db->count_all()** - -Permits you to determine the number of rows in a particular table. -Submit the table name in the first parameter. Example:: - - echo $this->db->count_all('my_table'); // Produces an integer, like 25 - -************** -Query grouping -************** - -Query grouping allows you to create groups of WHERE clauses by enclosing them in parentheses. This will allow -you to create queries with complex WHERE clauses. Nested groups are supported. Example:: - - $this->db->select('*')->from('my_table') - ->group_start() - ->where('a', 'a') - ->or_group_start() - ->where('b', 'b') - ->where('c', 'c') - ->group_end() - ->group_end() - ->where('d', 'd') - ->get(); - - // Generates: - // SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd' - -.. note:: groups need to be balanced, make sure every group_start() is matched by a group_end(). - -**$this->db->group_start()** - -Starts a new group by adding an opening parenthesis to the WHERE clause of the query. - -**$this->db->or_group_start()** - -Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'OR'. - -**$this->db->not_group_start()** - -Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'NOT'. - -**$this->db->or_not_group_start()** - -Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with 'OR NOT'. - -**$this->db->group_end()** - -Ends the current group by adding an closing parenthesis to the WHERE clause of the query. - -************** -Inserting Data -************** - -**$this->db->insert()** - -Generates an insert string based on the data you supply, and runs the -query. You can either pass an **array** or an **object** to the -function. Here is an example using an array:: - - $data = array( - 'title' => 'My title', - 'name' => 'My Name', - 'date' => 'My date' - ); - - $this->db->insert('mytable', $data); - // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') - -The first parameter will contain the table name, the second is an -associative array of values. - -Here is an example using an object:: - - /* - class Myclass { - public $title = 'My Title'; - public $content = 'My Content'; - public $date = 'My Date'; - } - */ - - $object = new Myclass; - $this->db->insert('mytable', $object); - // Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date') - -The first parameter will contain the table name, the second is an -object. - -.. note:: All values are escaped automatically producing safer queries. - -**$this->db->get_compiled_insert()** - -Compiles the insertion query just like $this->db->insert() but does not -*run* the query. This method simply returns the SQL query as a string. - -Example:: - - $data = array( - 'title' => 'My title', - 'name' => 'My Name', - 'date' => 'My date' - ); - - $sql = $this->db->set($data)->get_compiled_insert('mytable'); - echo $sql; - - // Produces string: INSERT INTO mytable (`title`, `name`, `date`) VALUES ('My title', 'My name', 'My date') - -The second parameter enables you to set whether or not the query builder query -will be reset (by default it will be--just like $this->db->insert()):: - - echo $this->db->set('title', 'My Title')->get_compiled_insert('mytable', FALSE); - - // Produces string: INSERT INTO mytable (`title`) VALUES ('My Title') - - echo $this->db->set('content', 'My Content')->get_compiled_insert(); - - // Produces string: INSERT INTO mytable (`title`, `content`) VALUES ('My Title', 'My Content') - -The key thing to notice in the above example is that the second query did not -utilize `$this->db->from()` nor did it pass a table name into the first -parameter. The reason this worked is because the query has not been executed -using `$this->db->insert()` which resets values or reset directly using -`$this->db->reset_query()`. - -.. note:: This method doesn't work for batched inserts. - -**$this->db->insert_batch()** - -Generates an insert string based on the data you supply, and runs the -query. You can either pass an **array** or an **object** to the -function. Here is an example using an array:: - - $data = array( - array( - 'title' => 'My title', - 'name' => 'My Name', - 'date' => 'My date' - ), - array( - 'title' => 'Another title', - 'name' => 'Another Name', - 'date' => 'Another date' - ) - ); - - $this->db->insert_batch('mytable', $data); - // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date') - -The first parameter will contain the table name, the second is an -associative array of values. - -.. note:: All values are escaped automatically producing safer queries. - -************* -Updating Data -************* - -**$this->db->replace()** - -This method executes a REPLACE statement, which is basically the SQL -standard for (optional) DELETE + INSERT, using *PRIMARY* and *UNIQUE* -keys as the determining factor. -In our case, it will save you from the need to implement complex -logics with different combinations of ``select()``, ``update()``, -``delete()`` and ``insert()`` calls. - -Example:: - - $data = array( - 'title' => 'My title', - 'name' => 'My Name', - 'date' => 'My date' - ); - - $this->db->replace('table', $data); - - // Executes: REPLACE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') - -In the above example, if we assume that the *title* field is our primary -key, then if a row containing 'My title' as the *title* value, that row -will be deleted with our new row data replacing it. - -Usage of the ``set()`` method is also allowed and all fields are -automatically escaped, just like with ``insert()``. - -**$this->db->set()** - -This function enables you to set values for inserts or updates. - -**It can be used instead of passing a data array directly to the insert -or update functions:** - -:: - - $this->db->set('name', $name); - $this->db->insert('mytable'); // Produces: INSERT INTO mytable (`name`) VALUES ('{$name}') - -If you use multiple function called they will be assembled properly -based on whether you are doing an insert or an update:: - - $this->db->set('name', $name); - $this->db->set('title', $title); - $this->db->set('status', $status); - $this->db->insert('mytable'); - -**set()** will also accept an optional third parameter (``$escape``), that -will prevent data from being escaped if set to FALSE. To illustrate the -difference, here is ``set()`` used both with and without the escape -parameter. - -:: - - $this->db->set('field', 'field+1', FALSE); - $this->db->where('id', 2); - $this->db->update('mytable'); // gives UPDATE mytable SET field = field+1 WHERE id = 2 - - $this->db->set('field', 'field+1'); - $this->db->where('id', 2); - $this->db->update('mytable'); // gives UPDATE `mytable` SET `field` = 'field+1' WHERE `id` = 2 - -You can also pass an associative array to this function:: - - $array = array( - 'name' => $name, - 'title' => $title, - 'status' => $status - ); - - $this->db->set($array); - $this->db->insert('mytable'); - -Or an object:: - - /* - class Myclass { - public $title = 'My Title'; - public $content = 'My Content'; - public $date = 'My Date'; - } - */ - - $object = new Myclass; - $this->db->set($object); - $this->db->insert('mytable'); - -**$this->db->update()** - -Generates an update string and runs the query based on the data you -supply. You can pass an **array** or an **object** to the function. Here -is an example using an array:: - - $data = array( - 'title' => $title, - 'name' => $name, - 'date' => $date - ); - - $this->db->where('id', $id); - $this->db->update('mytable', $data); - // Produces: - // - // UPDATE mytable - // SET title = '{$title}', name = '{$name}', date = '{$date}' - // WHERE id = $id - -Or you can supply an object:: - - /* - class Myclass { - public $title = 'My Title'; - public $content = 'My Content'; - public $date = 'My Date'; - } - */ - - $object = new Myclass; - $this->db->where('id', $id); - $this->db->update('mytable', $object); - // Produces: - // - // UPDATE `mytable` - // SET `title` = '{$title}', `name` = '{$name}', `date` = '{$date}' - // WHERE id = `$id` - -.. note:: All values are escaped automatically producing safer queries. - -You'll notice the use of the $this->db->where() function, enabling you -to set the WHERE clause. You can optionally pass this information -directly into the update function as a string:: - - $this->db->update('mytable', $data, "id = 4"); - -Or as an array:: - - $this->db->update('mytable', $data, array('id' => $id)); - -You may also use the $this->db->set() function described above when -performing updates. - -**$this->db->update_batch()** - -Generates an update string based on the data you supply, and runs the query. -You can either pass an **array** or an **object** to the function. -Here is an example using an array:: - - $data = array( - array( - 'title' => 'My title' , - 'name' => 'My Name 2' , - 'date' => 'My date 2' - ), - array( - 'title' => 'Another title' , - 'name' => 'Another Name 2' , - 'date' => 'Another date 2' - ) - ); - - $this->db->update_batch('mytable', $data, 'title'); - - // Produces: - // UPDATE `mytable` SET `name` = CASE - // WHEN `title` = 'My title' THEN 'My Name 2' - // WHEN `title` = 'Another title' THEN 'Another Name 2' - // ELSE `name` END, - // `date` = CASE - // WHEN `title` = 'My title' THEN 'My date 2' - // WHEN `title` = 'Another title' THEN 'Another date 2' - // ELSE `date` END - // WHERE `title` IN ('My title','Another title') - -The first parameter will contain the table name, the second is an associative -array of values, the third parameter is the where key. - -.. note:: All values are escaped automatically producing safer queries. - -.. note:: ``affected_rows()`` won't give you proper results with this method, - due to the very nature of how it works. Instead, ``update_batch()`` - returns the number of rows affected. - -**$this->db->get_compiled_update()** - -This works exactly the same way as ``$this->db->get_compiled_insert()`` except -that it produces an UPDATE SQL string instead of an INSERT SQL string. - -For more information view documentation for `$this->db->get_compiled_insert()`. - -.. note:: This method doesn't work for batched updates. - -************* -Deleting Data -************* - -**$this->db->delete()** - -Generates a delete SQL string and runs the query. - -:: - - $this->db->delete('mytable', array('id' => $id)); // Produces: // DELETE FROM mytable // WHERE id = $id - -The first parameter is the table name, the second is the where clause. -You can also use the where() or or_where() functions instead of passing -the data to the second parameter of the function:: - - $this->db->where('id', $id); - $this->db->delete('mytable'); - - // Produces: - // DELETE FROM mytable - // WHERE id = $id - - -An array of table names can be passed into delete() if you would like to -delete data from more than 1 table. - -:: - - $tables = array('table1', 'table2', 'table3'); - $this->db->where('id', '5'); - $this->db->delete($tables); - - -If you want to delete all data from a table, you can use the truncate() -function, or empty_table(). - -**$this->db->empty_table()** - -Generates a delete SQL string and runs the -query.:: - - $this->db->empty_table('mytable'); // Produces: DELETE FROM mytable - -**$this->db->truncate()** - -Generates a truncate SQL string and runs the query. - -:: - - $this->db->from('mytable'); - $this->db->truncate(); - - // or - - $this->db->truncate('mytable'); - - // Produce: - // TRUNCATE mytable - -.. note:: If the TRUNCATE command isn't available, truncate() will - execute as "DELETE FROM table". - -**$this->db->get_compiled_delete()** - -This works exactly the same way as ``$this->db->get_compiled_insert()`` except -that it produces a DELETE SQL string instead of an INSERT SQL string. - -For more information view documentation for $this->db->get_compiled_insert(). - -*************** -Method Chaining -*************** - -Method chaining allows you to simplify your syntax by connecting -multiple functions. Consider this example:: - - $query = $this->db->select('title') - ->where('id', $id) - ->limit(10, 20) - ->get('mytable'); - -.. _ar-caching: - -********************* -Query Builder Caching -********************* - -While not "true" caching, Query Builder enables you to save (or "cache") -certain parts of your queries for reuse at a later point in your -script's execution. Normally, when an Query Builder call is completed, -all stored information is reset for the next call. With caching, you can -prevent this reset, and reuse information easily. - -Cached calls are cumulative. If you make 2 cached select() calls, and -then 2 uncached select() calls, this will result in 4 select() calls. -There are three Caching functions available: - -**$this->db->start_cache()** - -This function must be called to begin caching. All Query Builder queries -of the correct type (see below for supported queries) are stored for -later use. - -**$this->db->stop_cache()** - -This function can be called to stop caching. - -**$this->db->flush_cache()** - -This function deletes all items from the Query Builder cache. - -An example of caching ---------------------- - -Here's a usage example:: - - $this->db->start_cache(); - $this->db->select('field1'); - $this->db->stop_cache(); - $this->db->get('tablename'); - //Generates: SELECT `field1` FROM (`tablename`) - - $this->db->select('field2'); - $this->db->get('tablename'); - //Generates: SELECT `field1`, `field2` FROM (`tablename`) - - $this->db->flush_cache(); - $this->db->select('field2'); - $this->db->get('tablename'); - //Generates: SELECT `field2` FROM (`tablename`) - - -.. note:: The following statements can be cached: select, from, join, - where, like, group_by, having, order_by - - -*********************** -Resetting Query Builder -*********************** - -**$this->db->reset_query()** - -Resetting Query Builder allows you to start fresh with your query without -executing it first using a method like $this->db->get() or $this->db->insert(). -Just like the methods that execute a query, this will *not* reset items you've -cached using `Query Builder Caching`_. - -This is useful in situations where you are using Query Builder to generate SQL -(ex. ``$this->db->get_compiled_select()``) but then choose to, for instance, -run the query:: - - // Note that the second parameter of the get_compiled_select method is FALSE - $sql = $this->db->select(array('field1','field2')) - ->where('field3',5) - ->get_compiled_select('mytable', FALSE); - - // ... - // Do something crazy with the SQL code... like add it to a cron script for - // later execution or something... - // ... - - $data = $this->db->get()->result_array(); - - // Would execute and return an array of results of the following query: - // SELECT field1, field1 from mytable where field3 = 5; - -.. note:: Double calls to ``get_compiled_select()`` while you're using the - Query Builder Caching functionality and NOT resetting your queries - will results in the cache being merged twice. That in turn will - i.e. if you're caching a ``select()`` - select the same field twice. - -*************** -Class Reference -*************** - -.. php:class:: CI_DB_query_builder - - .. php:method:: reset_query() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Resets the current Query Builder state. Useful when you want - to build a query that can be cancelled under certain conditions. - - .. php:method:: start_cache() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Starts the Query Builder cache. - - .. php:method:: stop_cache() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Stops the Query Builder cache. - - .. php:method:: flush_cache() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Empties the Query Builder cache. - - .. php:method:: set_dbprefix([$prefix = '']) - - :param string $prefix: The new prefix to use - :returns: The DB prefix in use - :rtype: string - - Sets the database prefix, without having to reconnect. - - .. php:method:: dbprefix([$table = '']) - - :param string $table: The table name to prefix - :returns: The prefixed table name - :rtype: string - - Prepends a database prefix, if one exists in configuration. - - .. php:method:: count_all_results([$table = '', [$reset = TRUE]]) - - :param string $table: Table name - :param bool $reset: Whether to reset values for SELECTs - :returns: Number of rows in the query result - :rtype: int - - Generates a platform-specific query string that counts - all records returned by an Query Builder query. - - .. php:method:: get([$table = ''[, $limit = NULL[, $offset = NULL]]]) - - :param string $table: The table to query - :param int $limit: The LIMIT clause - :param int $offset: The OFFSET clause - :returns: CI_DB_result instance (method chaining) - :rtype: CI_DB_result - - Compiles and runs SELECT statement based on the already - called Query Builder methods. - - .. php:method:: get_where([$table = ''[, $where = NULL[, $limit = NULL[, $offset = NULL]]]]) - - :param mixed $table: The table(s) to fetch data from; string or array - :param string $where: The WHERE clause - :param int $limit: The LIMIT clause - :param int $offset: The OFFSET clause - :returns: CI_DB_result instance (method chaining) - :rtype: CI_DB_result - - Same as ``get()``, but also allows the WHERE to be added directly. - - .. php:method:: select([$select = '*'[, $escape = NULL]]) - - :param string $select: The SELECT portion of a query - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a SELECT clause to a query. - - .. php:method:: select_avg([$select = ''[, $alias = '']]) - - :param string $select: Field to compute the average of - :param string $alias: Alias for the resulting value name - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a SELECT AVG(field) clause to a query. - - .. php:method:: select_max([$select = ''[, $alias = '']]) - - :param string $select: Field to compute the maximum of - :param string $alias: Alias for the resulting value name - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a SELECT MAX(field) clause to a query. - - .. php:method:: select_min([$select = ''[, $alias = '']]) - - :param string $select: Field to compute the minimum of - :param string $alias: Alias for the resulting value name - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a SELECT MIN(field) clause to a query. - - .. php:method:: select_sum([$select = ''[, $alias = '']]) - - :param string $select: Field to compute the sum of - :param string $alias: Alias for the resulting value name - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a SELECT SUM(field) clause to a query. - - .. php:method:: distinct([$val = TRUE]) - - :param bool $val: Desired value of the "distinct" flag - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Sets a flag which tells the query builder to add - a DISTINCT clause to the SELECT portion of the query. - - .. php:method:: from($from) - - :param mixed $from: Table name(s); string or array - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Specifies the FROM clause of a query. - - .. php:method:: join($table, $cond[, $type = ''[, $escape = NULL]]) - - :param string $table: Table name to join - :param string $cond: The JOIN ON condition - :param string $type: The JOIN type - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a JOIN clause to a query. - - .. php:method:: where($key[, $value = NULL[, $escape = NULL]]) - - :param mixed $key: Name of field to compare, or associative array - :param mixed $value: If a single key, compared to this value - :param bool $escape: Whether to escape values and identifiers - :returns: DB_query_builder instance - :rtype: object - - Generates the WHERE portion of the query. - Separates multiple calls with 'AND'. - - .. php:method:: or_where($key[, $value = NULL[, $escape = NULL]]) - - :param mixed $key: Name of field to compare, or associative array - :param mixed $value: If a single key, compared to this value - :param bool $escape: Whether to escape values and identifiers - :returns: DB_query_builder instance - :rtype: object - - Generates the WHERE portion of the query. - Separates multiple calls with 'OR'. - - .. php:method:: or_where_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) - - :param string $key: The field to search - :param array $values: The values searched on - :param bool $escape: Whether to escape values and identifiers - :returns: DB_query_builder instance - :rtype: object - - Generates a WHERE field IN('item', 'item') SQL query, - joined with 'OR' if appropriate. - - .. php:method:: or_where_not_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) - - :param string $key: The field to search - :param array $values: The values searched on - :param bool $escape: Whether to escape values and identifiers - :returns: DB_query_builder instance - :rtype: object - - Generates a WHERE field NOT IN('item', 'item') SQL query, - joined with 'OR' if appropriate. - - .. php:method:: where_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) - - :param string $key: Name of field to examine - :param array $values: Array of target values - :param bool $escape: Whether to escape values and identifiers - :returns: DB_query_builder instance - :rtype: object - - Generates a WHERE field IN('item', 'item') SQL query, - joined with 'AND' if appropriate. - - .. php:method:: where_not_in([$key = NULL[, $values = NULL[, $escape = NULL]]]) - - :param string $key: Name of field to examine - :param array $values: Array of target values - :param bool $escape: Whether to escape values and identifiers - :returns: DB_query_builder instance - :rtype: object - - Generates a WHERE field NOT IN('item', 'item') SQL query, - joined with 'AND' if appropriate. - - .. php:method:: group_start() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Starts a group expression, using ANDs for the conditions inside it. - - .. php:method:: or_group_start() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Starts a group expression, using ORs for the conditions inside it. - - .. php:method:: not_group_start() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Starts a group expression, using AND NOTs for the conditions inside it. - - .. php:method:: or_not_group_start() - - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Starts a group expression, using OR NOTs for the conditions inside it. - - .. php:method:: group_end() - - :returns: DB_query_builder instance - :rtype: object - - Ends a group expression. - - .. php:method:: like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) - - :param string $field: Field name - :param string $match: Text portion to match - :param string $side: Which side of the expression to put the '%' wildcard on - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a LIKE clause to a query, separating multiple calls with AND. - - .. php:method:: or_like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) - - :param string $field: Field name - :param string $match: Text portion to match - :param string $side: Which side of the expression to put the '%' wildcard on - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a LIKE clause to a query, separating multiple class with OR. - - .. php:method:: not_like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) - - :param string $field: Field name - :param string $match: Text portion to match - :param string $side: Which side of the expression to put the '%' wildcard on - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a NOT LIKE clause to a query, separating multiple calls with AND. - - .. php:method:: or_not_like($field[, $match = ''[, $side = 'both'[, $escape = NULL]]]) - - :param string $field: Field name - :param string $match: Text portion to match - :param string $side: Which side of the expression to put the '%' wildcard on - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a NOT LIKE clause to a query, separating multiple calls with OR. - - .. php:method:: having($key[, $value = NULL[, $escape = NULL]]) - - :param mixed $key: Identifier (string) or associative array of field/value pairs - :param string $value: Value sought if $key is an identifier - :param string $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a HAVING clause to a query, separating multiple calls with AND. - - .. php:method:: or_having($key[, $value = NULL[, $escape = NULL]]) - - :param mixed $key: Identifier (string) or associative array of field/value pairs - :param string $value: Value sought if $key is an identifier - :param string $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a HAVING clause to a query, separating multiple calls with OR. - - .. php:method:: group_by($by[, $escape = NULL]) - - :param mixed $by: Field(s) to group by; string or array - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds a GROUP BY clause to a query. - - .. php:method:: order_by($orderby[, $direction = ''[, $escape = NULL]]) - - :param string $orderby: Field to order by - :param string $direction: The order requested - ASC, DESC or random - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds an ORDER BY clause to a query. - - .. php:method:: limit($value[, $offset = 0]) - - :param int $value: Number of rows to limit the results to - :param int $offset: Number of rows to skip - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds LIMIT and OFFSET clauses to a query. - - .. php:method:: offset($offset) - - :param int $offset: Number of rows to skip - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds an OFFSET clause to a query. - - .. php:method:: set($key[, $value = ''[, $escape = NULL]]) - - :param mixed $key: Field name, or an array of field/value pairs - :param string $value: Field value, if $key is a single field - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds field/value pairs to be passed later to ``insert()``, - ``update()`` or ``replace()``. - - .. php:method:: insert([$table = ''[, $set = NULL[, $escape = NULL]]]) - - :param string $table: Table name - :param array $set: An associative array of field/value pairs - :param bool $escape: Whether to escape values and identifiers - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Compiles and executes an INSERT statement. - - .. php:method:: insert_batch($table[, $set = NULL[, $escape = NULL[, $batch_size = 100]]]) - - :param string $table: Table name - :param array $set: Data to insert - :param bool $escape: Whether to escape values and identifiers - :param int $batch_size: Count of rows to insert at once - :returns: Number of rows inserted or FALSE on failure - :rtype: mixed - - Compiles and executes batch ``INSERT`` statements. - - .. note:: When more than ``$batch_size`` rows are provided, multiple - ``INSERT`` queries will be executed, each trying to insert - up to ``$batch_size`` rows. - - .. php:method:: set_insert_batch($key[, $value = ''[, $escape = NULL]]) - - :param mixed $key: Field name or an array of field/value pairs - :param string $value: Field value, if $key is a single field - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds field/value pairs to be inserted in a table later via ``insert_batch()``. - - .. php:method:: update([$table = ''[, $set = NULL[, $where = NULL[, $limit = NULL]]]]) - - :param string $table: Table name - :param array $set: An associative array of field/value pairs - :param string $where: The WHERE clause - :param int $limit: The LIMIT clause - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Compiles and executes an UPDATE statement. - - .. php:method:: update_batch($table[, $set = NULL[, $value = NULL[, $batch_size = 100]]]) - - :param string $table: Table name - :param array $set: Field name, or an associative array of field/value pairs - :param string $value: Field value, if $set is a single field - :param int $batch_size: Count of conditions to group in a single query - :returns: Number of rows updated or FALSE on failure - :rtype: mixed - - Compiles and executes batch ``UPDATE`` statements. - - .. note:: When more than ``$batch_size`` field/value pairs are provided, - multiple queries will be executed, each handling up to - ``$batch_size`` field/value pairs. - - .. php:method:: set_update_batch($key[, $value = ''[, $escape = NULL]]) - - :param mixed $key: Field name or an array of field/value pairs - :param string $value: Field value, if $key is a single field - :param bool $escape: Whether to escape values and identifiers - :returns: CI_DB_query_builder instance (method chaining) - :rtype: CI_DB_query_builder - - Adds field/value pairs to be updated in a table later via ``update_batch()``. - - .. php:method:: replace([$table = ''[, $set = NULL]]) - - :param string $table: Table name - :param array $set: An associative array of field/value pairs - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Compiles and executes a REPLACE statement. - - .. php:method:: delete([$table = ''[, $where = ''[, $limit = NULL[, $reset_data = TRUE]]]]) - - :param mixed $table: The table(s) to delete from; string or array - :param string $where: The WHERE clause - :param int $limit: The LIMIT clause - :param bool $reset_data: TRUE to reset the query "write" clause - :returns: CI_DB_query_builder instance (method chaining) or FALSE on failure - :rtype: mixed - - Compiles and executes a DELETE query. - - .. php:method:: truncate([$table = '']) - - :param string $table: Table name - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Executes a TRUNCATE statement on a table. - - .. note:: If the database platform in use doesn't support TRUNCATE, - a DELETE statement will be used instead. - - .. php:method:: empty_table([$table = '']) - - :param string $table: Table name - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Deletes all records from a table via a DELETE statement. - - .. php:method:: get_compiled_select([$table = ''[, $reset = TRUE]]) - - :param string $table: Table name - :param bool $reset: Whether to reset the current QB values or not - :returns: The compiled SQL statement as a string - :rtype: string - - Compiles a SELECT statement and returns it as a string. - - .. php:method:: get_compiled_insert([$table = ''[, $reset = TRUE]]) - - :param string $table: Table name - :param bool $reset: Whether to reset the current QB values or not - :returns: The compiled SQL statement as a string - :rtype: string - - Compiles an INSERT statement and returns it as a string. - - .. php:method:: get_compiled_update([$table = ''[, $reset = TRUE]]) - - :param string $table: Table name - :param bool $reset: Whether to reset the current QB values or not - :returns: The compiled SQL statement as a string - :rtype: string - - Compiles an UPDATE statement and returns it as a string. - - .. php:method:: get_compiled_delete([$table = ''[, $reset = TRUE]]) - - :param string $table: Table name - :param bool $reset: Whether to reset the current QB values or not - :returns: The compiled SQL statement as a string - :rtype: string - - Compiles a DELETE statement and returns it as a string. diff --git a/user_guide_src/source/database/results.rst b/user_guide_src/source/database/results.rst deleted file mode 100644 index 6fea7c6be..000000000 --- a/user_guide_src/source/database/results.rst +++ /dev/null @@ -1,507 +0,0 @@ -######################## -Generating Query Results -######################## - -There are several ways to generate query results: - -.. contents:: - :local: - :depth: 2 - -************* -Result Arrays -************* - -**result()** - -This method returns the query result as an array of **objects**, or -**an empty array** on failure. Typically you'll use this in a foreach -loop, like this:: - - $query = $this->db->query("YOUR QUERY"); - - foreach ($query->result() as $row) - { - echo $row->title; - echo $row->name; - echo $row->body; - } - -The above method is an alias of ``result_object()``. - -You can also pass a string to ``result()`` which represents a class to -instantiate for each result object (note: this class must be loaded) - -:: - - $query = $this->db->query("SELECT * FROM users;"); - - foreach ($query->result('User') as $user) - { - echo $user->name; // access attributes - echo $user->reverse_name(); // or methods defined on the 'User' class - } - -**result_array()** - -This method returns the query result as a pure array, or an empty -array when no result is produced. Typically you'll use this in a foreach -loop, like this:: - - $query = $this->db->query("YOUR QUERY"); - - foreach ($query->result_array() as $row) - { - echo $row['title']; - echo $row['name']; - echo $row['body']; - } - -*********** -Result Rows -*********** - -**row()** - -This method returns a single result row. If your query has more than -one row, it returns only the first row. The result is returned as an -**object**. Here's a usage example:: - - $query = $this->db->query("YOUR QUERY"); - - $row = $query->row(); - - if (isset($row)) - { - echo $row->title; - echo $row->name; - echo $row->body; - } - -If you want a specific row returned you can submit the row number as a -digit in the first parameter:: - - $row = $query->row(5); - -You can also add a second String parameter, which is the name of a class -to instantiate the row with:: - - $query = $this->db->query("SELECT * FROM users LIMIT 1;"); - $row = $query->row(0, 'User'); - - echo $row->name; // access attributes - echo $row->reverse_name(); // or methods defined on the 'User' class - -**row_array()** - -Identical to the above ``row()`` method, except it returns an array. -Example:: - - $query = $this->db->query("YOUR QUERY"); - - $row = $query->row_array(); - - if (isset($row)) - { - echo $row['title']; - echo $row['name']; - echo $row['body']; - } - -If you want a specific row returned you can submit the row number as a -digit in the first parameter:: - - $row = $query->row_array(5); - -In addition, you can walk forward/backwards/first/last through your -results using these variations: - - | **$row = $query->first_row()** - | **$row = $query->last_row()** - | **$row = $query->next_row()** - | **$row = $query->previous_row()** - -By default they return an object unless you put the word "array" in the -parameter: - - | **$row = $query->first_row('array')** - | **$row = $query->last_row('array')** - | **$row = $query->next_row('array')** - | **$row = $query->previous_row('array')** - -.. note:: All the methods above will load the whole result into memory - (prefetching). Use ``unbuffered_row()`` for processing large - result sets. - -**unbuffered_row()** - -This method returns a single result row without prefetching the whole -result in memory as ``row()`` does. If your query has more than one row, -it returns the current row and moves the internal data pointer ahead. - -:: - - $query = $this->db->query("YOUR QUERY"); - - while ($row = $query->unbuffered_row()) - { - echo $row->title; - echo $row->name; - echo $row->body; - } - -You can optionally pass 'object' (default) or 'array' in order to specify -the returned value's type:: - - $query->unbuffered_row(); // object - $query->unbuffered_row('object'); // object - $query->unbuffered_row('array'); // associative array - -********************* -Custom Result Objects -********************* - -You can have the results returned as an instance of a custom class instead -of a ``stdClass`` or array, as the ``result()`` and ``result_array()`` -methods allow. This requires that the class is already loaded into memory. -The object will have all values returned from the database set as properties. -If these have been declared and are non-public then you should provide a -``__set()`` method to allow them to be set. - -Example:: - - class User { - - public $id; - public $email; - public $username; - - protected $last_login; - - public function last_login($format) - { - return $this->last_login->format($format); - } - - public function __set($name, $value) - { - if ($name === 'last_login') - { - $this->last_login = DateTime::createFromFormat('U', $value); - } - } - - public function __get($name) - { - if (isset($this->$name)) - { - return $this->$name; - } - } - } - -In addition to the two methods listed below, the following methods also can -take a class name to return the results as: ``first_row()``, ``last_row()``, -``next_row()``, and ``previous_row()``. - -**custom_result_object()** - -Returns the entire result set as an array of instances of the class requested. -The only parameter is the name of the class to instantiate. - -Example:: - - $query = $this->db->query("YOUR QUERY"); - - $rows = $query->custom_result_object('User'); - - foreach ($rows as $row) - { - echo $row->id; - echo $row->email; - echo $row->last_login('Y-m-d'); - } - -**custom_row_object()** - -Returns a single row from your query results. The first parameter is the row -number of the results. The second parameter is the class name to instantiate. - -Example:: - - $query = $this->db->query("YOUR QUERY"); - - $row = $query->custom_row_object(0, 'User'); - - if (isset($row)) - { - echo $row->email; // access attributes - echo $row->last_login('Y-m-d'); // access class methods - } - -You can also use the ``row()`` method in exactly the same way. - -Example:: - - $row = $query->custom_row_object(0, 'User'); - -********************* -Result Helper Methods -********************* - -**num_rows()** - -The number of rows returned by the query. Note: In this example, $query -is the variable that the query result object is assigned to:: - - $query = $this->db->query('SELECT * FROM my_table'); - - echo $query->num_rows(); - -.. note:: Not all database drivers have a native way of getting the total - number of rows for a result set. When this is the case, all of - the data is prefetched and ``count()`` is manually called on the - resulting array in order to achieve the same result. - -**num_fields()** - -The number of FIELDS (columns) returned by the query. Make sure to call -the method using your query result object:: - - $query = $this->db->query('SELECT * FROM my_table'); - - echo $query->num_fields(); - -**free_result()** - -It frees the memory associated with the result and deletes the result -resource ID. Normally PHP frees its memory automatically at the end of -script execution. However, if you are running a lot of queries in a -particular script you might want to free the result after each query -result has been generated in order to cut down on memory consumption. - -Example:: - - $query = $this->db->query('SELECT title FROM my_table'); - - foreach ($query->result() as $row) - { - echo $row->title; - } - - $query->free_result(); // The $query result object will no longer be available - - $query2 = $this->db->query('SELECT name FROM some_table'); - - $row = $query2->row(); - echo $row->name; - $query2->free_result(); // The $query2 result object will no longer be available - -**data_seek()** - -This method sets the internal pointer for the next result row to be -fetched. It is only useful in combination with ``unbuffered_row()``. - -It accepts a positive integer value, which defaults to 0 and returns -TRUE on success or FALSE on failure. - -:: - - $query = $this->db->query('SELECT `field_name` FROM `table_name`'); - $query->data_seek(5); // Skip the first 5 rows - $row = $query->unbuffered_row(); - -.. note:: Not all database drivers support this feature and will return FALSE. - Most notably - you won't be able to use it with PDO. - -*************** -Class Reference -*************** - -.. php:class:: CI_DB_result - - .. php:method:: result([$type = 'object']) - - :param string $type: Type of requested results - array, object, or class name - :returns: Array containing the fetched rows - :rtype: array - - A wrapper for the ``result_array()``, ``result_object()`` - and ``custom_result_object()`` methods. - - Usage: see `Result Arrays`_. - - .. php:method:: result_array() - - :returns: Array containing the fetched rows - :rtype: array - - Returns the query results as an array of rows, where each - row is itself an associative array. - - Usage: see `Result Arrays`_. - - .. php:method:: result_object() - - :returns: Array containing the fetched rows - :rtype: array - - Returns the query results as an array of rows, where each - row is an object of type ``stdClass``. - - Usage: see `Result Arrays`_. - - .. php:method:: custom_result_object($class_name) - - :param string $class_name: Class name for the resulting rows - :returns: Array containing the fetched rows - :rtype: array - - Returns the query results as an array of rows, where each - row is an instance of the specified class. - - .. php:method:: row([$n = 0[, $type = 'object']]) - - :param int $n: Index of the query results row to be returned - :param string $type: Type of the requested result - array, object, or class name - :returns: The requested row or NULL if it doesn't exist - :rtype: mixed - - A wrapper for the ``row_array()``, ``row_object() and - ``custom_row_object()`` methods. - - Usage: see `Result Rows`_. - - .. php:method:: unbuffered_row([$type = 'object']) - - :param string $type: Type of the requested result - array, object, or class name - :returns: Next row from the result set or NULL if it doesn't exist - :rtype: mixed - - Fetches the next result row and returns it in the - requested form. - - Usage: see `Result Rows`_. - - .. php:method:: row_array([$n = 0]) - - :param int $n: Index of the query results row to be returned - :returns: The requested row or NULL if it doesn't exist - :rtype: array - - Returns the requested result row as an associative array. - - Usage: see `Result Rows`_. - - .. php:method:: row_object([$n = 0]) - - :param int $n: Index of the query results row to be returned - :returns: The requested row or NULL if it doesn't exist - :rtype: stdClass - - Returns the requested result row as an object of type - ``stdClass``. - - Usage: see `Result Rows`_. - - .. php:method:: custom_row_object($n, $type) - - :param int $n: Index of the results row to return - :param string $class_name: Class name for the resulting row - :returns: The requested row or NULL if it doesn't exist - :rtype: $type - - Returns the requested result row as an instance of the - requested class. - - .. php:method:: data_seek([$n = 0]) - - :param int $n: Index of the results row to be returned next - :returns: TRUE on success, FALSE on failure - :rtype: bool - - Moves the internal results row pointer to the desired offset. - - Usage: see `Result Helper Methods`_. - - .. php:method:: set_row($key[, $value = NULL]) - - :param mixed $key: Column name or array of key/value pairs - :param mixed $value: Value to assign to the column, $key is a single field name - :rtype: void - - Assigns a value to a particular column. - - .. php:method:: next_row([$type = 'object']) - - :param string $type: Type of the requested result - array, object, or class name - :returns: Next row of result set, or NULL if it doesn't exist - :rtype: mixed - - Returns the next row from the result set. - - .. php:method:: previous_row([$type = 'object']) - - :param string $type: Type of the requested result - array, object, or class name - :returns: Previous row of result set, or NULL if it doesn't exist - :rtype: mixed - - Returns the previous row from the result set. - - .. php:method:: first_row([$type = 'object']) - - :param string $type: Type of the requested result - array, object, or class name - :returns: First row of result set, or NULL if it doesn't exist - :rtype: mixed - - Returns the first row from the result set. - - .. php:method:: last_row([$type = 'object']) - - :param string $type: Type of the requested result - array, object, or class name - :returns: Last row of result set, or NULL if it doesn't exist - :rtype: mixed - - Returns the last row from the result set. - - .. php:method:: num_rows() - - :returns: Number of rows in the result set - :rtype: int - - Returns the number of rows in the result set. - - Usage: see `Result Helper Methods`_. - - .. php:method:: num_fields() - - :returns: Number of fields in the result set - :rtype: int - - Returns the number of fields in the result set. - - Usage: see `Result Helper Methods`_. - - .. php:method:: field_data() - - :returns: Array containing field meta-data - :rtype: array - - Generates an array of ``stdClass`` objects containing - field meta-data. - - .. php:method:: free_result() - - :rtype: void - - Frees a result set. - - Usage: see `Result Helper Methods`_. - - .. php:method:: list_fields() - - :returns: Array of column names - :rtype: array - - Returns an array containing the field names in the - result set. diff --git a/user_guide_src/source/database/transactions.rst b/user_guide_src/source/database/transactions.rst deleted file mode 100644 index cfd6a566d..000000000 --- a/user_guide_src/source/database/transactions.rst +++ /dev/null @@ -1,127 +0,0 @@ -############ -Transactions -############ - -CodeIgniter's database abstraction allows you to use transactions with -databases that support transaction-safe table types. In MySQL, you'll -need to be running InnoDB or BDB table types rather than the more common -MyISAM. Most other database platforms support transactions natively. - -If you are not familiar with transactions we recommend you find a good -online resource to learn about them for your particular database. The -information below assumes you have a basic understanding of -transactions. - -CodeIgniter's Approach to Transactions -====================================== - -CodeIgniter utilizes an approach to transactions that is very similar to -the process used by the popular database class ADODB. We've chosen that -approach because it greatly simplifies the process of running -transactions. In most cases all that is required are two lines of code. - -Traditionally, transactions have required a fair amount of work to -implement since they demand that you keep track of your queries and -determine whether to commit or rollback based on the success or failure -of your queries. This is particularly cumbersome with nested queries. In -contrast, we've implemented a smart transaction system that does all -this for you automatically (you can also manage your transactions -manually if you choose to, but there's really no benefit). - -Running Transactions -==================== - -To run your queries using transactions you will use the -$this->db->trans_start() and $this->db->trans_complete() functions as -follows:: - - $this->db->trans_start(); - $this->db->query('AN SQL QUERY...'); - $this->db->query('ANOTHER QUERY...'); - $this->db->query('AND YET ANOTHER QUERY...'); - $this->db->trans_complete(); - -You can run as many queries as you want between the start/complete -functions and they will all be committed or rolled back based on success -or failure of any given query. - -Strict Mode -=========== - -By default CodeIgniter runs all transactions in Strict Mode. When strict -mode is enabled, if you are running multiple groups of transactions, if -one group fails all groups will be rolled back. If strict mode is -disabled, each group is treated independently, meaning a failure of one -group will not affect any others. - -Strict Mode can be disabled as follows:: - - $this->db->trans_strict(FALSE); - -Managing Errors -=============== - -If you have error reporting enabled in your config/database.php file -you'll see a standard error message if the commit was unsuccessful. If -debugging is turned off, you can manage your own errors like this:: - - $this->db->trans_start(); - $this->db->query('AN SQL QUERY...'); - $this->db->query('ANOTHER QUERY...'); - $this->db->trans_complete(); - - if ($this->db->trans_status() === FALSE) - { - // generate an error... or use the log_message() function to log your error - } - -Disabling Transactions -====================== - -If you would like to disable transactions you can do so using -``$this->db->trans_off()``:: - - $this->db->trans_off(); - - $this->db->trans_start(); - $this->db->query('AN SQL QUERY...'); - $this->db->trans_complete(); - -When transactions are disabled, your queries will be auto-committed, just as -they are when running queries without transactions, practically ignoring -any calls to ``trans_start()``, ``trans_complete()``, etc. - -Test Mode -========= - -You can optionally put the transaction system into "test mode", which -will cause your queries to be rolled back -- even if the queries produce -a valid result. To use test mode simply set the first parameter in the -$this->db->trans_start() function to TRUE:: - - $this->db->trans_start(TRUE); // Query will be rolled back - $this->db->query('AN SQL QUERY...'); - $this->db->trans_complete(); - -Running Transactions Manually -============================= - -If you would like to run transactions manually you can do so as follows:: - - $this->db->trans_begin(); - - $this->db->query('AN SQL QUERY...'); - $this->db->query('ANOTHER QUERY...'); - $this->db->query('AND YET ANOTHER QUERY...'); - - if ($this->db->trans_status() === FALSE) - { - $this->db->trans_rollback(); - } - else - { - $this->db->trans_commit(); - } - -.. note:: Make sure to use $this->db->trans_begin() when running manual - transactions, **NOT** $this->db->trans_start(). diff --git a/user_guide_src/source/database/utilities.rst b/user_guide_src/source/database/utilities.rst deleted file mode 100644 index 114291f85..000000000 --- a/user_guide_src/source/database/utilities.rst +++ /dev/null @@ -1,316 +0,0 @@ -###################### -Database Utility Class -###################### - -The Database Utility Class contains methods that help you manage your -database. - -.. contents:: - :local: - :depth: 2 - -****************************** -Initializing the Utility Class -****************************** - -.. important:: In order to initialize the Utility class, your database - driver must already be running, since the utilities class relies on it. - -Load the Utility Class as follows:: - - $this->load->dbutil(); - -You can also pass another database object to the DB Utility loader, in case -the database you want to manage isn't the default one:: - - $this->myutil = $this->load->dbutil($this->other_db, TRUE); - -In the above example, we're passing a custom database object as the first -parameter and then tell it to return the dbutil object, instead of -assigning it directly to ``$this->dbutil``. - -.. note:: Both of the parameters can be used individually, just pass an empty - value as the first one if you wish to skip it. - -Once initialized you will access the methods using the ``$this->dbutil`` -object:: - - $this->dbutil->some_method(); - -**************************** -Using the Database Utilities -**************************** - -Retrieve list of database names -================================ - -Returns an array of database names:: - - $dbs = $this->dbutil->list_databases(); - - foreach ($dbs as $db) - { - echo $db; - } - - -Determine If a Database Exists -============================== - -Sometimes it's helpful to know whether a particular database exists. -Returns a boolean TRUE/FALSE. Usage example:: - - if ($this->dbutil->database_exists('database_name')) - { - // some code... - } - -.. note:: Replace *database_name* with the name of the database you are - looking for. This method is case sensitive. - -Optimize a Table -================ - -Permits you to optimize a table using the table name specified in the -first parameter. Returns TRUE/FALSE based on success or failure:: - - if ($this->dbutil->optimize_table('table_name')) - { - echo 'Success!'; - } - -.. note:: Not all database platforms support table optimization. It is - mostly for use with MySQL. - -Repair a Table -============== - -Permits you to repair a table using the table name specified in the -first parameter. Returns TRUE/FALSE based on success or failure:: - - if ($this->dbutil->repair_table('table_name')) - { - echo 'Success!'; - } - -.. note:: Not all database platforms support table repairs. - -Optimize a Database -=================== - -Permits you to optimize the database your DB class is currently -connected to. Returns an array containing the DB status messages or -FALSE on failure. - -:: - - $result = $this->dbutil->optimize_database(); - - if ($result !== FALSE) - { - print_r($result); - } - -.. note:: Not all database platforms support database optimization. It - it is mostly for use with MySQL. - -Export a Query Result as a CSV File -=================================== - -Permits you to generate a CSV file from a query result. The first -parameter of the method must contain the result object from your -query. Example:: - - $this->load->dbutil(); - - $query = $this->db->query("SELECT * FROM mytable"); - - echo $this->dbutil->csv_from_result($query); - -The second, third, and fourth parameters allow you to set the delimiter -newline, and enclosure characters respectively. By default commas are -used as the delimiter, "\n" is used as a new line, and a double-quote -is used as the enclosure. Example:: - - $delimiter = ","; - $newline = "\r\n"; - $enclosure = '"'; - - echo $this->dbutil->csv_from_result($query, $delimiter, $newline, $enclosure); - -.. important:: This method will NOT write the CSV file for you. It - simply creates the CSV layout. If you need to write the file - use the :doc:`File Helper <../helpers/file_helper>`. - -Export a Query Result as an XML Document -======================================== - -Permits you to generate an XML file from a query result. The first -parameter expects a query result object, the second may contain an -optional array of config parameters. Example:: - - $this->load->dbutil(); - - $query = $this->db->query("SELECT * FROM mytable"); - - $config = array ( - 'root' => 'root', - 'element' => 'element', - 'newline' => "\n", - 'tab' => "\t" - ); - - echo $this->dbutil->xml_from_result($query, $config); - -.. important:: This method will NOT write the XML file for you. It - simply creates the XML layout. If you need to write the file - use the :doc:`File Helper <../helpers/file_helper>`. - -******************** -Backup Your Database -******************** - -Database Backup Notes -===================== - -Permits you to backup your full database or individual tables. The -backup data can be compressed in either Zip or Gzip format. - -.. note:: This feature is only available for MySQL and Interbase/Firebird databases. - -.. note:: For Interbase/Firebird databases, the backup file name is the only parameter. - - $this->dbutil->backup('db_backup_filename'); - -.. note:: Due to the limited execution time and memory available to PHP, - backing up very large databases may not be possible. If your database is - very large you might need to backup directly from your SQL server via - the command line, or have your server admin do it for you if you do not - have root privileges. - -Usage Example -============= - -:: - - // Load the DB utility class - $this->load->dbutil(); - - // Backup your entire database and assign it to a variable - $backup = $this->dbutil->backup(); - - // Load the file helper and write the file to your server - $this->load->helper('file'); - write_file('/path/to/mybackup.gz', $backup); - - // Load the download helper and send the file to your desktop - $this->load->helper('download'); - force_download('mybackup.gz', $backup); - -Setting Backup Preferences -========================== - -Backup preferences are set by submitting an array of values to the first -parameter of the ``backup()`` method. Example:: - - $prefs = array( - 'tables' => array('table1', 'table2'), // Array of tables to backup. - 'ignore' => array(), // List of tables to omit from the backup - 'format' => 'txt', // gzip, zip, txt - 'filename' => 'mybackup.sql', // File name - NEEDED ONLY WITH ZIP FILES - 'add_drop' => TRUE, // Whether to add DROP TABLE statements to backup file - 'add_insert' => TRUE, // Whether to add INSERT data to backup file - 'newline' => "\n" // Newline character used in backup file - ); - - $this->dbutil->backup($prefs); - -Description of Backup Preferences -================================= - -======================= ======================= ======================= ======================================================================== -Preference Default Value Options Description -======================= ======================= ======================= ======================================================================== -**tables** empty array None An array of tables you want backed up. If left blank all tables will be - exported. -**ignore** empty array None An array of tables you want the backup routine to ignore. -**format** gzip gzip, zip, txt The file format of the export file. -**filename** the current date/time None The name of the backed-up file. The name is needed only if you are using - zip compression. -**add_drop** TRUE TRUE/FALSE Whether to include DROP TABLE statements in your SQL export file. -**add_insert** TRUE TRUE/FALSE Whether to include INSERT statements in your SQL export file. -**newline** "\\n" "\\n", "\\r", "\\r\\n" Type of newline to use in your SQL export file. -**foreign_key_checks** TRUE TRUE/FALSE Whether output should keep foreign key checks enabled. -======================= ======================= ======================= ======================================================================== - -*************** -Class Reference -*************** - -.. php:class:: CI_DB_utility - - .. php:method:: backup([$params = array()]) - - :param array $params: An associative array of options - :returns: raw/(g)zipped SQL query string - :rtype: string - - Perform a database backup, per user preferences. - - .. php:method:: database_exists($database_name) - - :param string $database_name: Database name - :returns: TRUE if the database exists, FALSE otherwise - :rtype: bool - - Check for the existence of a database. - - .. php:method:: list_databases() - - :returns: Array of database names found - :rtype: array - - Retrieve a list of all the database names. - - .. php:method:: optimize_database() - - :returns: Array of optimization messages or FALSE on failure - :rtype: array - - Optimizes the database. - - .. php:method:: optimize_table($table_name) - - :param string $table_name: Name of the table to optimize - :returns: Array of optimization messages or FALSE on failure - :rtype: array - - Optimizes a database table. - - .. php:method:: repair_table($table_name) - - :param string $table_name: Name of the table to repair - :returns: Array of repair messages or FALSE on failure - :rtype: array - - Repairs a database table. - - .. php:method:: csv_from_result($query[, $delim = ','[, $newline = "\n"[, $enclosure = '"']]]) - - :param object $query: A database result object - :param string $delim: The CSV field delimiter to use - :param string $newline: The newline character to use - :param string $enclosure: The enclosure delimiter to use - :returns: The generated CSV file as a string - :rtype: string - - Translates a database result object into a CSV document. - - .. php:method:: xml_from_result($query[, $params = array()]) - - :param object $query: A database result object - :param array $params: An associative array of preferences - :returns: The generated XML document as a string - :rtype: string - - Translates a database result object into an XML document. |