Updating database records by user using PHP MYSQL & Ajax

We will display all the student records and give the option to the user to edit the mark obtained by each student. Here user will update the mark and same will be displayed after updation of the record.

Why use Ajax

For better user experience we will update the record with new mark without reloading the page by using Ajax. Here each record will have one Edit button. On click of the update button we will display one text box with default mark ( record data ) inside it. User can change the data and click the Updation button. Through Ajax we will post the updated mark to our PHP script to update the MySQL student table record. Based on the success of the updation process we will display a message and updated mark.

Basic understanding of Ajax is required for this tutorial

JQuery This script is modifed by using JQuery.

How the script work.

We display the records in a tabular manner. Each record will have mark at right most column with one button to edit. See the demo

Demo of Record display with edit option

We have displayed our records by using Query and using SQL limit command to display only 10 records. ( you can extended as many numbers you want ) . Each student mark we kept in a separate div tag. Here the id of the div tag is the id of the student record ( which is unique ). Example 3, 14, 6 etc. To manage the button we used same id with ‘s’ as prefix. Example s4, s12 etc.

require "config.php"; // MySQL connection string
echo "<div id="msgDsp" STYLE="position: absolute; right: 0px; top: 10px;left:800px;text-align:left; FONT-SIZE: 12px;font-family: Verdana;border-style: solid;border-width: 1px;border-color:white;padding:0px;height:20px;width:250px;top:10px;z-index:1"> Edit mark </div>";

$count="SELECT name,id,class,mark,sex FROM student LIMIT 10";

$i=1;

echo "<br><br><br><table class='t1'><tr><th>Name</th><th>Class</th><th>Sex</th><th>Mark</th><th>Edit</th></tr>";
foreach ($dbo->query($count) as $row) {
$m=$i%2;
$sid='s' . $row['id'];
echo "<tr class='r$m'><td> $row[name] </td><td> $row[class]  </td><td> $row[sex] </td><td>  <div id=$row[id] STYLE="width:140px;">$row[mark]</div></td><td><input type=button id=$sid value='Edit' onclick=edit_field($row[id])></td></tr>";
$i=$i+1;
}
echo "</table>";

User clicks the Edit button

With the click of Edit button we will post the id of the record ( same as div tag id ) to the JavaScript function edit_field(). Inside this function we will create two more variables using this id data.

function edit_field(id){


var sid='s'+id;

var t1='t'+ id;

-------

We read the present mark ( data ) of the record

var mark=document.getElementById(id).innerHTML; // Read the present mark

Now we fill the div tag with a input text box with default value of present mark and with a button to trigger updation process through Ajax.

document.getElementById(id).style.backgroundColor = '#ffff00'; // Add different color to background
document.getElementById(id).innerHTML = '<input type=text id=' + t1 + ' value='+ mark + ' size=2> <input type=button value=Update onclick=ajax(' + id + ');>'; // Add different color to background
document.getElementById(id).style.display = 'inline';  // show the details

Now there is no need to show again the edit button so we will hide the same.

document.getElementById(sid).style.display = 'none'; // Hide the edit button

To our Ajax function we will pass the id value and inside the Ajax function we will read the updated mark. By using these two data we will prepare our string to send data to PHP script to update the record.

Displaying Messages by using div layer

We kept one div layer msgDsp to display all the messages. During process time we will display one wait image. Based on the outcome of record updation we can display a failure or success message using this div layer. After displaying the message we will hide this layer after some time. You can read more on div message layers here.

Backend PHP Script to update the record ( display-ajax.php)

To this page we will be posting two parameters, id and mark ( as updated by user ).

$id=$_POST['id'];

$mark=$_POST['mark'];

Before the updation we will keep one flag to monitor the process. If there is any failure of validation or database updation then our flag status will change to Failed. Similarly we will add one more variable message to store the matching failure or success message to be displayed to user.

$message=''; // 

$status='success';              // Set the flag

After receiving the data we will first sanitize them. You will get more details on this at our SQL security section.

if(!is_numeric($mark)){ // checking data

$message= "Data Error";

$status='Failed';
 }

As we are updating student mark so we will keep a minimum value of 0 and maximum value of 100 for the user entered data. You can add your own validation process to the data as per your requirements. We also expect id and mark to be numeric only so we will use is_numeric() function to check these two variables.

if($mark > 100 or $mark < 0 ){
$message= "Mark should be between 0 & 100";
$status='Failed';
}

If all data validations are cleared then we will go for updating the record with new data ( mark ).

require "config.php"; // MySQL connection string
$count=$dbo->prepare("update student set mark=:mark WHERE id=:id");
$count->bindParam(":mark",$mark,PDO::PARAM_INT,3);
$count->bindParam(":id",$id,PDO::PARAM_INT,3);

After this we will be using Json to return the string of data to our main page to display the updated record.

$a = array('id'=>$id,'mark'=>$mark);
$a = array('data'=>$a,'value'=>array("status"=>"$status","message"=>"$message"));
echo json_encode($a);

Receiving the updated record and displaying.

Our Ajax function will receive the updated record and check the status. If the updation is successful then it will display the updated mark and show the edit button again. We will display the message through our message div tag for 2 seconds.

var myObject = JSON.parse(httpxml.responseText); 
if(myObject.value.status=='success'){
document.getElementById(myObject.data.id).innerHTML = myObject.data.mark;
document.getElementById("msgDsp").innerHTML=myObject.value.message;
var sid='s'+myObject.data.id;
document.getElementById(sid).style.display = 'inline'; // Display  the edit button
setTimeout("document.getElementById('msgDsp').innerHTML=' '",2000)
}

If the updation has failed then we will keep showing the same update option and change the border color of the display message box to red while showing the failure message to user. We can show one reset button or close button so user can reload the page with fresh data.

Part II : Edit all the columns of a record

PHP MySQL Collecting single record from MySQL PHP Code generator for PDO & mysqli

Subscribe to our YouTube Channel here

kishan	27-05-2014
the above demo is nice.... it is editing only one field i want entire fields,,,for example firstname,lastname,email fields in a table format having only one edit button....I need help...thanks

rajeev	02-12-2014
click on text and get value of each row record click on text and get value of each row record

renu	01-10-2015
don't have idea about pdo i generlly use mysqli........ if possible send me the above example in mysqli

Robert	13-05-2017
I have read the tutorial, but it is missing the part that explains how to "use" and how to "interpreter" the tutorial itself. It is excessively concise, maybe it is for gurus, because if could it be for newbies ... well it looks to me they are missing too many steps. Regards

smo1234	19-05-2017
Third part of this tutorial is added by using JQuery. This part is kept in display-edit3 folder. In this MySQLI is used in place of PDO. Those who don't want to use PDO can use this part for MySQLi with minor changes. We tried to explain more for better understanding of all.

David	22-01-2019
Can you help me please ? onclick=edit_field($row[id]) does nothing. My table data is displayed but cannot be edited ! Is there an update to the script ? Thank you.

smo	13-11-2014
Second part is on editing more fields.

Editing database records by user using PHP & Ajax