Editing database records by user using PHP & 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

How the script work.

Records with edit option script
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

download script
Visitors Rating
Your Rating




Google+
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
smo

13-11-2014

Second part is on editing more fields.
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
subhendu

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.

Post Comment This is for short comments only. Use the forum for more discussions.




HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer