Display, update and delete data from database in CodeIgniter 3
Use CodeIgniter 3 with MySQL to display and update data in a web application. Also, use Ajax in CodeIgniter 3 to delete a record from database. You do not need to submit the form or refresh the whole page to delete the data from MySQL database.
In this topic, we will create an application that will display a list of students with their details and for each student, one (say admin) can update or delete the student. The application will be developed using CodeIgniter 3 and MySQL. To delete the student record, we will use Ajax. You will also learn CodeIgniter 3 form validation in developing this application.
Create the table named 'student' in MySQL database. Table structure is given below:
Table: student
This table stores details of all students.
Below are the columns in this table.
student_id - Primary key and auto incremented id of the student
first_name - First name of the student
last_name - Last name of the student
school - School name of the student
Create table script for this table is given below; you can use this code to create the table.
student.sql
CREATE TABLE `student` (
`student_id` int(11) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`school` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student` (`student_id`, `first_name`, `last_name`, `school`) VALUES
(1, 'Peter', 'Samuel', 'St. Jones High School'),
(2, 'Ram', 'Nayak', 'St. Jones High School'),
(3, 'Eric', 'Stewart', 'St. Paul\'s High School'),
(4, 'Nigel', 'Cullen', 'St. Paul\'s High School'),
(5, 'George', 'Sircar', 'St. Paul\'s High School');
ALTER TABLE `student`
ADD PRIMARY KEY (`student_id`);
ALTER TABLE `student`
MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
Rows in the table should be as below:
Step 2 - Create the views to display and update students
We will use the folder named 'student' under xampp/htdocs. So, the root directory is xampp/htdocs/student.
There are two views to display the list of students (student.php) and to update the students (update_student.php).
View to display the students (student.php)
Student view lists the details of the student. Controller sends the student data to the view and using a FOR LOOP student rows are displayed in an html table. There are two actions against each student row, edit student and delete student.
For edit, it calls the update_student() method of student controller with student_id as the parameter. For delete, it calls a JavaScript function delStudent() with student_id as the parameter to the function.
Function delStudent()
JavaScript function runs a Ajax script to call delete_student() method of the controller. After student row is deleted by the controller method, it displays the message and reloads the student list.
<script>
function delStudent(student_id) {
if (confirm("Are you sure you want to delete student with id = " + student_id+"?")) {
$.ajax({
type: "POST",
url: "<?= base_url()?>delete_student",
data: {id:student_id},
success: function(msg) {
alert(msg);
location.reload();
}
});
}
}
</script>
View to Update student (update_student.php)
Update student page displays a form with the existing values displayed in various fields.
application/views/update_student.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>Update Student</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="<?php echo base_url('assets/css/style.css') ?>">
</head>
<body>
<div class="container">
<h4>Update Student Details</h4>
<?php if (!empty($student_details))
foreach ($student_details as $row){
$student_id = $row->student_id;
$f_name = $row->first_name;
$l_name = $row->last_name;
$school = $row->school;
} ?>
<p class="error "><?php if ($err_msg != "") { echo $err_msg;}?></p>
<p class="success"><?php if ($succ_msg !="") {echo $succ_msg;}?></p>
<form id ="frm" action = "<?=base_url('update_student/'.$student_id)?>" method = "post">
<div class="form-group col-md-12">
<input type = "hidden" name = "student_id" value = "<?php echo $student_id?>">
<label>First Name</label>
<input type = "text" name="f_name" id = "f_name" class="form-control" value="<?php if (!empty($f_name)) echo $f_name; else echo set_value('f_name');?>" placeholder="Enter First Name">
<div class="error"><?php echo form_error('f_name'); ?></div>
</div>
<div class="form-group col-md-12">
<label>Last Name</label>
<input type = "text" name="l_name" id = "l_name" class="form-control" value="<?php if (!empty($l_name)) echo $l_name; else echo set_value('l_name');?>" placeholder="Enter Last Name">
<div class="error"><?php echo form_error('l_name'); ?></div>
</div>
<div class="form-group col-md-12">
<label>School Name</label>
<input type = "text" name="school" id = "school" class="form-control" value="<?php if (!empty($school)) echo $school; else echo set_value('school');?>" placeholder="Enter School Name">
<div class="error"><?php echo form_error('school'); ?></div>
</div>
<div class="col-md-12 text-center">
<input type ="submit" name="update_student" class="btn btn-primary" value="Submit">
<a href = "<?=base_url()?>" class="btn btn-danger" name="login">Cancel</a>
</div>
</form>
</div>
</body>
</html>
In the <form> action it calls update_student() method of the controller.
There is a custom stylesheet used in the views. It is given below:
Our controller class will have three methods, an index() method to display the students, update_student() method for updating student details and delete_student() method to delet a student.
Controller method index()
index() method is the default method in a controller. In this method it just loads the student view that displays list of students.
public function index(){
$data = [];
$data['student_data'] = $this->ModelStudent->get_student();
$this->load->view('student',$data);
}
It calls a model method get_student() to get all students from database table in an array $data[] and loads the student view with $data[].
Controller Method update_student()
When the user clicks on Edit link for a student, this method is called with student_id as parameter. It checks first if form is submitted, if it is submitted, it processes the data, otherwise it gets the details of the student from database and loads the update student view. See the code below:
public function update_student(){
$data = [];
$data['succ_msg'] = "";
$data['err_msg'] = "";
if (isset($_POST['update_student'])) {
// get the values of form fields
$student_id=$this->input->post('student_id');
$f_name=$this->input->post('f_name');
$l_name=$this->input->post('l_name');
$school=$this->input->post('school');
$data['student_id'] = $student_id;
// validate each field
$this->form_validation->set_rules('f_name', 'First Name', 'trim|required',
array(
'required'=>'You must provide a %s')
);
$this->form_validation->set_rules('l_name', 'Last Name', 'trim|required',
array(
'required'=>'You must provide a %s')
);
$this->form_validation->set_rules('school', 'School Name', 'trim|required',
array(
'required'=>'You must provide %s')
);
if ($this->form_validation->run() == FALSE) {
// validation not passed, so load the form again
$this->load->view('update_student',$data);
}
else {
// validation is success, so go for registration
$update_data = array('first_name'=>$f_name, 'last_name'=>$l_name,'school'=>$school);
$result = $this->ModelStudent->update_student($update_data,$student_id);
if ($result){
$data['succ_msg'] = "Student Details updated successfully";
}
else{
$data['err_msg'] = "Error in updating student details";
}
$this->load->view('update_student',$data);
}
}
else{
$student_id = $this->uri->segment(3, 0);
$data['student_details'] = $this->ModelStudent->get_student($student_id);
$this->load->view('update_student',$data);
}
}
Validations are performed on the input data - first name, last name and school name using form_validation->set_rules() method. For details on how to do form validation in CodeIgniter, you can read the topic How to do form validation in CodeIgniter 3.
Controller Method delete_student()
Method delete_student() is called by Ajax request when user clicks on Delete student link from the list of students. This method gets the value of student_id and calls model method delete_student() to delete the student row from the database.
public function delete_student(){
$student_id = $this->input->post('id');
$result = $this->ModelStudent->delete_student($student_id);
if ($result)
echo "Student details deleted Successfully";
else
echo "Error in deleting Student record";
}
Step 4 - Model (ModelStudent.php)
We have used three methods of the model; These are get_student(), update_student() and delete_student(). So now let's see the code for these methods in the model:
application/models/ModelStudent.php
<?php
class ModelStudent extends CI_Model {
function __construct() {
parent::__construct();
}
function get_student($student_id = "") {
$this->db->select('*');
$this->db->from('student');
if ($student_id != "")
$this->db->where('student_id',$student_id);
$this->db->order_by('student_id');
$query = $this->db->get();
return $query->result();
}
function update_student($data, $student_id) {
if (!empty($data)) {
$this->db->where('student_id',$student_id);
$result = $this->db->update('student', $data);
return $result;
}
}
function delete_student($student_id) {
$this->db->where('student_id',$student_id);
if ($this->db->delete('student'))
return true;
else
return false;
}
}
?>
Method get_student()
It has optional student_id parameter, if student_id has a value, it selects data from student table for the student_id. But if student_id is blank, it selects all students from the table. It returns the result set to the controller.
Method update_student()
This function takes two parameters, an array of values and student_id. It updates the student table with the values for the specific student_id.
Method delete_student()
It takes student_id as parameter and deletes the row from the table for the specific student_id.
Our views, controller and models are developed. Now we need to do some setup changes before we run the application. Let's do that in next step.
Step 5 - Update Setup files and Test the Application
This will redirect all requests except those which are not existing files or directories.
Test The Application
Check that in the XAMPP control panel Apache and MySQL services are running. Run localhost/student in the browser. You will see the list of students page displayed as below:
Click on the Edit link for any student and update the details of the student. Also delete a student and see the list does not contain the deleted student.
Conclusion
This was a simple yet useful application in CodeIgniter to update and delete rows in MySQL database. There can be many uses of this type of application in our projects. I have used students as an example, it can be product or items or users. Hope this was useful for you.
Development Steps
Conclusion