Working with the CodeIgniter Database Queries

CodeIgniter is a powerful PHP framework that simplifies the process of developing web applications. One of its key features is its built-in database functionality, which allows developers to perform various database operations effortlessly. In this article, we will explore how to work with database queries in CodeIgniter.

Setting up the Database

Before we start querying the database, we need to configure the database settings in CodeIgniter. Open the application/config/database.php file and provide the necessary details such as the database type, hostname, username, password, and database name. CodeIgniter supports a wide range of databases, including MySQL, PostgreSQL, SQLite, and more.

Once you have configured the database settings, you can start using CodeIgniter's database library to execute SQL queries and retrieve data from the database.

Query Builder Class

CodeIgniter provides a powerful Query Builder class that simplifies the process of building database queries. This class offers an intuitive and fluent interface to construct complex queries without directly writing raw SQL statements. It also helps prevent common security vulnerabilities, such as SQL injection attacks.

To use the Query Builder class, you need to load the database library by adding the following code to your controller:

$this->load->database();

Now, let's explore some common database operations using CodeIgniter's Query Builder class.

Inserting Data

To insert data into a database table, we can use the insert() method provided by the Query Builder class. This method accepts two parameters: the table name and an associative array that represents the data to be inserted.

Here's an example of inserting a new user into the users table:

$data = [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT)
];

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

Retrieving Data

To retrieve data from a database table, we can use the get() or get_where() methods. The get() method retrieves all rows from the specified table, while the get_where() method allows us to specify conditions for the query using an associative array.

Here's an example of retrieving all users from the users table:

$query = $this->db->get('users');
$result = $query->result();

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

Updating Data

To update data in a database table, we can use the update() method. This method accepts three parameters: the table name, an associative array representing the updated data, and an array of conditions.

Here's an example of updating a user's email address in the users table:

$data = [
    'email' => 'new_email@example.com'
];

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

Deleting Data

To delete data from a database table, we can use the delete() method. This method accepts two parameters: the table name and an array of conditions.

Here's an example of deleting a user from the users table:

$this->db->where('id', $user_id);
$this->db->delete('users');

Conclusion

CodeIgniter's database functionality makes working with databases in PHP a breeze. By utilizing the Query Builder class, you can easily perform common database operations without worrying about complex SQL statements or security vulnerabilities. This article covered some basic examples of working with the CodeIgniter database queries, but there is much more you can explore in the official CodeIgniter documentation. Happy coding!