Home arrow PHP arrow Page 2 - Inserting, Updating and Deleting Database Rows with Code Igniter

Performing conditional SELECT statements with Code Igniter - PHP

You've seen some of the things you can accomplish with the Code Igniter PHP framework in earlier parts of this series. In this seventh part of the series, you will learn, through copious examples, how to perform insertions, updates and deletions on a selected MySQL table by using Code Igniter’s database class. You'll see that these tasks are very intuitive and easy to grasp, thanks to Code Igniter's friendly learning curve.

TABLE OF CONTENTS:
  1. Inserting, Updating and Deleting Database Rows with Code Igniter
  2. Performing conditional SELECT statements with Code Igniter
  3. Inserting new rows into a selected MySQL table
  4. Updating and deleting database records using the active record pattern
By: Alejandro Gervasio
Rating: starstarstarstarstar / 23
September 24, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



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