Update Data Using Ajax jQuery in PHP

In this tutorial, You will learn How to update data using Ajax in PHP. I have shared the best ajax jQuery script with a simple example. It will help you to update data without page refresh.

When you use to execute PHP code without reloading the page then you have the best option to use ajax jQuery. It is very fast & easy to implement any backend script. If you are working with the backend script, you should develop your project by using ajax jquery.

update data using ajax in php

How to Update Data Using Ajax in PHP

Before Getting Started this Script, You must configure the following basic requirement.

  • First of all, you should create the following folder structure
codingstatus.com/
  |__database.php
  |__show-data.php
  |__data-list.js
  |__update-ajax.js
  |__update-data.php
  |__update-form.php
  |__style.css
  • Insert Data into MySQL Database

Read Also –

Insert Data Using Ajax in PHP

Load More Data on Infinite Scroll Using Ajax

Dependent Dropdown Using Ajax PHP

1. Connect MySQL Database to PHP

Use the following code to connect MYSQL database to PHP

<?php
$hostname     = "localhost"; // replace with your hostname
$username     = "root";     // replace with your username
$password     = "";        // replace with your username
$databasename = "codingstatus"; // replace with your database name
// Create connection
$connection = mysqli_connect($hostname, $username, $password,$databasename);
// Check connection
if (!$connection) {
    die("Unable to Connect database: " . mysqli_connect_error());
}

?>

2. Fetch Data From Database

To fetch data from database, you have to configure the following steps –

  • Include database connection file
  • Create a custom function fetch_data() to fetch data from the database.
  • Create another custom function show_data() to display data in an HTML table.
  • Call fetch_data()   passing $connection variable  as a parameter and assign it to a new variable $fetchData
  • Also, show_data() and pass $fetchData as a parameter.

File Name – show-data.php

<?php
include("database.php");

$fetchData= fetch_data($connection);
show_data($fetchData);
// fetch query
function fetch_data($connection){
  $query="SELECT * from usertable ORDER BY id DESC";
  $exec=mysqli_query($connection, $query);
  if(mysqli_num_rows($exec)>0){

    $row= mysqli_fetch_all($exec, MYSQLI_ASSOC);
    return $row;	
        
  }else{
    return $row=[];
  }
}


function show_data($fetchData){


 echo '<table border="1">
        <tr>
            <th>S.N</th>
            <th>Full Name</th>
            <th>Email Address</th>
            <th>City</th>
            <th>Country</th>
            <th>Edit</th>
            <th>Delete</th>
        </tr>';

 if(count($fetchData)>0){
      $sn=1;
      foreach($fetchData as $data){ 

  echo "<tr>
          <td>".$sn."</td>
          <td>".$data['fullName']."</td>
          <td>".$data['emailAddress']."</td>
          <td>".$data['city']."</td>
          <td>".$data['country']."</td>
          <td><a href='javascript:void(0)' onclick='editData(".$data['id'].")'>Edit</a></td>
          <td><a href='javascript:void(0)' onclick='deleteData(".$data['id'].")'>Delete</a></td>   </tr>";
       
  $sn++; 
     }
}else{
     
  echo "<tr>
        <td colspan='7'>No Data Found</td>
       </tr>"; 
}
  echo "</table>";
}


3. Show Data in HTML Tables

To show data in an HTML table, you have to configure the following steps-

  • Include style.css to design HTML table. It will provide in upcoming steps.
  • Create a div section with id="msg" to display a message after updating data.
  • Include show-data.php to display data in the form of Table.
  • Include jQuery CDN to execute ajax jquery code
  • Also, Include file update-ajax.js to execute a custom ajax script.

File Name – data-list.php

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="style.css">
</head>
<body>

<div id="msg"></div>
<div id="table-container">
  <?php 
  include('show-data.php');

  ?>
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script type="text/javascript" src="update-ajax.js"></script>

</body>
</html>

4. Update Data using Ajax

To update data using ajax, you have to configure the following steps –

  • Create a custom function with id parameter and assign it to a variable editData. This function will execute when you click the edit button then an update form will be loaded with value based on passing id.
  • Write ajax script on submitting the form to send request to update-data.php for updating the data without reloading the page.
  • When data are updated on clicking the submit button then a message will be displayed in div section id="#msg"

File Name – update-ajax.js

var editData = function(id){
   $('#table-container').load('update-form.php')

    $.ajax({    
        type: "GET",
        url: "update-data.php", 
        data:{editId:id},            
        dataType: "html",                  
        success: function(data){   

          var userData=JSON.parse(data);  
          $("input[name='id']").val(userData.id);               
          $("input[name='fullName']").val(userData.fullName);
          $("input[name='emailAddress']").val(userData.emailAddress);
          $("input[name='city']").val(userData.city);
          $("input[name='country']").val(userData.country);
           
        }

    });
};



$(document).on('submit','#updateForm',function(e){
        e.preventDefault();
         var id= $("input[name='id']").val();               
         var fullName= $("input[name='fullName']").val();
         var emailAddress= $("input[name='emailAddress']").val();
         var city= $("input[name='city']").val();
         var country= $("input[name='country']").val();
        $.ajax({
        method:"POST",
        url: "update-data.php",
        data:{
        	updateId:id,
        	fullName:fullName,
        	emailAddress:emailAddress,
        	city:city,
        	country:country

        },
        success: function(data){
        $('#table-container').load('show-data.php');
        $('#msg').html(data);
   
    }});
});

