How to delete records from MySQL table with Ajax 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 a student, I will use Ajax. You will also learn CodeIgniter 3 form validation in developing this application.

Step 1 - Create a MySQL table for student details

Let us create the table named 'student' in MySQL database. Table structure is given below:

Table: student

This table stores details of all students.

codeigniter 3 update database

Below are the columns in this table.

  1. student_id - It is the primary key and auto incremented id of the student
  2. first_name - First name of the student
  3. last_name - Last name of the student
  4. 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

After you create the table, verify if table structure is same as above and the rows are inserted in the table. Rows in the table should be as below:

codeigniter 3 updata data

Before I go to next step, let me show the folder structure I am using. I have created a folder named 'student' under xampp/htdocs and kept all CodeIgniter folder/files in this folder along with our custom files. So, the root directory is xampp/htdocs/student.

Codeigniter update database record

You can see, we have Controller - StudentController.php, Model - ModelStudent.php and two views under views folder. Folder 'assets' is created for the purpose of keeping all custom stylesheets, JavaScripts and image files. Here we have css/style.css for our application.

Step 2 - Create the views to display list of students and to update student details

There are two views for displaying list of students (student.php) and updating student details (update_student.php). Let us take a look at them one by one.

View for displaying students (student.php)

how to update form data in codeigniter

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 a html table. There are two actions against each student row, edit student and delete student. Let's see the code:

application/views/student.php

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.

View to Update student (update_student.php)

update query in codeigniter 3

Update student page displays a form with the existing values displayed in various fields.

application/views/update_student.php

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:

assets/css/style.css

I just added few simple styles here, you can always add better styles.

Now let's see the controller methods.

Step 3 - Write Controller code (StudentController.php)

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 for deleting 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.

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:

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 Validate Form Data 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.

Step 4 - Write Model code (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

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 Configuration files and Test the Application

We will update below files in config folder:

codeigniter update query with where condition

  1. Update autoload.php (application/config/autoload.php)

    I have added 'database' and 'form_validation' in autoload libraries.

    Also, 'url' and 'form' are added in helper array.

  2. Update config file (application/config/config.php)

    Set 'base_url' and 'index_page' as below in this file:

  3. Update database setup file (application/config/database.php)

    As I mentioned before, I am using a database named 'demo' in localhost with user 'root' with no password, so I updated database.php as below:

  4. Update routes file (application/config/routes.php) I updated routes.php for default controller which is StudentController.
  5. Update .htaccess (hypertext access) file to remove index.php from the URL

    .htaccess file is used to do configuration changes for a directory. I have kept .htaccess file in my root folder which is xampp/htdocs/student.

    update data in codeigniter

    Content of .htaccess file is given below:

    Using URL rewriting rule, it redirects all request except those which are not existing files or directories.

Test The Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/student. You will see the list of students page displayed as below:

onclick through delete record in ajax codeigniter

Click on 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.

Our development and testing are done. Hope you could understand all the steps and could test it successfully.

How to use Ajax in CodeIgniterDownload Source Code

I have put source codes for controller, model and view in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can use the code as is or you can modify them as per your requirements.

How to use delete method in AjaxConclusion

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.