CodeIgniter Connection database and Quick Start

First, the database configuration

CodeIgniter has a configuration file that lets you store database connection values (Username: username, password: password, database name: ). The configuration file is located in the application/config/database.php . You can also set the database connection values for a particular environment by placing different database.php files into a specific environment configuration folder.

The accessory file is stored in a multidimensional array in the following format:

$db[' Default '] [' hostname '] = "localhost";$db[' Default '] [' username '] = "root";$db[' Default '] [' password '] = "";$db[' Default '] [' database '] = "database_name";$db[' Default '] [' dbdriver '] = "MySQL";$db[' Default '] [' dbprefix '] = "";$db[' Default '] [' pconnect '] =TRUE;$db[' Default '] [' db_debug '] =FALSE;$db[' Default '] [' cache_on '] =FALSE;$db[' Default '] [' cachedir '] = "";$db[' Default '] [' char_set '] = "UTF8";$db[' Default '] [' dbcollat '] = "Utf8_general_ci";$db[' Default '] [' swap_pre '] = "";$db[' Default '] [' autoinit '] =TRUE;$db[' Default '] [' stricton '] =FALSE;

　　The reason we use multidimensional arrays is to allow you to arbitrarily store settings for multiple connection values. Example: If you run multiple environments (development: development, Production: production, Test: testing, and so on). ), you can establish a separate connection group for each environment and switch directly to the group. For example, to set up a "test" environment, you can do this:

$db[' Test '] [' hostname '] = "localhost";$db[' Test '] [' username '] = "root";$db[' Test '] [' password '] = "";$db[' Test '] [' database '] = "database_name";$db[' Test '] [' dbdriver '] = "MySQL";$db[' Test '] [' dbprefix '] = "";$db[' Test '] [' pconnect '] =TRUE;$db[' Test '] [' db_debug '] =FALSE;$db[' Test '] [' cache_on '] =FALSE;$db[' Test '] [' cachedir '] = "";$db[' Test '] [' char_set '] = "UTF8";$db[' Test '] [' dbcollat '] = "Utf8_general_ci";$db[' Test '] [' swap_pre '] = "";$db[' Test '] [' autoinit '] =TRUE;$db[' Test '] [' stricton '] =FALSE;

　　Then, tell the system to use the "test" group, you can set the variables that are located in the configuration file:

$active _group = "Test";

Note: The name "Test" is arbitrary, which allows you to set it free, our primary connection defaults to the name "Default", and of course, you can give it a more meaningful name based on your project.

Active Record

The Active Record class can be globally set through the $active_record variable in the database configuration file (Allow/Disallow True/false (Boolean)). If you don't use this class, Then you can reduce the consumption of computer resources when the database class is initialized by setting this variable value to false.

$active _record TRUE;

Note: Some codeigniter classes, such as sessions, require active records support when performing some functions.

Parameter resolution:

• hostname -the host name of the database, usually located on the local computer, can be represented as "localhost".
• Username -the user name that needs to be connected to the database.
• Password -the password for the login database.
• Database-The names of the databases you need to connect to.
• dbdriver -database type. such as: MySQL, Postgres, ODBC and so on. Must be a lowercase letter.
• Dbprefix -The prefix of the data table when the active record query is run, which allows multiple CodeIgniter programs to be installed on a database.
• pconnect -True/false (Boolean)-Use persistent connections.
• db_debug -True/false (Boolean)-Displays database error information.
• cache_on -True/false (Boolean)-the database query cache is open, see the database Cache class for details.
• Cachedir -The absolute path of the server where the database query cache directory resides.
• Char_set -the character set used when communicating with the database.
• Dbcollat -the character rule used when communicating with the database.

  tip: in the case of MySQL or mysqli database, if the server running environment PHP version is less than 5.2.3, MySQL version is less than 5.0.7, then this setting is only used to back up (queries created by database maintenance Class DB Forge). If you use a multibyte character set and use an incompatible mysql_real_escape_string () function in a low-version PHP environment, it will make your Web site more vulnerable to SQL injection.

