Home arrow PHP arrow Page 3 - Working with the Active Record Class in Code Igniter

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:
  1. Working with the Active Record Class in Code Igniter
  2. Pulling database records with Code Igniter’s active record class
  3. Performing conditional SELECT queries with the active record pattern
  4. Selecting database rows that match a given condition
By: Alejandro Gervasio
Rating: starstarstarstarstar / 6
September 17, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: