Add, Edit And Delete Records Using jQuery, Ajax, PHP And MySQL

Last Updated : Jul 1, 2023

IN - jQuery Ajax PHP MySQL | Written & Updated By - Anjali

In this tutorial we will show you how to add, edit and delete records using jQuery, Ajax, PHP and MySQL. In this way you can do any modification in MySQL database dynamically means without refreshing your page.

In this you can insert new rows in database, edit existing row and update the row in database and delete any row in database.You may also like delete multiple records from MySQL using PHP.

To Add, Edit And Delete Records It Takes Only Three Steps:-

Make a PHP file to display database records
Make a js file and define scripting
Make a PHP file for database operations

Step 1. Make a PHP file to display database records

We make a PHP file and save it with a name display_records.php

// Database Structure 
CREATE TABLE `user_detail` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` text NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

<html>
<head>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="modify_records.js"></script>
</head>
<body>
<div id="wrapper">

<?php
$host="localhost";
$username="root";
$password="";
$databasename="sample";
$connect=mysql_connect($host,$username,$password);
$db=mysql_select_db($databasename);

$select =mysql_query("SELECT * FROM user_detail");
?>

<table align="center" cellpadding="10" border="1" id="user_table">
<tr>
<th>NAME</th>
<th>AGE</th>
<th></th>
</tr>
<?php
while ($row=mysql_fetch_array($select)) 
{
 ?>
 <tr id="row<?php echo $row['id'];?>">
  <td id="name_val<?php echo $row['id'];?>"><?php echo $row['name'];?></td>
  <td id="age_val<?php echo $row['id'];?>"><?php echo $row['age'];?></td>
  <td>
   <input type='button' class="edit_button" id="edit_button<?php echo $row['id'];?>" value="edit" onclick="edit_row('<?php echo $row['id'];?>');">
   <input type='button' class="save_button" id="save_button<?php echo $row['id'];?>" value="save" onclick="save_row('<?php echo $row['id'];?>');">
   <input type='button' class="delete_button" id="delete_button<?php echo $row['id'];?>" value="delete" onclick="delete_row('<?php echo $row['id'];?>');">
  </td>
 </tr>
 <?php
}
?>

<tr id="new_row">
 <td><input type="text" id="new_name"></td>
 <td><input type="text" id="new_age"></td>
 <td><input type="button" value="Insert Row" onclick="insert_row();"></td>
</tr>
</table>

</div>
</body>
</html>

In this step we create a database table 'user_detail' and insert some rows and then display the records to edit, delete and create a new row having textbox and buttons to insert new records in database.

We insert modify_records.js file which we were going to create in next step.You may also like add, edit and delete rows from table dynamically using JavaScript.

Step 2. Make a js file and define scripting

We make a js file and save it with a name modify_records.js

function edit_row(id)
{
 var name=document.getElementById("name_val"+id).innerHTML;
 var age=document.getElementById("age_val"+id).innerHTML;

 document.getElementById("name_val"+id).innerHTML="<input type='text' id='name_text"+id+"' value='"+name+"'>";
 document.getElementById("age_val"+id).innerHTML="<input type='text' id='age_text"+id+"' value='"+age+"'>";
	
 document.getElementById("edit_button"+id).style.display="none";
 document.getElementById("save_button"+id).style.display="block";
}

function save_row(id)
{
 var name=document.getElementById("name_text"+id).value;
 var age=document.getElementById("age_text"+id).value;
	
 $.ajax
 ({
  type:'post',
  url:'modify_records.php',
  data:{
   edit_row:'edit_row',
   row_id:id,
   name_val:name,
   age_val:age
  },
  success:function(response) {
   if(response=="success")
   {
    document.getElementById("name_val"+id).innerHTML=name;
    document.getElementById("age_val"+id).innerHTML=age;
    document.getElementById("edit_button"+id).style.display="block";
    document.getElementById("save_button"+id).style.display="none";
   }
  }
 });
}

function delete_row(id)
{
 $.ajax
 ({
  type:'post',
  url:'modify_records.php',
  data:{
   delete_row:'delete_row',
   row_id:id,
  },
  success:function(response) {
   if(response=="success")
   {
    var row=document.getElementById("row"+id);
    row.parentNode.removeChild(row);
   }
  }
 });
}

function insert_row()
{
 var name=document.getElementById("new_name").value;
 var age=document.getElementById("new_age").value;

 $.ajax
 ({
  type:'post',
  url:'modify_records.php',
  data:{
   insert_row:'insert_row',
   name_val:name,
   age_val:age
  },
  success:function(response) {
   if(response!="")
   {
    var id=response;
    var table=document.getElementById("user_table");
    var table_len=(table.rows.length)-1;
    var row = table.insertRow(table_len).outerHTML="<tr id='row"+id+"'><td id='name_val"+id+"'>"+name+"</td><td id='age_val"+id+"'>"+age+"</td><td><input type='button' class='edit_button' id='edit_button"+id+"' value='edit' onclick='edit_row("+id+");'/><input type='button' class='save_button' id='save_button"+id+"' value='save' onclick='save_row("+id+");'/><input type='button' class='delete_button' id='delete_button"+id+"' value='delete' onclick='delete_row("+id+");'/></td></tr>";

    document.getElementById("new_name").value="";
    document.getElementById("new_age").value="";
   }
  }
 });
}

In this step we create four funtions to edit, save, delete and insert records.In edit_row() function we get row id and get the name and age text and then insert the textbox with age and name value in both the columns to edit the text and then display the save button to save the records.

In save_row() function we get the row id and then with the help of row id get the name and age value then send the values to modify_records.php page to update the record and if the record updated successfully we display the edited record in there respective row.

In delete_row() function we again get the row id and send it to our modify_records.php page for deletion.

In insert_row() function we get the value of new_name and new_age from the textbox we made to insert new rows and send the data to modify_records.php page to insert new record in database and if record inserted successfully we display the new record in our table.

Step 3. Make a PHP file for database operations

We make a PHP file and save it with a name modify_records.php

<?php
$host="localhost";
$username="root";
$password="";
$databasename="sample";

$connect=mysql_connect($host,$username,$password);
$db=mysql_select_db($databasename);

if(isset($_POST['edit_row']))
{
 $row=$_POST['row_id'];
 $name=$_POST['name_val'];
 $age=$_POST['age_val'];

 mysql_query("update user_detail set name='$name',age='$age' where id='$row'");
 echo "success";
 exit();
}

if(isset($_POST['delete_row']))
{
 $row_no=$_POST['row_id'];
 mysql_query("delete from user_detail where id='$row_no'");
 echo "success";
 exit();
}

if(isset($_POST['insert_row']))
{
 $name=$_POST['name_val'];
 $age=$_POST['age_val'];
 mysql_query("insert into user_detail values('','$name','$age')");
 echo mysql_insert_id();
 exit();
}
?>

In this step we create three isset() conditions to edit, delete and insert records in database. In first isset() condition we get all the three value and update the row having that particular row id and display 'success'.

In second isset() condition we get the row id and delete the row having that id.In third isset() condition we get the name and age value and then insert in our database and then display the inserted id to create a new table row in our table in display_records.php page.

Always validate data before and after submitting the form to prevent sql injections.

Thats all, this is how to add, edit and delete records using jQuery, Ajax, PHP and MySQL. You can customize this code further as per your requirement. And please feel free to give comments on this tutorial.

I hope this tutorial on database operations in php helps you and the steps and method mentioned above are easy to follow and implement.

About Anjali

Experienced Computer Programmer with a broad range of experience in technology. Strengths in application development and Object Oriented architecture design, front end programming, usability and multimedia technology. Expert in coding languages such as C, C++ Java, JavaScript, PHP and more.

Follow Anjali On Linkedin 🡪