Working with the Active Record Class in Code Igniter - Performing conditional SELECT queries with the active record pattern (
Page 3 of 4 )
True to form, executing conditional SELECT queries by means of Code Igniter’s database class is a straightforward process that only requires using its intuitive “where()” method. As its name suggests, it can be used to run queries containing certain conditions, as you’d normally do when using a WHERE clause.
To demonstrate how the aforementioned method can be utilized within the context of a concrete example, again I’m going to use the sample “users” MySQL table that was created in the previous section. Based on its structure, below I redefined the corresponding model, so now it looks like this:
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');
}
}
As shown above, the “Users” model looks nearly identical to its previous incarnation. There is a brand new method, however, called “getUsersWhere()”, which allows us to fetch database rows that match a certain condition. Of course, it’s clear to see that this specific method hides a “WHERE” SQL clause behind its signature, but fortunately you don’t have to code it explicitly.
Now that you have seen how the above model class was built, please save it to Code Igniter’s /system/application/models/ folder as “users.php.” Done? Then it’s time to define a new controller class, which will be tasked with fetching all the users whose IDs are lesser than 5.
The signature of the controller class that performs this task is as following:
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 <',5);
$data['numusers']=$this->Users->getNumUsers();
$data['title']='Displaying user data';
$data['header']='User List';
// load 'users_view' view
$this->load->view('users_view',$data);
}
}
That was quite simple to code and read, wasn’t it? As you can see above, the “Users” controller will extract from the sample “users” MySQL table all the rows with an ID less than 5. This conditional SQL statement is executed by way of the following expression:
$data['users']=$this->Users->getUsersWhere('id <',5);
Now that I have shown you how the controller works, it’s time to save it to the /system/application/controllers/ folder as “users.php” and proceed to create the pertinent view file, which actually looks as simple as this:
<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>
Having now defined the above view file, and assuming that’s been saved to the /system/application/views/ folder, you can test this MySQL-driven application by pointing your browser to the following URL:
http://localhost/codeigniter/index.php/users/
If everything have been set up correctly, you should get an output similar to this:
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
Well, at this point you have hopefully grasped how to perform a simple conditional SQL statement by using the database class that comes included with Code Igniter. Therefore, the last thing that I’m going to teach you in this tutorial will be how to extract rows from the same “users” MySQL table whose IDs are greater than 2, in this manner completing this introduction to using the active record pattern to execute WHERE SQL clauses.
This topic will be discussed in detail in the section to come, so click on the link below and keep reading. I’ll be there, waiting for you.
