Insert Fetch Update Delete Mysql data in Codeigniter

Insert Data into Mysql Table in Codeigniter

Hello friends this is my new codeigniter series video, in this video we are going to learn some advance concept of codeigniter, in this video we will discuss how to insert data into mysql database table in codeigniter framework. In previous video of codeigniter tutorial we have show some basic introduction about codeigniter framework, what is model view controller in codeigniter framework and how to remove index dot php file from url. This all things we have discuss in my previous codeigniter framework, now in this video we will learn how can we insert data into Mysql database table by using Codeigniter framework. All you know codeigniter framework is purely based on model view controller work flow, for example here we want to insert data into database table, so user will fill up the form which we have define under view file, when user will submit the form the request is send to controllers, then controllers will send this request to models and then after lastly models will insert data into database. So this framework is purely based on models views controllers work flow. Now we have lets us learn how to insert data into mysql database table, so for this first this is my testing database and here we have one table user with three column like id, first name and last name. We will insert data into this table by using codeigniter framework.

Select or Fetch Mysql Table Data in Codeigniter

Hello friends in this video tutorial we are going to learn how to fetch or select data from mysql database table in codeigniter framework, in previous video we have show how can we insert data into mysql table in codeigniter framework. In this video tutorial we will fetch data from mysql table and display that data on web page by using functionality of models view controllers of codeigniter framework.

Delete or Remove Data from Mysql Table in Codeigniter

Hello friends in this video we are going to learn to how to delete data from mysql database table in codeigniter framework, in previous video we have discuss how to fetch data from mysql database table and display that data on web page in codeigniter framework. In this video we will add one more table column and in that column we will add delete link, when user click on that link, then it will ask you are you sure you want to delete this data, if user click on ok button then after data will be deleted but suppose it click on cancel button then no action we be perform and data will not delete from table.

Update or Edit Mysql Table Data in Codeigniter

Hello friends in this codeigniter video series we are going to discuss how to update mysql table data in codeigniter framework. In previous video we have discussed how to delete mysql data in codeingiter framework. In this video we will add one more table column in main view dot php page for update data, in that link we will store user id when we have click on that link then that user data will be appear under html form and after we can edit user data and submit form then after we can update mysql table data in codeigniter framework.

Source Code

Database

 --  
 -- Table structure for table `tbl_user`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `first_name` varchar(200) NOT NULL,  
  `last_name` varchar(200) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ;  
 --  
 -- Dumping data for table `tbl_user`  
 --  
 INSERT INTO `tbl_user` (`id`, `first_name`, `last_name`) VALUES  
 (91, 'Harold', 'Jones'),  
 (89, 'Christine', 'Smith'),  
 (88, 'Marker', 'Angela'),  
 (87, 'Romeo', 'Mary'),  
 (86, 'Smith', 'John');  

Controller - main.php

 <?php  
 defined('BASEPATH') OR exit('No direct script access allowed');  
 class Main extends CI_Controller {  
      //functions  
      public function index(){  
           $this->load->model("main_model");  
           $data["fetch_data"] = $this->main_model->fetch_data();  
           //$this->load->view("main_view");  
           $this->load->view("main_view", $data);  
      }  
      public function form_validation()  
      {  
           //echo 'OK';  
           $this->load->library('form_validation');  
           $this->form_validation->set_rules("first_name", "First Name", 'required|alpha');  
           $this->form_validation->set_rules("last_name", "Last Name", 'required|alpha');  
           if($this->form_validation->run())  
           {  
                //true  
                $this->load->model("main_model");  
                $data = array(  
                     "first_name"     =>$this->input->post("first_name"),  
                     "last_name"          =>$this->input->post("last_name")  
                );  
                if($this->input->post("update"))  
                {  
                     $this->main_model->update_data($data, $this->input->post("hidden_id"));  
                     redirect(base_url() . "main/updated");  
                }  
                if($this->input->post("insert"))  
                {  
                     $this->main_model->insert_data($data);  
                     redirect(base_url() . "main/inserted");  
                }  
           }  
           else  
           {  
                //false  
                $this->index();  
           }  
      }  
      public function inserted()  
      {  
           $this->index();  
      }  
      public function delete_data(){  
           $id = $this->uri->segment(3);  
           $this->load->model("main_model");  
           $this->main_model->delete_data($id);  
           redirect(base_url() . "main/deleted");  
      }  
      public function deleted()  
      {  
           $this->index();  
      }  
      public function update_data(){  
           $user_id = $this->uri->segment(3);  
           $this->load->model("main_model");  
           $data["user_data"] = $this->main_model->fetch_single_data($user_id);  
           $data["fetch_data"] = $this->main_model->fetch_data();  
           $this->load->view("main_view", $data);  
      }  
      public function updated()  
      {  
           $this->index();  
      }  
 }  

