Performing conditional SELECT queries with the active record pattern - PHP

If you’re a PHP developer who’s searching for a third-party framework that lets you build full-blown web applications without having to deal with a long and hard learning curve, then this group of articles might be what you really need. Welcome to the sixth installment of the series entitled “Introducing the Code Igniter PHP Framework.” By using a hands-on approach, this series of articles walks you through the main features that come packaged with this friendly yet powerful development software that lets you quickly build robust PHP applications.

TABLE OF CONTENTS:

By: Alejandro Gervasio

Poor Best

Rating:

/ 6

September 17, 2008

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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>

</head>

<body>

<ul>

<?php foreach($users as $user):?>

<li>

</li>

<?php endforeach;?>

</ul>

</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.

Next: Selecting database rows that match a given condition >>