5. Update Data Using PHP

To update data using PHP, you have to configure the following steps –

  • Include the database connection file.
  • Create a  custom edit_data() to fetch data on the basis of a particular id.
  • Create another custom function update_data() to update data using PHP and MySQL.
  • Also, create a custom function legal_input() to validate the legal data.

File Name – update-data.php

The code of this file will be executed on the Ajax request.

<?php
include('database.php');


if(isset($_GET['editId'])){
   $id= $_GET['editId'];
    edit_data($connection, $id);
}


if(isset($_POST['updateId'])){
   $id= $_POST['updateId'];
   update_data($connection,$id);
}
// edit data query

function edit_data($connection, $id){
    $query="SELECT * from usertable WHERE id=$id";
    $exec=mysqli_query($connection, $query);

    $row=mysqli_fetch_assoc($exec);
    echo json_encode($row);
                
    
}

// update data query
function update_data($connection, $id){

      $fullName= legal_input($_POST['fullName']);
      $emailAddress= legal_input($_POST['emailAddress']);
      $city = legal_input($_POST['city']);
      $country = legal_input($_POST['country']);

      $query="UPDATE usertable 
              SET fullName='$fullName',
                  emailAddress='$emailAddress',
                  city= '$city',
                  country='$country' WHERE id=$id";

      $exec= mysqli_query($connection,$query);
  
      if($exec){
        
         echo "data was updated"; 
        
      }else{
         $msg= "Error: " . $query . "<br>" . mysqli_error($connection);
         echo $msg; 
      }
}
   

// convert illegal input to legal input
function legal_input($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}

?>

6. Create Update Form Using HTML

Use the following HTML code to create update form.

File Name – update-form.php

<!--====form section start====-->
<div class="user-detail">
    <h2>Update User Data</h2>
    <p id="msg"></p>
    <form id="updateForm" method="POST">
      <input type="hidden" name="id" id="updateId">
          <label>Full Name</label>
          <input type="text" placeholder="Enter Full Name" name="fullName" required>
          <label>Email Address</label>
          <input type="email" placeholder="Enter Email Address" name="emailAddress" required>
          <label>City</label>
          <input type="city" placeholder="Enter Full City" name="city" required>
          <label>Country</label>
          <input type="text" placeholder="Enter Full Country" name="country" required>
          <button type="submit" name="update">Submit</button>
    </form>
        </div>
</div>

 

7. Use CSS Code to design Update Form & Table

You can use the following CSS code to design an update form & table.  Even you write your own css code according to your project requirement.

File  Name – style.css

* {
  box-sizing: border-box;}
.user-detail {
    height: 100vh;
    border: 2px solid #f1f1f1;
    padding: 16px;
    background-color: white;
    width: 30%;}
input{
  width: 100%;
  padding: 15px;
  margin: 5px 0 22px 0;
  display: inline-block;
  border: none;
  background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
    background-color: #ddd;
    outline: none;}
button[type=submit] {
    background-color: #434140;
    color: #ffffff;
    padding: 10px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 100%;
    opacity: 0.9;
    font-size: 20px;}
label{
  font-size: 18px;;}
button[type=submit]:hover {
  background-color:#3d3c3c;}
  #msg{
    color: green;
    font-size: 16px;
  }



   * {box-sizing: border-box;}
.user-detail {
    height: 100vh;
    border: 2px solid #f1f1f1;
    padding: 16px;
    background-color: white;
    width: 30%;}
input{
  width: 100%;
  padding: 15px;
  margin: 5px 0 22px 0;
  display: inline-block;
  border: none;
  background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
    background-color: #ddd;
    outline: none;}
button[type=submit] {
    background-color: #434140;
    color: #ffffff;
    padding: 10px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 100%;
    opacity: 0.9;
    font-size: 20px;}
label{
  font-size: 18px;;}
button[type=submit]:hover {
   background-color:#3d3c3c;}
.form-title a, .form-title h2{
   display: inline-block;}
.form-title a{
      text-decoration: none;
      font-size: 20px;
      background-color: green;
      color: honeydew;
      padding: 2px 10px;}
table, td, th {  
      border: 1px solid #ddd;
      text-align: left;}
table {
      border-collapse: collapse;
      width: 50%;}
.table-data{
        position: relative;
      left:50px;
      top:50px;}
th, td {
      padding: 15px;
    }
body{
    overflow-x: hidden;}
.list-title a, .list-title h2{
   display: inline-block;
   margin: 0px 35px;}
   .list-title a{
      text-decoration: none;
      font-size: 20px;
      background-color: green;
      color: honeydew;
      padding: 2px 10px;}

 

Suggestion

Dear Developers, I hope that you have got the above Ajax Script. Now, you can Update Data Using Ajax in PHP and MySQL. If you have any queries, you can ask me through through the following comment box

I regularly share my coding knowledge with you. So, you should continue to visit my blog and share this tutorial with your friends.

Thanks for giving the time to this tutorial.

Leave a Comment