Working with the Active Record Class in Code Igniter

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.

Naturally, if you’ve already read all the tutorials that precede this one, then you’re probably familiar with utilizing some core classes that come bundled with Code Igniter. This framework can be used to perform all sorts of clever tasks, including the validation of HTML forms, working with MySQL, paging database records, and so forth.

Indeed, this PHP 4-based framework allows you to develop a wide range of programs, by using the model imposed by the Model-View-Controller pattern. This pattern makes it extremely convenient to separate application logic from visual presentation.

And now that I mentioned the MVC approach, surely you’ll recall that in the previous article of this series, I explained how to apply this design pattern to developing a web application whose main goal was validating the data entered into a sample online form.

As with any other web program developed with Code Igniter, this data validation application was composed of two primary modules. The first one was a controller class, which implemented all the logic required for checking the validity of the data entered on the mentioned HTML form.

The second module was comprised of two view files, which were used either to redisplay the form in question along with a bunch of error messages, or to print on screen a simple confirmation message, depending on the result of the validation process. Does all of this ring any bells for you? I bet it does!

Besides, as I mentioned earlier, Code Igniter comes equipped with a handy active record class that permits you to select, insert, update and delete database rows without having to explicitly code any SQL statements. Therefore, in the next few lines I’ll be creating some hands-on examples for you, aimed at demonstrating how to use this class to manipulate a few basic records stored on a MySQL table.

Are you ready to learn how to use the active record pattern within the Code Igniter’s context? Then let’s begin now!

{mospagebreak title=Pulling database records with Code Igniter’s active record class}

A good place to start showing you how to use the active record class that comes bundled with Code Igniter to manipulate MySQL rows, is with creating a sample table for testing purposes. In this case, I’m going to use the same “users” table that I used in previous tutorials, whose structure looks like this:



As you’ll certainly recall, the above MySQL table was populated with data about some fictional users (well, some are real, actually), so it is pretty useful for demonstrating how to handle its records via the active record pattern.

Therefore, I’m going to set up a basic example that fetches all of the rows from the previous table, by using first a model class, then the corresponding controller, and finally a view file.

That being explained, here’s the short signature of the model in question:


class Users extends Model{

function Users(){

// call the Model constructor

parent::Model();

// load database class and connect to MySQL

$this->load->database();

}

// get all users

function getUsers(){

$query=$this->db->get(‘users’);

if($query->num_rows()>0){

// 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 you can see, the constructor of the above “Users” model first loads the database class, which will be used to fetch all of the records contained into the “users” table. However, you should pay close attention to the way that its “getUsers()” and “getNumUsers()” methods have been implemented.

In this specific case, “getUsers()” behaves simply as a wrapper for the “get()” method that belongs to Code Igniter’s database class, which as its name implies, comes in handy for retrieving all of the rows from a selected table without having to specify explicitly any SELECT statement.

Finally, the “getNumUsers()” method will return the total number of rows contained in a selected table to client code. It’s that simple, really.

Having studied in depth the structure of the “Users” model class, do you realize how easy it is to use the active record pattern to pull out a few database records from a MySQL table? I guess you do! So, now that you have grasped how the previous model does its thing, you should save it to the /system/application/models/ folder as “users.php.”

Okay, it’s time to create the corresponding controller class, which naturally will use the model’s methods to extract the records of the “users” MySQL table. Here’s how this brand new class looks:

class Users extends Controller{

function Users(){

// load controller parent

parent::Controller();

// load ‘Users’ model

$this->load->model(‘Users’);

}

function index(){

$data['users']=$this->Users->getAllUsers();

$data['numusers']=$this->Users->getNumUsers();

$data['title']=’Displaying user data’;

$data['header']=’User List’;

// load ‘users_view’ view

$this->load->view(‘users_view’,$data);

}

}


At this moment, undoubtedly things are getting more interesting, since the above controller class performs two crucial tasks, in the following order: first, it loads the model class, and then implements the “index()” methods in such a way that it permits it to use its methods to fetch all of the rows of the previous “users” MySQL table. Second, this data is embedded directly into a view file called “users_view.php” for display purposes.

Definitely, the major advantage in using the active record approach is that it wasn’t necessary to get our hands dirty coding SQL statements. Pretty good, right?

Oops, before I forget, you should save the controller class to the Code Igniter /system/application/controllers/ folder as “users.php.” In this way it can be called by typing the following URL into the browser’s address field:

http://localhost/codeigniter/index.php/users/

However, before you do that, it’s necessary to create the view file that displays user-related data on screen. Thus, here’s the definition of this simple file:

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


Definitely, the above view is pretty easy to grasp, so in this case I’m not going to spend a long time explaining how it functions. In simple terms, it displays the data on the users stored in the sample MySQL table, along with the total number of rows.

Below I included the output generated by the previous view file. Here it is:


User List


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


Full Name: Mary Smith Email: mary@domain.com


Full Name: Amanda Bears Email: amanda@domain.com


Full Name: Jodie Foster Email: jodie@domain.com


Full Name: Laura Linney Email: laura@domain.com


Full Name: Alice Dern Email: alice@domain.com


Full Name: Jennifer Aniston Email: jennifer@domain.com


Total number of users :10


In addition, you should save this view file to the /system/application/views/ folder, so it can be found directly by the controller.

So far, so good. At this point, you have learned how to use the active record pattern with Code Igniter to fetch a few database rows from a MySQL table. As you saw for yourself, accomplishing this task didn’t require coding any SQL SELECT statements, since the whole operation was handled behind the scenes by the corresponding database class.

In the upcoming section, I’ll be coding for you a brand new hands-on example, aimed at illustrating how to perform conditional SELECT queries by means of Code Igniter’s active record pattern class.

Want to see how this will be done? Then click on the below link and read the next few lines.

{mospagebreak title=Performing conditional SELECT queries with the active record pattern}

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.

{mospagebreak title=Selecting database rows that match a given condition}

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!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan