Select, Update and Delete data in CodeIgniter 3 and MySQL
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 the 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 a MySQL table for student details
Create the table named 'student' in MySQL database. The table structure is given below:
Table: student
This table stores the 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:
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. The 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 - edit student and delete student.
For edit, it calls the update_student() method of the 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 an Ajax script to call delete_student() method of the controller. After a 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 in the 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, index() method to display the students, update_student() method for updating the student details and delete_student() method to delete a student.
Controller method index()
index() method is the default method in a controller. In this method it loads the student view that displays a 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 the students from the database table in an array $data[] and loads the student view with $data[].
Controller Method update_student()
When the user clicks on the Edit link for a student, this method is called with student_id as a parameter. It checks first if the form is submitted, if it is submitted, it processes the data, otherwise, it gets the details of the student from the 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 an Ajax request when the user clicks on the Delete student link from the list of students. This method gets the value of student_id and calls the 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";
}
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 an optional student_id parameter, if student_id has a value, it selects data from the 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 a parameter and deletes the row from the table for the specific student_id.
Our views, controller and model are developed. Now we need to do some setup changes before we run the application. Let's do that in next step.
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 page displaying a list of students as given 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 products or items or users.
Conclusion
Post a Comment