Working with the Active Record Class in Code Igniter - Selecting database rows that match a given condition (
Page 4 of 4 )
As you might have guessed, using Code Igniter’s database class for extracting from the “users” table only the database rows whose ID values are greater than 2 is a process very similar to the example developed in the previous section.
Of course, in this case I’m going to use the same model class. That being said, here is its signature:
class Users extends Model{
function Users(){
// call the Model constructor
parent::Model();
// load database class and connect to MySQL
$this->load->database();
}
function getAllUsers(){
$query=$this->db->get('users');
if($query->num_rows()>0){
// return result set as an associative array
return $query->result_array();
}
}
function getUsersWhere($field,$param){
$this->db->where($field,$param);
$query=$this->db->get('users');
// return result set as an associative array
return $query->result_array();
}
// get total number of users
function getNumUsers(){
return $this->db->count_all('users');
}
}
Obviously, the signature of the above model remains the same, because the class that’s actually responsible for performing the conditional SQL statement discussed before is the controller, right? Therefore, keeping in mind this concept, below I created such a class, which looks like this:
class Users extends Controller{
function Users(){
// load controller parent
parent::Controller();
// load 'Users' model
$this->load->model('Users');
}
function index(){
$data['users']=$this->Users->getUsersWhere('id >',2);
$data['numusers']=$this->Users->getNumUsers();
$data['title']='Displaying user data';
$data['header']='User List';
// load 'users_view' view
$this->load->view('users_view',$data);
}
}
As illustrated above, the previous “User” controller class has been provided with the ability to fetch, from the pertinent “users” MySQL table, all of the rows whose IDs are greater than 2. Naturally, performing a conditional SQL clause like this one is pretty trivial, but it demonstrates in a nutshell how to use the active record pattern with Code Igniter.
Finally, there’s one step that still remains undone. It consists merely of defining the view file that will display the values of these table rows on the browser. Here it is:
<html>
<head>
<title><?php echo $title;?></title>
</head>
<body>
<h1><?php echo $header;?></h1>
<ul>
<?php foreach($users as $user):?>
<li>
<p><?php echo 'Full Name: '.$user['firstname'].' '.$user['lastname'].' Email: '.$user['email'];?></p>
</li>
<?php endforeach;?>
</ul>
<p><?php echo 'Total number of users :'.$numusers;?></p>
</body>
</html>
Well, having created the above view file, and assuming that the model and the controller has been saved to their respective folders, if you test this sample application with your own web server, you should get the following output:
Full Name: Alejandro Gervasio Email: alejandro@domain.com
Full Name: John Doe Email: john@domain.com
Full Name: Susan Norton Email: susan@domain.com
Full Name: Marian Wilson Email: marian@domain.com
Total number of users :10
Definitely, this isn’t rocket science! Yet this practical example should give you a clear idea of how to execute conditionals SQL statements using the active record pattern. Besides, it’s worthwhile to clarify that Code Igniter’s database class comes equipped with many other methods that permit us to perform queries without having to write SQL statements. However, if you wish to examine a full reference of them, the best place to go is its official web site.
Final thoughts
In this sixth episode of the series, I provided you with a quick overview on selecting database records through the active record pattern. Undeniably, Code Igniter makes this process easy, meaning that you shouldn’t have major problems practicing the relevant techniques.
In the upcoming article, I’m going to finish explaining how to apply the active record pattern with Code Igniter, this time by discussing how to insert, update and delete database rows.
Therefore, now that you’re aware of the topics that will be covered in the next part, you won’t want to miss it!
