---

Like some other system, we have to interface with the database all the time and CodeIgniter makes this activity simple for us. It gives rich arrangement of functionalities to cooperate with database.

In this segment, we will see how the CRUD (Create, Read, Update, Delete) capacities work with CodeIgniter. We will utilize stud table to choose, update, erase, and embed the information in stud table.

Table Name: stud

roll_no int(11)

name varchar(30)

Connecting to a Database

We can interface with database in the accompanying two manner −

• Automatic Connecting − Automatic association should be possible by utilizing the record application/config/autoload.php. Programmed association will stack the database for every single page. We simply need to include the database library as appeared underneath −

$autoload['libraries'] = array(‘database’);

• Manual Connecting−If you need database network for just a portion of the pages, at that point we can go for manual associating. We can interface with database physically by including the accompanying line in any class.

$this->load->database();

Here, we are not passing any contention since everything is set in the database config document application/config/database.php

Inserting a Record

To embed a record in the database, the addition() work is utilized as appeared in the accompanying table −

Language structure

insert([$table = ''[, $set = NULL[, $escape = NULL]]])

Parameters

$table (string) − Table name

$set (exhibit) − An affiliated cluster of field/esteem sets

$escape (bool) − Whether to get away from esteems and identifiers

Returns

Valid on progress, FALSE on disappointment

Bring Type back

bool

The accompanying model tells the best way to embed a record in stud table. The $data is a cluster where we have set the information and to embed this information to the table stud, we simply need to pass this exhibit to the supplement work in the second contention.

$data = array( 
   'roll_no' => ‘1’, 
   'name' => ‘Virat’ 
); 

$this->db->insert("stud", $data);

Updating a Record

To refresh a record in the database, the update() work is utilized alongside set() and where() works as appeared in the tables underneath. The set() capacity will set the information to be refreshed.

Punctuation

set($key[, $value = ''[, $escape = NULL]])

Parameters

$key (blended) − Field name, or a variety of field/esteem sets

$value (string) − Field esteem, if $key is a solitary field

$escape (bool) − Whether to get away from esteems and identifiers

Returns

CI_DB_query_builder occurrence (strategy affixing)

Bring Type back

CI_DB_query_builder

The where() capacity will choose which record to refresh.

Linguistic structure

where($key[, $value = NULL[, $escape = NULL]])

Parameters

$key (blended) − Name of field to analyze, or affiliated exhibit

$value (blended) − If a solitary key, contrasted with this worth

$escape (bool) − Whether to get away from esteems and identifiers

Returns

DB_query_builder case

Bring Type back

object

At long last, the update() capacity will refresh information in the database.

Sentence structure

update([$table = ''[, $set = NULL[, $where = NULL[, $limit = NULL]]]])

Parameters

$table (string) − Table name

$set (exhibit) − A cooperative cluster of field/esteem sets

$where (string) − The WHERE provision

$limit (int) − The LIMIT provision

Returns

Valid on progress, FALSE on disappointment

Bring Type back

bool

$data = array( 
   'roll_no' => ‘1’, 
   'name' => ‘Virat’ 
); 

$this->db->set($data); 
$this->db->where("roll_no", ‘1’); 
$this->db->update("stud", $data);

Deleting a Record

To erase a record in the database, the erase() work is utilized as appeared in the accompanying table −

Linguistic structure

delete([$table = ''[, $where = ''[, $limit = NULL[, $reset_data = TRUE]]]])

Parameters

$table (blended) − The table(s) to erase from; string or cluster

$where (string) − The WHERE condition

$limit (int) − The LIMIT condition

$reset_data (bool) − TRUE to reset the inquiry "express" provision

Returns

CI_DB_query_builder occurrence (technique affixing) or FALSE on disappointment

Bring Type back

blended

Utilize the accompanying code to erase a record in the stud table. The primary contention shows the name of the table to erase record and the subsequent contention chooses which record to erase.

$this->db->delete("stud", "roll_no = 1");

Selecting a Record

To choose a record in the database, the get work is utilized, as appeared in the accompanying table −

Grammar

get([$table = ''[, $limit = NULL[, $offset = NULL]]])

Parameters

$table (string) − The table to question exhibit

$limit (int) − The LIMIT provision

$offset (int) − The OFFSET provision

Returns

CI_DB_result example (technique tying)

Bring Type back

CI_DB_result

Utilize the accompanying code to get all the records from the database. The main explanation brings all the records from "stud" table and returns the article, which will be put away in $query object. The subsequent proclamation calls the outcome() work with $query item to get all the records as exhibit.

$query = $this->db->get("stud"); 
$data['records'] = $query->result();

Closing a Connection

Database connection can be closed manually, by executing the following code −

$this->db->close(); 

Example

Create a controller class calledStud_controller.phpand save it atapplication/controller/Stud_controller.php

Here is a complete example, wherein all of the above-mentioned operations are performed. Before executing the following example, create a database and table as instructed at the starting of this chapter and make necessary changes in the database config file stored atapplication/config/database.php

