Inserting, Updating and Deleting Database Rows with Code Igniter - Performing conditional SELECT statements with Code Igniter (
Page 2 of 4 )
Actually, before I proceed to demonstrate how to perform inserts, updates and deletions with the active record pattern, I’d like to reintroduce a pair of practical examples developed in the previous tutorial. They showed how to run conditional SELECT statements via this pattern.
Each of these examples was comprised naturally of three source files: a model, a controller class, and finally a view file. All of them worked with a sample “users” MySQL table, whose structure was similar to the following:
Okay, now that you remember how the table looked, here’s the complete source code corresponding to the first example. It fetches all of the users whose IDs are less than 5. Take a look at it, please:
(definition for ‘users.php’ file - located at /system/application/models/ folder)
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');
}
}
(definition for ‘users.php’ file - located at /system/application/controllers/ folder)
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);
}
}
(definition for ‘users_view.php’ file - located at /system/application/views/ folder)
<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>
As you can see, the three source files listed above are the building blocks of a basic MySQL-driven application. Its goal is to retrieve all of the rows included in the previous “users” table whose ID values are less than 5. Obviously, this is only a primitive example, but it shows how to use the active record pattern to perform conditional SELECT statements with Code Igniter.
In a similar fashion, the following sample application fetches all the users whose IDs are greater than 2. Here are its corresponding source files.
(definition for ‘users.php’ file - located at /system/application/models/ folder)
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');
}
}
(definition for ‘users.php’ file - located at /system/application/controllers/ folder)
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);
}
}
(definition for ‘users_view.php’ file - located at /system/application/views/ folder)
<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 listed the full source code for the two previous web applications, I should assume that running conditional SQL queries using the active record pattern is now a familiar topic to you, right? Thus, it’s time to learn how to use this pattern to perform inserts, updates and deletions against a selected database table.
As you may guess, this subject will be covered in depth in the following section. Please read the next few lines.
/ 7 
