How To Insert Data Into A Database And Display Database Records Using Codeigniter

Introduction

 
In this tutorial, I will show you step-by-step how to insert data into a database & how to display database records using CodeIgniter. 
 

Why use CodeIgniter?

 
For web application development, CodeIgniter is one of the best frameworks. CodeIgniter development is fast and reliable compared to other frameworks. For connecting to the database, CodeIgniter provides out of the box libraries and performs various operations like sending an email, uploading the file, etc...
 

Feature of CodeIgniter Framework

  1. CodeIgniter is based on the Model-View-Controller system
  2. CodeIgniter is a lightweight system 
  3. Form and Data Validation
  4. CodeIgniter is provide Sending email class, Attachment, HTML/TEXT email, multiple protocols, and more
  5. Data Encryption
  6. Benchmarking
  7. Localization
  8. Pagination
Let’s start the insertion steps.
 
Step 1
 
First, you need to create a table for insert data into the table. Use the below code to create a table. (Give your table name as per your requirement). The below code indicates that if the table already exists in the database, then remove/drop those tables and create a new table with the following fields.
  1. DROP TABLE IF EXISTS `tb_category`;    
  2. CREATE TABLE IF NOT EXISTS `tb_category` (    
  3.    `ct_id` int(3) NOT NULL AUTO_INCREMENT,    
  4.    `ct_name` varchar(20) NOT NULL,    
  5.    `ct_descritpion` text NOT NULL,    
  6.    `cr_date` date NOT NULL,    
  7.    PRIMARY KEY (`ct_id`)    
  8. ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;     
Step 2
 
In the 2nd step, you need to create a view file. Open the application/views/ and create a new view file category_form.php (Your View File Name). The below code is used for the user interface. It's the HTML file. You can also use bootstrap and CSS to create an attractive form. In form tag action=”your_controller_name/insert_function”.
  1. <!DOCTYPE html>  
  2. <html>  
  3.     <head>  
  4.         <title></title>  
  5.     </head>  
  6.     <body>  
  7.         <table>  
  8.             <form action="My_controller/insert_category" method="post">  
  9.                 <tr>  
  10.                     <td>Enter Category Name :</td>  
  11.                     <td>  
  12.                         <input type="text" name="category" id="category">  
  13.                         </td>  
  14.                     </tr>  
  15.                     <tr>  
  16.                         <td>Enter Description :</td>  
  17.                         <td>  
  18.                             <textarea name="descripiton" id="descripiton"></textarea>  
  19.                         </td>  
  20.                     </tr>  
  21.                     <tr>  
  22.                         <td>  
  23.                             <input type="submit" value="Submit">  
  24.                             </td>  
  25.                         </tr>  
  26.                     </form>  
  27.                 </table>  
  28.             </body>  
  29.         </html>     
Step 3
 
In the 3rd step, you need to create a controller file in the application/controllers/ directory and create a new controller called My_controller.php (Your controller name). Create methods in the controller class to perform CRUD operation.
 
The below code is used to retrieve data on the controller from the form using the post method. After that, all data are stored in an array and the array stored in one variable. After that, all those data are sent on the model file. 
 
“$insert_category = $this->Category_model->insert_category($data);” the meaning of this line / code is used for send all data on model file.
 
Header() is used for redirect on the display page or any other page. base_url is your route URL you have to define base_url in your application/config/ directory open config.php file put your project URL.
 
$config['base_url'] = 'http://localhost/InterView_Test/Practical_Task_3/';.
  1. <?php     
  2. /**  
  3.  *   
  4.  */    
  5. class My_controller extends CI_Controller{    
  6.       
  7.   function __construct()    
  8.   {    
  9.     parent::__construct();    
  10.     $this->load->helper('url');   /* This helper is used to load your URL */  
  11.     $this->load->model("Category_model");  /* This used to load model */  
  12.   }    
  13.     
  14.   public function index(){    
  15.     $this->load->view('category_form.php');    
  16.   }    
  17.     
  18.   public function insert_category(){    
  19.     $cr_date = date("Y/m/d");    
  20.     $data = array(    
  21.       'ct_name' => $this->input->post('category'),    
  22.       'ct_descritpion' => $this->input->post('descripiton'),    
  23.       'cr_date' => $cr_date,    
  24.     );    
  25.     $insert_category = $this->Category_model->insert_category($data);    
  26.     header("location:".base_url()."index.php/My_controller/display");    
  27.     
  28.     /* Note: base_url is your route URL you have to define base_url in your application/config/   directory open config.php file. Put your project URL   
  29.      $config['base_url'] = 'http://localhost/InterView_Test/Practical_Task_3/'; */    
  30.   }    
  31. }    
  32. ?>    
Step 4
 
In the 4th Step, you need to create a Model file. The model file is used for the applied queries. In application/models/ directory create a new model file called Category_model.php (Your Model File Name).
 
The below code is used to get all data’s in function parameter and those data are all passed in query.
 
Here “$this->db->insert” is the insertion query in CodeIgniter format (INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...)). 
  1. <?php     
  2. /**  
  3.  *   
  4.  */    
  5. class Category_model extends CI_Model    
  6. {    
  7.       
  8.   function __construct()    
  9.   {    
  10.      function __construct() {    
  11.       parent::__construct();    
  12.       $this->load->database();    
  13.     }    
  14.   }    
  15.     
  16.   public function insert_category($data){    
  17.     $result = $this->db->insert("`tb_category`",$data);    
  18.     return $result;    
  19.   }    
  20. }    
  21. ?>     
