How to update data using Ajax in PHP and MySQL without page refresh

Updating data in the database without a page refresh is an excellent way to achieve user experience as it is very fast. Using Ajax with PHP, you can update or refresh a specific part of the web page without reloading the entire page.

In this topic, I will develop an application to update user status (Active or Inactive) without submitting a form using PHP, Ajax and MySQL. Admin can unlock the account or even can lock an account if required by just clicking a button. This can be applied to any similar type of requirement where a specific field is to be updated in the database and the updated value is to be displayed on the screen instantly.

So, how it works? We will first display a list of users from the database in a html table with details of users with their status. If the user status is Active, Admin can make it Inactive by clicking a button. Similarly, if the user status is Inactive, Admin can activate the user by clicking the button. An Ajax script is executed on the onClick event of the button to update the status in the database. After updating the status, it displays the list of users with updated status.

Assumption

  1. You must have basic knowledge of PHP, HTML, CSS and JavaScript/jQuery.
  2. You must have basic understanding of Ajax. You can read below topics for more details:
    1. How to install xampp for Windows with setup for PHP development
    2. How to write PHP code with examples for beginners
    3. How to build a simple CRUD application in PHP and MySQL

Folders and Files

Below is a screenshot of the folder structure and files I am using:

jquery ajax update database onclick

I have created a folder named 'user' under 'xampp/htdocs' as I am using XAMPP. If you are using WAMP, create 'user' folder under 'www'.

  1. Folder 'cfg' - In this folder I have kept dbconnect.php which is used to establish a connection to the MySQL database.
  2. Folder 'css' - My custom stylesheet (style.css) is in this folder
  3. Folder 'js' - In this folder I have my custom JavaScripts file (status.js). Ajax Script is written in this file.
  4. index.php displays a list of users from database in a html table and provides option for admin to change status of a user.
  5. update_user_status.php is the php program called from Ajax. This php program updates the user status in MySQL database and display the updated list of users.

Below is the screenshot of list of users displayed by index.php.

ajax update database without refresh

Check the last two columns; status of the user is displayed and then in Action a button is displayed to make the user Active/Inactive depending on current status of the user.

Delete duplicate data in SQLWatch YouTube Video

Step 1 - Create a MySQL table for user details

We will create a table named 'user' in MySQL database. We will use "test" database. Just make sure same table does not exist already. Table structure is given below:

Table: user

This table stores all users.

how to update data in jquery

Table has 4 columns in it.

  1. id - It is the primary key and auto incremented id to identify each row
  2. name - Name of the user
  3. email - email ID of the user
  4. status - Status of the user, enum (A - Active, I - Inactive)
Create table script and insert statements for this table are given below; you can use this code to create the table and populate data.

