Working with the Active Record Class in Code Igniter - Pulling database records with Code Igniter’s active record class (
Page 2 of 4 )
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.