• Swap_pre -Replaces the default Dbprefix table prefix, which is useful for distributed applications where you can use a table prefix that is customized by the end user.

  Tip: If you set the value of the $db [' Default '] [' dbprefix '] and also set the value of $db [' Default '] [' swap_pre '], the query code and $db[' default The value of ' [' Swap_pre '] is replaced with the value of the $db [' Default '] [' dbprefix '], and if $db [' Default '] [' swap_pre '] value is not set, directly precede the database table name with $db [' Default ' [' Dbprefix '] value.

• Autoinit -When the database library is loaded, the database needs to be automatically connected, and if set to False, the connection will be made before the first query.
• Stricton -True/false (Boolean)-whether to force the use of the "Strict Mode" connection, using Strict SQL is a good practice when developing a program.
• Port -the database port number. To use this value, you should add a line of code to the database configuration array.

$db [' Default '] [' port '] = 5432;

tip: Not all values are required, depending on the database platform you are using, such as (MySQL, Postgres, etc.) for example, when you use SQLite, you do not need to provide username or password, The database name is the path to your database file. The above assumes that you are using a MySQL database.

Second, connect the database

There are two ways to connect to a database:

Automatic connection

The Auto Connect feature automatically instantiates the database class when each page is loaded. To enable auto Connect, you can add the application/config/autoload.php following in the library array database :

$autoload Array (' database ');

Manual connection

If only a subset of the pages require a database connection, you can manually add the following code to your desired function or manually add it to your class.

$this->load->database ();

If the first parameter of the above function does not have any information, it will be found in the system-specified database configuration file, which is a preferred method for most people.

Available parameters

1. The database connection value, passed in an array or DSN string.
2. Whether to return connection Id,true/false (Boolean), the default value is FALSE (see "Connecting Multiple Databases" below).
3. Whether the Active Record class is enabled, True/false (Boolean), and the default value is NULL. If you have questions, check out /system/core/Loader.php the database() method

Manually connect to a database

The first parameter of the function allows you to freely specify the detailed database configuration information you have customized from your configuration file. Or you can even submit a database's connection properties without the specified configuration file. Examples:

To select a specified array from your configuration file you can do this:

$this->load->database (' group_name ');

group_nameRefers to the name of an array with database connection information that exists in your configuration file.

To manually connect to the database you require, you can do this by defining the following array:

$config[' hostname '] = "localhost";$config[' username '] = "MyUserName";$config[' password '] = "MyPassword";$config[' database '] = "MyDatabase";$config[' dbdriver '] = "MySQL";$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);

Or you can submit the database configuration information in DSN mode. DSN must be implemented in the following ways:

$dsn = ' dbdriver://username:[email protected]/database '; $this->load->database ($dsn);

When connecting with a DSN string, to override the configuration defaults, add the configuration variable to the query string.

$dsn = ' Dbdriver://username:[email protected]/database?char_set=utf8&dbcollat=utf8_general_ci& Cache_on=true&cachedir=/path/to/cache '; $this->load->database ($dsn);

When your Dbdriver value is mysqli for remote connection, remember to specify a parameter port for the remote MySQL port

Connecting multiple databases

If you need to connect more than one database at a time, you can do this in the following ways:

$DB 1 $this TRUE ); $DB 2 $this TRUE

Note: Changing the "Group_one" and "Group_two" for you specifies the group name of the connection attribute (or the array name of the connection array as mentioned above).

Returns a database object by setting the second argument of a function to True (Boolean).

When you use this method, you will use the object name to execute the action command instead of the User Wizard mode, that is, you will perform the database operation in the following way:

$DB 1->query ();
$DB 1->result ();
etc...

Instead of:

$this->db->query ();
$this->db->result ();
etc...

To connect multiple databases, first set the $db in config/database.php [' xxxxxx '] [' pconnect '] = FALSE; This is a problem caused by mysql_pconnect () and is not related to CI.

Reconnect/Keep Connection valid

When you are doing some heavy-weight PHP operations, such as working with pictures, if you exceed the idle timeout limit of the database server, you should consider using the reconnect () method to send a ping to the server before executing more queries. This allows the connection to be gracefully maintained or re-established.

$this->db->reconnect ();

To close a connection manually

Although CodeIgniter can intelligently manage and automatically shut down the database connection, you can still explicitly close it in the following way.

$this->db->close ();

Third, the database QuickStart example code

The following section will briefly explain how to use the database. For more detailed information, please read the separate introduction page for each function.

Initializing the Database class

The following code loads and initializes the database class according to your database configuration:

$this->load->database ();

Once loaded, you can use it anywhere like this:

Note: If all of your pages require the initialization of the database class, you can let it load automatically.

Multi-result standard query (object form)

  $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 result () function above returns an array of objects . Example: $row->title

Multi-result standard query (array form)

$query $this->db->query (' SELECT name, title, email from my_table '); foreach ($query as$row) {    echo$row[' title '];     Echo $row [' name '];     Echo $row [' email '];}

The result_array () function above returns an array with the subscript. Example: $row [' title ']

Test Query Results

If your query may not return results, we recommend that you first use the num_rows () function to test:

$query $this->db->query ("YOUR query"); if ($query->num_rows () > 0) {   foreach ($queryas$row    {      echo$row, title;       Echo $row, name;       Echo $row, body;   

Single-result standard query (object form)

$query $this->db->query (' SELECT name from my_table LIMIT 1 '); $row $query, Row (); Echo $row->name;

The row () function above returns an object . Example: $row->name

Single-result standard query (array form)

$query $this->db->query (' SELECT name from my_table LIMIT 1 '); $row $query-Row_array (); Echo $row [' Name '];

The row_array () function above returns an array . Example: $row [' name ']

Standard insertion (INSERT)

$sql = "INSERT into MyTable (title, name)        VALUES (".  $this->db->escape ($title). ",". $this->db->escape ($name). ")" ; $this->db->query ($sql); Echo $this

Quick Query

The Quick query class provides us with a way to get data quickly:

$query $this->db->get (' table_name '); foreach ($queryas$row) {    echo$row-title;}

The get () function above returns all results in the data table. The shortcut query class provides shortcut functions for all database operations.

Shortcut Insert (INSERT)

$data Array (               $title,               $name,               $date            ); $this $data ); // Produces:insert into MyTable (title, name, date) VALUES (' {$title} ', ' {$name} ', ' {$date} ')