user.sql


  CREATE TABLE `users` (
    `id` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `email` varchar(100) NOT NULL,
    `status` enum('A','I') NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
  
  INSERT INTO `users` (`id`, `name`, `email`, `status`) VALUES
  (1, 'User Name1', 'user1@test.com', 'I'),
  (2, 'User Name2', 'user2@test.com', 'A'),
  (3, 'User Name3', 'user3@test.com', 'I'),
  (4, 'User Name4', 'user4@test.com', 'A'),
  (5, 'User Name5', 'user5@test.com', 'A');
  
  ALTER TABLE `users`
    ADD PRIMARY KEY (`id`);
  
  ALTER TABLE `users`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

After you create the table, please verify if table structure is same as above. Also make sure all rows are populated. There should be five test users in the table with different status (Active and Inactive).

Step 2 - Connect to MySQL database (dbconnect.php)

Use below script to connect to the database. Note that we have this database connection php program in 'cfg' folder. This is written once and used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.

dbconnect.php


$server = "localhost";
$uid = "root";
$pwd = "";
$dbname = "test";
$conn = new mysqli($server, $uid, $pwd, $dbname);

if ($conn->connect_error)
die ("Error in Connection".$conn->connect_error);

Parameters used to connect to the database:

  1. server - in our case it is localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - test in our case.

If connection is successful it will return true and false otherwise. We will include this dbconnect.php in other php programs so that we do not need to write it again in the program. For detail database connection understanding please read topic How to connect to MySQL database in PHP using MySQLi and PDO.

Step 3 - Display list of users (index.php)

Our index.php displays the list of users along with their status in an html table. There will be Action for each user either to make an user active/inactive. It is a simple php code to select all users and display one by one in a loop using an html table. Depending on status of the user a green or red button is displayed. Let's see the code:

index.php

we are using Bootstrap 5, jQuery library is added to run jQuery Ajax. Our jQuery Ajax code will be written in js/status.js. Note that we have included "table_data.php" which is used to display data in an html table. See below:

table_data.php

Let us see the last two columns of the table. If status is 'A', it displays 'Active', otherwise it displays 'Inactive'. JavaScript function updateStatus() is called on the onClick event with two parameters, userid and status as 'A' or 'I'.

Step 4 - Write Ajax script to update status (status.js and update_user_status.php)

Function updateStatus()

status.js


  function updateStatus(id, status){
    $.ajax({
        type:"post",
        url:"update_status.php",
        data:{id:id, status:status},
        success:function(response){
            if (response == "error")
                $(".errMsg").html("Some Error occurred, please contact Admin");
            else
                $(".table").html(response);
        },
        error:function(){
            $(".errMsg").html("Some Error occurred, please contact Admin");
        }
    });
}

This function runs an Ajax request with below parameters:

  1. type: post. We are using POST method
  2. url : update_user_status.php. This PHP updates the status of the user and refresh the user list in the table
  3. data: we are sending two data values with the request, one is userid and other is status
  4. success: we write the response of Ajax call in the table with class "table" which is defined in table_data.php. Response from Ajax call is nothing but displaying updated list of users. In case of error we are displaying the error message in a div with class="errMsg" defined in table_data.php.

Now, let us see the code for update_user_status.php.

update_user_status.php


  include "cfg/dbconnect.php";
  $id = $_POST['id'];
  $status = $_POST['status'];
  
  $sql_u = "update users set status = ? where id = ?";
  try{
  $stmt_u = $conn->prepare($sql_u);
  $stmt_u->bind_param("si",$status, $id);
  $stmt_u->execute();
  
  $sql = "select * from users order by name";
  $stmt = $conn->prepare($sql);
  $stmt->execute();
  $result = $stmt->get_result();
  // refresh the table to show updated status
  include "table_data.php"; 
  
  }
  catch(Exception $e){
      echo "error";
  }

It takes the userid and status from Ajax data parameter and update the status of the user in the database. Once updated, it displays the list of users in the html table. We just included table_data.php. So, in this table updated status of the user will be visible.

Step 5 - Add CSS (style.css)

We need to add some styles. Very simple and basic styles are used here. You can always modify it to give the page a better look. I have already added style.css in index.php. See below:

css/style.css


 h1, h2{
     text-align: center;
 }
 body{
     width:50%;
     margin: auto;
 }
 table{
     text-align: center;
 }
 .btn{
     width:106px;
     font-size: 13px;
 }
  

Test the Application

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

Update Data Using Ajax in PHP

You will see the users are displayed with name, email, status and action for each user. Click on the button for a user and see if the status gets changed. Also, the action button for that user changes after the status gets changed. Check in database table also if the status for the user is updated in the users table.

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

Scope of Improvement

You can use a search option in case number of users are more. Here we are using only 5 users so search is not important. Application might have thousands of users we do not want to list all users where it is difficult to find the user even it is sorted. So better option is to use search criteria. It will display only those users matching search criteria and then update the status.

Download Source Code

I have put all codes 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 directly use the code or you can modify them as per your requirements.

Conclusion

You can apply this in your project where you want user to update specific data in a web page without a form submit or page refresh. Hope it will be useful for you.

Delete duplicate data in SQLWatch YouTube Video