CodeIgniter - Using Active Record for database interactions

Using Active Record in CodeIgniter provides a convenient and secure way to interact with databases by allowing you to build database queries using a fluent interface. Active Record abstracts away much of the SQL syntax and provides a more intuitive and readable way to perform database operations. Here's how you can use Active Record for database interactions in CodeIgniter:

1. Loading the Database Library:

• Before using Active Record, make sure the database library is loaded. If it's not autoloaded, you can load it in your controller or model.

 $this->load->database();

2. Performing Database Queries with Active Record:

Select Query:

 $query = $this->db->get('users'); // SELECT * FROM users
foreach ($query->result() as $row) {
    echo $row->username;
}

You can also add conditions and filters:

 $this->db->where('status', 'active');
$query = $this->db->get('users');

Insert Query:

$data = array(
    'username' => 'john',
    'email' => '[email protected]',
    'password' => 'password'
);
$this->db->insert('users', $data); // INSERT INTO users (username, email, password) VALUES ('john', '[email protected]', 'password')
 

Update Query:

 $data = array(
    'email' => '[email protected]'
);
$this->db->where('id', 1);
$this->db->update('users', $data); // UPDATE users SET email = '[email protected]' WHERE id = 1

Delete Query:

$this->db->where('id', 1);
$this->db->delete('users'); // DELETE FROM users WHERE id = 1
 

3. Method Chaining:

• Active Record allows method chaining, making it easy to build complex queries in a readable manner.

$this->db->select('username, email');
$this->db->from('users');
$this->db->where('status', 'active');
$query = $this->db->get();

4. Escaping Queries:

• Active Record automatically escapes queries to prevent SQL injection attacks. You don't need to manually escape values.

 $data = array(
    'username' => $this->db->escape($username),
    'email' => $this->db->escape($email)
);
$this->db->insert('users', $data);

5. Executing Raw SQL Queries:

• While Active Record is preferred for most queries, you can also execute raw SQL queries if necessary.

$query = $this->db->query('SELECT * FROM users');
foreach ($query->result() as $row) {
    echo $row->username;
}