Learn Codeigniter PHP Framework : Part 8 – codeigniter database access using active record

Codeigniter implements the active record design pattern which allows developers to easily handle databases. It makes the database access layer simple to use and maintain. Active record represents the tables in a database as an object. Typically, each table is a class, and each single row becomes an object. All operations such as create, read, update, delete on the row are represented as methods in the class, which that object inherits from its class. Please note, Active record is different from an object relational mapper (ORM), which codeigniter does not support by default.

Developers have to update configuration settings before using databases. All configuration files for codeigniter are in system/application/config folder. Database configuration is in the database.php file under the config folder.

The major configuration settings in database.php are

$active_record = TRUE;
$active_group = “default”;
$db[‘default’][‘hostname’] = “localhost”;
$db[‘default’][‘username’] = “db_username”;
$db[‘default’][‘password’] = “db_password”;
$db[‘default’][‘database’] = “db_name”;
$db[‘default’][‘dbdriver’] = “mysql”;
$db[‘default’][‘dbprefix’] = “”;
$db[‘default’][‘pconnect’] = TRUE;
$db[‘default’][‘db_debug’] = TRUE;
$db[‘default’][‘cache_on’] = FALSE;
$db[‘default’][‘cachedir’] = “”;
$db[‘default’][‘char_set’] = “utf8”;
$db[‘default’][‘dbcollat’] = “utf8_general_ci”;

hostname – This is the location of the database, It is generally ‘localhost’ or an IP address.

username and password – The username and password of a database user with sufficient permissions.

database – The name of your database.

dbdriver – The type of database being used. Codeigniter offers support for MySQL, MySQLi, Postgre SQL, ODBC, and MS SQL.

The $active_group helps us setup databases for production, development and staging and switch between them easily by changing the group name corresponding to the first dimension of the $db variable.

The database library is the heart of the active record implementation and should be loaded manually/automatically before manipulating the database. 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();

The Database library in CodeIgniter allows developers to pass in simple SQL queries without the need to write database connection statements etc.

$sql = “select a.name, a.id
from employees a
where a.group_id = '5'
group by a.name”;
 
$res = $this->db->query($sql);

To loop over the result set of a query, two methods exist; namely result() or result_array() methods. result() allows to process the result set as an object. result_array() allows to process the result set as an array.

Example with result():

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

Example with result_array():

foreach ($Q- > result_array() as $row){
echo $row[‘name’];
echo $row[‘id’];
}

num_rows() gives a count of rows in a result set.

echo $res->num_rows();

The row() method allows us to retrieve data when the result set contains a single row.

$res = $this->db->query($sql);
$row= $res->row();
echo $row->id;
 
The row_array also helps us do the same.
 
<pre lang="php">
$res = $this->db->query($sql);
$row = $res->row_array();
echo $row[‘id’];

You can also pass insert, update, delete statements to the query() method.

$sql = “insert into employees (name,age) values (‘Mani’, 35)”;
$this->db->query($sql);

To retrieve all the database records from the specified table, we use the active record method named get()

$res = $this->db->get(‘employees’);

where employees is the table name.

Another way of running the query in active record is to use method chaining.

$this->db->select(‘id, name’);
$this->db->from(‘employees’);
$res = $this->db->get();

The first two statements use active record functions called select() and from() which correspond to the sql counterparts in a query.

We can pass an array of data to the insert() function and a table name for the insert statements to be run against. The array is an associative array and must have the key as the table column name as in the database.

$data = array(
‘name’ = > "mani",
‘age’ = > "25"
);
$this->db->insert(‘employees’, $data);