Models - main_model.php

 <?php  
 class Main_model extends CI_Model  
 {  
      function test_main()  
      {  
           echo "This is model function";  
      }  
      function insert_data($data)  
      {  
           $this->db->insert("tbl_user", $data);  
      }  
      function fetch_data()  
      {  
           //$query = $this->db->get("tbl_user");  
           //select * from tbl_user  
           //$query = $this->db->query("SELECT * FROM tbl_user ORDER BY id DESC");  
           $this->db->select("*");  
           $this->db->from("tbl_user");  
           $query = $this->db->get();  
           return $query;  
      }  
      function delete_data($id){  
           $this->db->where("id", $id);  
           $this->db->delete("tbl_user");  
           //DELETE FROM tbl_user WHERE id = $id  
      }  
      function fetch_single_data($id)  
      {  
           $this->db->where("id", $id);  
           $query = $this->db->get("tbl_user");  
           return $query;  
           //Select * FROM tbl_user where id = '$id'  
      }  
      function update_data($data, $id)  
      {  
           $this->db->where("id", $id);  
           $this->db->update("tbl_user", $data);  
           //UPDATE tbl_user SET first_name = '$first_name', last_name = '$last_name' WHERE id = '$id'  
      }  
 }  

Views - main_view.php

 <html>  
 <head>  
   <title>Insert Update Delete Data using Codeigniter</title>  
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />  
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
 </head>  
 <body>  
 <div class="container">  
      <br /><br /><br />  
      <h3 align="center">Insert Update Delete Data using Codeigniter</h3><br />  
      <form method="post" action="<?php echo base_url()?>main/form_validation">  
           <?php  
           if($this->uri->segment(2) == "inserted")  
           {  
           //base url - http://localhost/tutorial/codeigniter  
           //redirect url - http://localhost/tutorial/codeigniter/main/inserted  
                //main - segment(1)  
                //inserted - segment(2)  
                echo '<p class="text-success">Data Inserted</p>';  
           }  
           if($this->uri->segment(2) == "updated")  
           {  
                echo '<p class="text-success">Data Updated</p>';  
           }  
           ?>  
           <?php  
           if(isset($user_data))  
           {  
                foreach($user_data->result() as $row)  
                {  
           ?>  
           <div class="form-group">  
                <label>Enter First Name</label>  
                <input type="text" name="first_name" value="<?php echo $row->first_name; ?>" class="form-control" />  
                <span class="text-danger"><?php echo form_error("first_name"); ?></span>  
           </div>  
           <div class="form-group">  
                <label>Enter Last Name</label>  
                <input type="text" name="last_name" value="<?php echo $row->last_name; ?>" class="form-control" />  
                <span class="text-danger"><?php echo form_error("last_name"); ?></span>  
           </div>  
           <div class="form-group">  
                <input type="hidden" name="hidden_id" value="<?php echo $row->id; ?>" />  
                <input type="submit" name="update" value="Update" class="btn btn-info" />  
           </div>       
           <?php       
                }  
           }  
           else  
           {  
           ?>  
           <div class="form-group">  
                <label>Enter First Name</label>  
                <input type="text" name="first_name" class="form-control" />  
                <span class="text-danger"><?php echo form_error("first_name"); ?></span>  
           </div>  
           <div class="form-group">  
                <label>Enter Last Name</label>  
                <input type="text" name="last_name" class="form-control" />  
                <span class="text-danger"><?php echo form_error("last_name"); ?></span>  
           </div>  
           <div class="form-group">  
                <input type="submit" name="insert" value="Insert" class="btn btn-info" />  
           </div>       
           <?php  
           }  
           ?>  
      </form>  
      <br /><br />  
      <h3>Fetch Data from Table using Codeigniter</h3><br />  
      <div class="table-responsive">  
           <table class="table table-bordered">  
                <tr>  
                     <th>ID</th>  
                     <th>First Name</th>  
                     <th>Last Name</th>  
                     <th>Delete</th>  
                     <th>Update</th>  
                </tr>  
           <?php  
           if($fetch_data->num_rows() > 0)  
           {  
                foreach($fetch_data->result() as $row)  
                {  
           ?>  
                <tr>  
                     <td><?php echo $row->id; ?></td>  
                     <td><?php echo $row->first_name; ?></td>  
                     <td><?php echo $row->last_name; ?></td>  
                     <td><a href="#" class="delete_data" id="<?php echo $row->id; ?>">Delete</a></td>  
                     <td><a href="<?php echo base_url(); ?>main/update_data/<?php echo $row->id; ?>">Edit</a></td>  
                </tr>  
           <?php       
                }  
           }  
           else  
           {  
           ?>  
                <tr>  
                     <td colspan="5">No Data Found</td>  
                </tr>  
           <?php  
           }  
           ?>  
           </table>  
      </div>  
      <script>  
      $(document).ready(function(){  
           $('.delete_data').click(function(){  
                var id = $(this).attr("id");  
                if(confirm("Are you sure you want to delete this?"))  
                {  
                     window.location="<?php echo base_url(); ?>main/delete_data/"+id;  
                }  
                else  
                {  
                     return false;  
                }  
           });  
      });  
      </script>  
 </div>  
 </body>  
 </html>