<?php 
   class Stud_controller extends CI_Controller {
	
      function __construct() { 
         parent::__construct(); 
         $this->load->helper('url'); 
         $this->load->database(); 
      } 
  
      public function index() { 
         $query = $this->db->get("stud"); 
         $data['records'] = $query->result(); 
			
         $this->load->helper('url'); 
         $this->load->view('Stud_view',$data); 
      } 
  
      public function add_student_view() { 
         $this->load->helper('form'); 
         $this->load->view('Stud_add'); 
      } 
  
      public function add_student() { 
         $this->load->model('Stud_Model');
			
         $data = array( 
            'roll_no' => $this->input->post('roll_no'), 
            'name' => $this->input->post('name') 
         ); 
			
         $this->Stud_Model->insert($data); 
   
         $query = $this->db->get("stud"); 
         $data['records'] = $query->result(); 
         $this->load->view('Stud_view',$data); 
      } 
  
      public function update_student_view() { 
         $this->load->helper('form'); 
         $roll_no = $this->uri->segment('3'); 
         $query = $this->db->get_where("stud",array("roll_no"=>$roll_no));
         $data['records'] = $query->result(); 
         $data['old_roll_no'] = $roll_no; 
         $this->load->view('Stud_edit',$data); 
      } 
  
      public function update_student(){ 
         $this->load->model('Stud_Model');
			
         $data = array( 
            'roll_no' => $this->input->post('roll_no'), 
            'name' => $this->input->post('name') 
         ); 
			
         $old_roll_no = $this->input->post('old_roll_no'); 
         $this->Stud_Model->update($data,$old_roll_no); 
			
         $query = $this->db->get("stud"); 
         $data['records'] = $query->result(); 
         $this->load->view('Stud_view',$data); 
      } 
  
      public function delete_student() { 
         $this->load->model('Stud_Model'); 
         $roll_no = $this->uri->segment('3'); 
         $this->Stud_Model->delete($roll_no); 
   
         $query = $this->db->get("stud"); 
         $data['records'] = $query->result(); 
         $this->load->view('Stud_view',$data); 
      } 
   } 
?>

Create a model class calledStud_Model.phpand save it inapplication/models/Stud_Model.php

<?php 
   class Stud_Model extends CI_Model {
	
      function __construct() { 
         parent::__construct(); 
      } 
   
      public function insert($data) { 
         if ($this->db->insert("stud", $data)) { 
            return true; 
         } 
      } 
   
      public function delete($roll_no) { 
         if ($this->db->delete("stud", "roll_no = ".$roll_no)) { 
            return true; 
         } 
      } 
   
      public function update($data,$old_roll_no) { 
         $this->db->set($data); 
         $this->db->where("roll_no", $old_roll_no); 
         $this->db->update("stud", $data); 
      } 
   } 
?> 

Create a view file calledStud_add.phpand save it inapplication/views/Stud_add.php

<!DOCTYPE html> 
<html lang = "en">
 
   <head> 
      <meta charset = "utf-8"> 
      <title>Students Example</title> 
   </head> 
   <body> 
         <?php 
            echo form_open('Stud_controller/add_student');
            echo form_label('Roll No.'); 
            echo form_input(array('id'=>'roll_no','name'=>'roll_no')); 
            echo "<br/>"; 
			
            echo form_label('Name'); 
            echo form_input(array('id'=>'name','name'=>'name')); 
            echo "<br/>"; 
			
            echo form_submit(array('id'=>'submit','value'=>'Add')); 
            echo form_close(); 
         ?> 
   </body>
</html>

Create a view file calledStud_edit.phpand save it inapplication/views/Stud_edit.php

<!DOCTYPE html> 
<html lang = "en">
 
   <head> 
      <meta charset = "utf-8"> 
      <title>Students Example</title> 
   </head> 
	
   <body> 
      <form method = "" action = "">
		
         <?php 
            echo form_open('Stud_controller/update_student'); 
            echo form_hidden('old_roll_no',$old_roll_no); 
            echo form_label('Roll No.'); 
            echo form_input(array('id'?'roll_no',
               'name'?'roll_no','value'?$records[0]?roll_no)); 
            echo "
            "; 

            echo form_label('Name'); 
            echo form_input(array('id'?'name','name'?'name',
               'value'?$records[0]?name)); 
            echo "
            "; 

            echo form_submit(array('id'?'sub mit','value'?'Edit')); 
            echo form_close();
         ?> 
			
      </form> 
   </body>
	
</html>

Create a view file calledStud_view.phpand save it inapplication/views/Stud_view.php

<!DOCTYPE html> 
<html lang = "en">
 
   <head> 
      <meta charset = "utf-8"> 
      <title>Students Example</title> 
   </head>
	
   <body> 
      <a href = "<?php echo base_url(); ?>
         index.php/stud/add_view">Add</a>
		
      <table border = "1"> 
         <?php 
            $i = 1; 
            echo "<tr>"; 
            echo "<td>Sr#</td>"; 
            echo "<td>Roll No.</td>"; 
            echo "<td>Name</td>"; 
            echo "<td>Edit</td>"; 
            echo "<td>Delete</td>"; 
            echo "<tr>"; 
				
            foreach($records as $r) { 
               echo "<tr>"; 
               echo "<td>".$i++."</td>"; 
               echo "<td>".$r->roll_no."</td>"; 
               echo "<td>".$r->name."</td>"; 
               echo "<td><a href = '".base_url()."index.php/stud/edit/"
                  .$r->roll_no."'>Edit</a></td>"; 
               echo "<td><a href = '".base_url()."index.php/stud/delete/"
                  .$r->roll_no."'>Delete</a></td>"; 
               echo "<tr>"; 
            } 
         ?>
      </table> 
		
   </body>
	
</html>

Make the following change in the route file atapplication/config/routes.phpand add the following line at the end of file.

$route['stud'] = "Stud_controller"; 
$route['stud/add'] = 'Stud_controller/add_student'; 
$route['stud/add_view'] = 'Stud_controller/add_student_view'; 
$route['stud/edit/(\d+)'] = 'Stud_controller/update_student_view/$1'; 
$route['stud/delete/(\d+)'] = 'Stud_controller/delete_student/$1';

Now, let us execute this example by visiting the following URL in the browser. Replace the yoursite.com with your URL.

http://yoursite.com/index.php/stud