Let’s start on how to display database records using CodeIgniter?
 
Step 1
 
For displaying records from the database, you need to create a display method in the controller. Here, I create a method in the existing controller that is My_controller.php.
 
The below code is used to display database records. $this->Category_model->ger_category() This code is used to called get_category() from the model file and in the model file execute the select query and results are stored in the variable after that those variable return on the controller and on the controller those records are stored in a variable and all data is sent on view file using $this->load->view(). 
  1. public function display()    
  2. {    
  3.     $data['category']=$this->Category_model->ger_category();     
  4.     $this->load->view('display_category.php'$data);    
  5.     
  6.     /* Note : Here fetched records are stored in $data variable and it is pass on view file using $this->load->view() function */    
  7. }   
Step 2
 
In the 2nd step, you need to create 1 method in the model file. Here, I create a method in the existing model file that is Category_model.php.
 
The below code is used to execute a select query. In the get_category function $this->db->get() is used to execute select query in Codeigniter format. Here “SELECT * FROM table_name”. Fetched records are stored in the $result variable and then return on the controller. 
  1. public function ger_category()    
  2. {    
  3.     $result = $this->db->get("`tb_category`");    
  4.     return $result;    
  5.     
  6.     /* Note: Here fetched records are stored in result variable and return on   
  7.         controller function */     
  8.  
Step 3
 
In the 3rd step, you need to create a view file for display records. I will create display_category.php.
 
The below code is used to display database records in table format. You can also use a bootstrap table for a more attractive table. 
  1. <!DOCTYPE html>    
  2. <html>    
  3. <head>    
  4.   <title></title>    
  5. </head>    
  6. <body>    
  7.   <table border="1" align="center" cellspacing="5" cellpadding="5">    
  8.     <thead>    
  9.       <tr>    
  10.         <th>Category Name</th>    
  11.         <th>Category Description</th>    
  12.         <th>Today Date</th>    
  13.         <th colspan="2">Action</th>    
  14.       </tr>    
  15.     </thead>    
  16.     <tbody>    
  17.       <?php     
  18.       foreach ($category->result() as $row) { /* $category variable is define in controller */    
  19.       $datedate = date('Y-m-d', strtotime($row->cr_date));    
  20.       ?>    
  21.       <tr>    
  22.         <td><?php echo $row->ct_name; ?></td>    
  23.         <td><?php echo $row->ct_descritpion; ?></td>    
  24.         <td><?php echo $date; ?></td>    
  25.         <td><a href="<?php echo base_url()."index.php/My_controller/category_delete?id=$row->ct_id"; ?>">Delete</a></td>    
  26.         <td><a href="<?php echo base_url()."index.php/My_controller/category_edit?id=$row->ct_id"; ?>">Edit</a></td>    
  27.       </tr>    
  28.       <?php     
  29.       }    
  30.       ?>    
  31.     </tbody>    
  32.   </table>    
  33. </body>    
  34. </html>