Inserting, Updating and Deleting Database Rows with Code Igniter

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.

Introduction

Admittedly, using a third-party PHP framework for building web applications rapidly confronts a PHP programmer with several challenging tasks, such as installing its source files and folders in the testing web server, probably editing and configuring a bunch of initialization parameters, and dealing with its learning curve.

Although in certain cases, all of these tasks can be accomplished in a relatively painless way, the truth is that there are several PHP frameworks available nowadays on the web that are pretty difficult to set up, and certainly come packaged with many features that can be quite overwhelming, particularly for inexperienced developers.

Fortunately, this isn’t the case with Code Igniter (http://codeigniter.com), a compact and powerful PHP framework developed in PHP 4 that permits you to create full-featured web applications very quickly. Among other things, it features a friendly learning curve.

And now that I mentioned the term “friendly,” you may recall that during the preceding article of this series, I demonstrated how easy it is to perform conditional SELECT statements against a selected table with the Code Igniter’s database class. Indeed, this class not only allows you to run queries using a traditional approach; it also allows you to select, insert, update and delete database records by means of the active record pattern, which lets you avoid coding SQL clauses explicitly.

In the aforementioned article I developed a couple of hands-on examples aimed at demonstrating how to select a few database rows via the active record approach. However, as I said before, Code Igniter lets you perform inserts, updates, and deletion operations using this design pattern as well.

Therefore, in the next few lines I’ll be discussing these interesting topics, as always, by means of several code samples. So, are you ready to learn how to insert, update and delete database records with Code Igniter? Then don’t waste more time in preliminaries. Start reading now!

{mospagebreak title=Performing conditional SELECT statements with Code Igniter}

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.

{mospagebreak title=Inserting new rows into a selected MySQL table}

In order to demonstrate how to insert a new row into the previous “users” MySQL table via the active record pattern, I’m going to work with only a controller class. Eventually, I could define a couple of additional views for displaying either an error message or a confirmation web page, depending on the result of the insert operation.

However, this will be left momentarily as homework for you, in case you feel bored and want to have some fun coding PHP classes. So, returning to the topic, the simplest way to insert a new record into a selected table is by using a method of the database class, not surprisingly called “insert().”

The prototype for using this brand new method can be seen in the code sample below.

class Users extends Controller{

function Users(){

// load controller parent

parent::Controller();

// load database class and connect to MySQL

$this->load->database();

}

// insert new row into ‘users’ MySQL table

function index(){

$data=array(‘firstname’=>’Jane’,’lastname’=>’Smith’,’email’=> ‘jane@domain.com’);

$this->db->insert(‘users’,$data);

}

}

As shown before, the “insert()” method is so intuitive that it practically doesn’t require any additional explanations. Anyway, you can see above that I built a simple controller class, which utilizes this method, to add a new fictional user to a sample MySQL table.

The data to be inserted is passed to the method in the form of an associative array, along with the name of the table that will be updated. In addition, it’s worth mentioning that it’s possible to utilize an object instead of an array, but for a detailed explanation of how to accomplish this, please check the Code Igniter’s user manual.

So far, you have learned how to employ the “insert()” method that belongs to  Code Igniter’s database class to add a new record to a selected table. It’s feasible to achieve the same results by using another method called “set().”

A typical usage of this method is illustrated below.

class Users extends Controller{

function Users(){

// load controller parent

parent::Controller();

// load database class and connect to MySQL

$this->load->database();

}

// insert new row into ‘users’ MySQL table using the ‘set()’ method

function index(){

$this->db->set(‘firstname’,’Jane’);

$this->db->set(‘lastname’,’Smith’);

$this->db->set(‘email’,’jane@domain.com’);

$this->db->insert(‘users’,$data);

}

}

As you can see, inserting new database rows with the “set()” method requires a few extra lines of code, but you may find it a bit more intuitive than its counterpart, “insert().” Anyway, both of them are valid, so you should choose the one that best suits your needs.

At this point, I’ve shown you how to perform database insertions very easily with the active record pattern. The last thing I’m going to show you in this article will be how to update and delete records from a MySQL table by using the pattern in question.

To learn the complete details of how to perform these database operations with Code Igniter, please read the upcoming section. We’re almost done!

{mospagebreak title=Updating and deleting database records using the active record pattern}

True to form, updating and deleting database rows with the active record pattern is only a matter of using a couple of intuitive methods, called “update()” and “delete()” respectively. They can be included in a simple controller class, as I did before when explaining how to perform database insertions.

To dissipate any possible doubts about how to use these method, I coded two controllers. The first one updates an existing row of a sample MySQL table, and the second one deletes a specified record.

Here are the respective controllers, so you can examine them in detail:

class Users extends Controller{

function Users(){

// load controller parent

parent::Controller();

// load database class and connect to MySQL

$this->load->database();

}

// update row of ‘users’ MySQL table

function index(){

$data=array(‘firstname’=>’Jane’,’lastname’=>’Smith’,’email’=> ‘jane@domain.com’);

$this->db->where(‘id’,’3′);

$this->db->update(‘users’,$data);

}

}


class Users extends Controller{

function Users(){

// load controller parent

parent::Controller();

// load database class and connect to MySQL

$this->load->database();

}

// delete row from ‘users’ MySQL table

function index(){

$this->db->where(‘id’,2);

$this->db->delete(‘users’);

}

}

Undoubtedly, after studying the signature of the above controllers, you’ll have to agree with me that updating and deleting database rows with Code Igniter is an extremely simple process! In the first case, the controller uses the “update()” method to update a specific row in the sample MySQL table, while the second class simply deletes an existing record.

And with this final example, I’m finishing this brief introduction to inserting, updating and deleting database rows with Code Igniter. Don’t forget to consult its user manual for a more detailed explanation of each of the methods covered in this tutorial.

Final thoughts

In this seventh part of the series, I provided you with a bunch of examples aimed at demonstrating how to perform insertions, updates and deletions on a selected MySQL table by using Code Igniter’s database class. As you saw before, all of these tasks are very intuitive and easy to grasp too.

In the forthcoming chapter, I’ll be taking a look at the email class of this handy PHP framework, so you can learn how to use this class within the context of the MVC pattern. Don’t miss the next part!

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