Selecting database rows that match a given condition - 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

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>

</head>

<body>

<ul>

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

<li>

</li>

<?php endforeach;?>

</ul>

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