Online edit and update of data by using PHP JQuery & storing data in MySQL table.

Records will be displayed in table showing name , class, mark , id of a student. We have used student table for this example.
Record Edit flow

User can click edit button associated with each record and then change the data. On click of edit button the record will change to edit mode by displaying text box and radio buttons for user to change the data. User can change the data and use the update button to update data in the table.
After updating the new data will be displayed.

This script is developed using PHP as backend script , MySQL as database, and JQuery at front end.

To manage database PHP MySQLi driver is used. You can get mysqli connection string here.

There are three files used in this script. You can download the zip file at the end of this tutorial. Folder Name : display-edit3

Display.php :

This file shows the records in a table and edit button is show against each record. Jquery code manages the data posting and colleting the same after update.

Display-submit.php :

this file collects the data and update the table. After updation returns the data to display.php file to display updated data.

Config.php :

MySQLi connection string with database login details ( userid , password & database name )

sql_dump.txt :

use this file to create your table inside MySQL database.

Displaying records with edit option ( display.php )

display.php file connects to database ( by config.php ) and display all records.

When the records are displayed ,data of each cell of the row is kept in a div tag and given one unique id. For example the student name of the record id = 6 is given as id= name_6.
Div with id

echo "<table class='t1' ><tr><th>ID</th><th>Name</th><th>Class</th><th>Sex</th><th>Mark</th><th>Edit</th></tr>";

while ($row = $stmt->fetch_assoc()) {
$m=$i%2; // To manage row style using css file. 
$mark_id='mark_' . $row['id'];  // Div tag to manage Mark data
$name_id='name_' . $row['id'];
$class_id='class_' . $row['id'];
$sex_id='sex_' . $row['id'];
echo "<tr class='r$m' height=50><td>$row[id]</td><td><div id=$name_id >$row[name]</div></td><td><div id=$class_id>$row[class]</div></td><td ><div id=$sex_id >$row[sex]</div> </td><td><div id=$mark_id>$row[mark]</div>
</td><td><input type=button value='EDIT' class='edit' id=$row[id]></td></tr>";
$i=$i+1;  // To manage row style
}

echo "</table>";

Each Edit button is given the id as row id of the record. When the user clicks an Edit button, the click function get executed and we came to know the row id or the edit button id by reading the id of the edit button.

var id=$(this).attr('id');

Changing value of the Edit button.

We can change the value ( text part or label ) of the button

$('#'+id).attr('value','Update');

We used the same button for Edit and updation so we will manage the value of button by using above code. To read the value ( or text ) written on button we used this code.

var value=$(this).attr('value');

How to show Edit mode.

While displaying the records we have stored each cell data in a div tag by giving unique id to it. ( check the code PHP above ) . Using the same id we can change the data to show a text box where old data is shown as default value.
Edit mode to update record

var mark = $('#'+'mark_'+id).html(); // reading the default value for mark 
$('#'+'mark_'+id).html("<input type=text name=mark id=mark value='"+mark+"' size=5>")

var name = $('#'+'name_'+id).html(); // reading the defaul value for name
$('#'+'name_'+id).html("<input type=text name=name id=name value='"+name+"' size=10>")

Radio buttons

To show the edit mode for sex ( to select ) we will display radio buttons and keep one of the button as selected based on the default data.

var sex = $('#'+'sex_'+id).html();
if(sex=='male'){var s1='checked';
var s2='';
}
else{var s1='';
var s2='checked';
}
$('#'+'sex_'+id).html("<input type='radio' name='sex' id='sex' value='male' "+s1+">male <input type='radio' name='sex' id='sex' value='female' "+s2+">female")

Updating the data

We have displayed the record in Edit mode by showing text box and radio buttons. While showing the edit mode our button to update will show Update as value ( or label ). When user clicks this update button the data entered by user is collected and posted to backend script display-submit.php. JQuery Post method is used to send data.

if(value=='Update'){
var mark = $('#mark').val(); // Reading user entered data for Mark
var name = $('#name').val();
var sex = $('input[name=sex]:checked').val();
var class1 = $('#class1').val();

  $.post( "display-submit.php", {"id":id,"mark":mark,"name":name,"sex":sex,"class":class1},function(return_data,status){

  if(return_data.value.status=="success"){
  var id=  return_data.data.id;
$('#'+'mark_'+id).html(return_data.data.mark);
$('#'+'class_'+id).html(return_data.data.class);
$('#'+'sex_'+id).html(return_data.data.sex);
$('#'+'name_'+id).html(return_data.data.name);
$('#'+id).attr('value','EDIT');
 }else{
// Write your own message / function  for failure of data update 
  }
$("#msgDsp").html(return_data.value.message);
        
},"json");
}

Receiving data at display-submit.php

We collect the data first and check or validate the same. On failure of data validation we post back the data with an error message. For example you can't enter mark beyond 100 for any student. You can add more validation of data as per your requirement.

If data validation is cleared then we will update the record with new data. Here we have used mysqli update functions.
After updating the data we will post the data back to display.php file by using JSON for displaying the updated data to user.
Full code is here

<?Php
$id=$_POST['id'];
$mark=$_POST['mark'];
$name=$_POST['name'];
$sex=$_POST['sex'];
$class=$_POST['class'];

$message=''; // 
$status='success';              // Set the flag  
//sleep(2); // if you want any time delay to be added

//// Data validation starts ///
if(!is_numeric($mark)){ // checking data
$message= "Data Error";
$status='Failed';
 }

if(!is_numeric($id)){  // checking data
$message= "Data Error";
$status='Failed';
}

if($mark > 100 or $mark < 0 ){
$message= "Mark should be between 0 & 100";
$status='Failed';
}
//// Data Validation ends /////
if($status<>'Failed'){  // Update the table now

//$message="update student set mark=$mark, name
require "config.php"; // MySQL connection string
$stmt = $connection->prepare("update student set mark=?,name=?,class=?,sex=? WHERE id=?");
if ($stmt) {
$stmt->bind_param('isssi', $mark,$name,$class, $sex, $id);
$stmt->execute();
$no=$stmt->affected_rows;
$message= " $no  Record updated<br>";
}else{
echo $connection->error;
}
}else{

}// end of if else if status is success 
$a = array('id'=>$id,'mark'=>$mark,'name'=>$name,'class'=>$class,'sex'=>$sex);
$a = array('data'=>$a,'value'=>array("status"=>"$status","message"=>"$message"));
echo json_encode($a); 
?>

This script is an extension of display of data using Ajax explained in PHP Section. You can download the ZIP file containing all the folders. This script is kept inside display-edit3 folder.

This article is written by plus2net.com team.

plus2net.com

Most Popular JQuery Scripts
1	Two dependant list boxes
2	Calendar with Date Selection
3	Data change by Slider
4	Show & Hide element

Update student table data using PHP , JQuery and database MySQL