CodeIgniter Beginner Tutorial 3– Playing with the Database

In this tutorial were going to play around some of the functions that are available in CodeIgniter that will allow us to manipulate the database ninja-style. Remember that you can always use the awesome codeIgniter user guide if you want to take a look at what other functions are available since I’m not going to tackle all of it in this tutorial. What I’m going to show is how to use some of those functions by implementing MVC.

And remember to check out the locally available user guide, it will be very helpful if you’re doing most of these offline. Which I also prefer since being connected to the internet tempts you to ask Google for everything.

 

Create the Model

First you need to create the Model which we will be working with. Nothing new here we just declared a new class for the mods model. If you don’t know what I’m doing then check out the first two tutorials on this codeIgniter beginner series.

class mods extends ci_Model{

}

Inserting Records

Inside the mods model create a function and call it insert_records(). In this function we specify a single parameter called $name. This argument for this parameter is going to be supplied in the controller in which we will load the mods model later on.

function insert_records($name){ $sql = "INSERT INTO testing SET name=?"; //query string //executes the query

//first argument->query string

//second argument, third argument,etc..->data bindings

$this->db->query($sql, $name); }

In this function were using something called the Query Bindings, its basically a safer way to execute queries on the database since the values which you supply to it are automatically escaped. This means that any malicious values supplied to queries won’t affect our database.

Next create a new controller and call it mods_controller.

class mods_controller extends ci_Controller{


}

Inside the mods_controller class create a function and call it inserts(). Inside the inserts() function we load the mods class. After the mods class has been loaded we can readily access its methods through the name of the class. And the method that were calling here is the insert_records() method, we just supplied a dummy name called blossom so that it will actually insert something on our table.

function inserts(){
	$this->load->model('mods'); //load the mods model class
	$this->mods->insert_records('blossom'); //call the insert_records() method
}

 

Updating Records

Updating records is pretty much the same with the inserting of records, but this time you’ll going to need a minimum of 2 arguments for your function. On the mods class create a new function called update_records, then supply 2 paramaters $name and $id. As usual we declare the sql query as a string. The only difference between the insert that we did earlier is that the second argument that you’re going to supply is actually an array composed of the values which will be supplied on the mods_controller controller.

function update_records($name, $id){
	$sql = "UPDATE testing SET name=? WHERE id=?";
	$this->db->query($sql, array($name, $id));
}

On the mods_controller all you have to do is to load up the mods model and call up the update_records() function. Remember to supply the values $id and $name. Supply a record which actually exist so that you can see if its really updating.

function updates(){
	$this->load->model('mods');
	$this->mods->update_records('jade', 2);
}

 

Deleting Records

Deleting records is still the same with inserting and updating records in the sense that were not using views for all of them. Though we can actually use views if we want to display the status of the query or something. Nothing new here, we just changed the sql statement to a delete query.

function delete_records($id){
	$sql = "DELETE FROM testing WHERE id=?";
	$this->db->query($sql, $id);
}

There’s also nothing new if we try to call the delete_records() function from the mods class. In here were trying to delete record with the id of 4 from our testing table.

function deletes(){
	$this->load->model('mods');
	$this->mods->delete_records(4);
}

 

Single Row Fetching

This time were going to use the Active Record Class to fetch a single row from our table.  First you have to create another function on the mods class and call it ar_getwhere() short for active record get where. The get_where() method accepts two arguments the first one is the name of the table, and the second is an array which stores the table field and value pair. In this case our table field is ‘id’ and the value is the argument which we will supply when we call the ar_getwhere() method.

function ar_getwhere($id){
	$where = array('id'=>$id);
	$query = $this->db->get_where('testing', $where);
	$row = $query->row();
		
	return $row->name;
}

If you want to include more fields in the where clause you can do so by adding more items on the $where array. In the example below we are including both the id field and the name field in our where clause.

$where = array('id'=>$id, 'name'=>$name);

This simply translates to the following query.

SELECT * FROM testing WHERE id='$id' AND name='$name'

Next we just have to call the function from our controller inside the mods_controller class.

function getname(){ $this->load->model('mods'); //load the mods class $row['name'] = $this->mods->ar_getwhere(1); //select * from testing where id is equal to 1 $this->load->view('mods_view', $row); //load the view in which we will display the name

//use the value returned from the query as the second argument }

And from mods_view we just simply echo out the name of the person with the id of 1.

<?php
echo $name; 
?>

 

Joins

You can just directly perform joins on your queries but the Active Record Class actually allows you to do this by using the join() function which takes 2 arguments, first is the table that you wish to join and the second is the common key between the table your selecting from and the table that you wish to join. Remember that you can use the join() function as many times as you would like but this ultimately depends on how many tables you wish to join together in a single query. After joining we just call the get() function. I can only guess what this does because I haven’t actually read about it. Maybe the get() function just puts the query and joins together in a single query. We then check if the number of rows returned is greater than 0. We won’t actually do anything if it returned 0 results. Next we just loop through the results and assign the rows returned to an array key.

function joins(){
	$this->db->select('name, clubname');
	$this->db->from('testing');
	$this->db->join('clubs', 'testing.id = clubs.person_id');
	$this->db->join('clubnames', 'clubs.club_id = clubnames.club_id');
	$query = $this->db->get();
	if($query->num_rows() > 0){
		foreach($query->result() as $v){
			$data['name'][] = $v->name;
			$data['clubname'][] = $v->clubname;
		}
	return $data;
	}
}

On the controller side of things we do what we have always done: load model, call function. The only thing that’s new in here is that we actually load a view called mods_view.

function joins_controller(){
	$this->load->model('mods');
	$data['info'] = $this->mods->joins();
	$this->load->view('mods_view', $data);
}

In the mods_view file we just loop through all the data stored in the $data array.

<table border="1"> <tr>   

<th>Student</th>   

<th>Club</th> </tr> <?php foreach($info['name'] as $k=>$v){ ?> <tr>   

<td><?php echo $v; ?></td>   

<td><?php echo $info['clubname'][$k]; ?></td> </tr> <?php } ?> </table>

 

Conclusion

That’s it for this tutorial. You’ve learned how to perform CRUD operations on your database using CodeIgniter. I hope you’re already getting comfortable on using the CodeIgniter user guide. It’s the ultimate reference believe me.

4 thoughts on “CodeIgniter Beginner Tutorial 3– Playing with the Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s