How to use the database query builder (Query Builder) in the CodeIgniter framework

How to use the database query builder (Query Builder) in the CodeIgniter framework

Introduction:
CodeIgniter is a lightweight PHP framework that provides many powerful tools and libraries. Convenient for developers to develop web applications. One of the most impressive features is the Query Builder, which provides a concise and powerful way to build and execute database queries. This article will introduce how to use the database query builder in the CodeIgniter framework and provide some code examples.

Text:

1. Initialize database configuration
   Before starting to use the database query builder, we need to configure the database connection first. In the CodeIgniter configuration file (config/database.php), we can set the database drive, host, user name, password and other information. Here is an example configuration:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'your_username',
    'password' => 'your_password',
    'database' => 'your_database',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

2. Querying Data
   Querying is easy using the database query builder. The following is a basic query example:

$this->db->select('name, email');
$query = $this->db->get('users');
$result = $query->result();

foreach ($result as $row) {
    echo $row->name;
    echo $row->email;
}

In the above example, we first call the select() method to specify the columns we need to return. Then use the get() method to execute the query and obtain the result set. Finally, we can use the result() method to convert the result set to an array or object. We can then loop through the resulting array and access the columns of each row.

In addition to basic queries, the database query builder also provides many other methods to perform complex query operations, such as adding filter conditions, sorting, grouping, joining, etc. Here are some examples of commonly used methods:

• Add conditions:

$this->db->where('id >=', 100);
$this->db->where('name !=', 'John');

• Sort results:

$this->db->order_by('name', 'DESC');

• Connect Table:

$this->db->join('orders', 'users.id = orders.user_id');

The above are just some examples. You can flexibly use these methods to build complex query statements according to actual needs.

3. Insert data
   In addition to queries, the database query builder can also be used to insert and update data. Here is an example of inserting data:

$data = array(
    'name' => 'John',
    'email' => 'john@example.com',
    'phone' => '1234567890'
);

$this->db->insert('users', $data);

In the above example, we first create an associative array containing the data to be inserted. Then use the insert() method to insert data into the table named "users".

4. Update data
   The following is an example of updating data:

$data = array(
    'name' => 'John Doe',
    'email' => 'john.doe@example.com',
    'phone' => '9876543210'
);

$this->db->where('id', 1);
$this->db->update('users', $data);

In the above example, we first create an association that contains the data to be updated array. Then use the where() method to specify the rows to be updated, and the update() method to update the table.

Summary:
CodeIgniter’s database query builder is a powerful and flexible tool that can help us easily build and execute database queries. This article provides sample code for initializing database configuration, querying data, inserting data, and updating data, hoping to help you make better use of this feature. Using the database query builder, you can manage and operate your database more efficiently and avoid the risks of hard coding and SQL injection.

The above is the detailed content of How to use the database query builder (Query Builder) in the CodeIgniter framework. For more information, please follow other related articles on the PHP Chinese website!