Editing all the columns data of a record using PHP MYSQL & Ajax

We can display rows of records and give user an option to edit and update any record. This way user can edit any data presented in a tabular format.

This tutorial is an extension of Editing single data of any record tutorial presented in Part 1. For better understanding it is highly recommended to read Part 1 of this tutorial before reading this.

Part I : Update single data of a record

JQuery This script is modifed by using JQuery.

Giving Option to Edit all the data of a row.

We will display each student record ( student table sql dump is part of the zip file for download ) in a row and each data of the row will be kept inside a div tag. Each div tag will have unique id associated to it. By adding student id to each data we can create one unique div tag. Note that each student has one unique id. For example student with id = 6 will have div tag associated with mark data like this.

<div id=mark_6>55</div>

Similarly the student with id=8 will have class data like this

<div id=class_8>Five</div>

This way all the records will have unique identity for the div tags matching to their respective id.

Demo of Record display with edit option

Once the user clicks the Edit button of the row the id of the record is passed to the JavaScript function edit_field(id).

Inside edit_field(id) function we can create eight variables to read all the data ( two for each data ) present in that particular row ( or record ). Here is an example to read and assign a text field to take care of user inputs.

var mark_id='mark_'+id; // To read the present mark from div 
var data_mark='data_mark'+ id;  // To assign id value to textbox

Using these two variables we can read the mark data and create one input text box to take new value.

var mark=document.getElementById(mark_id).innerHTML; // Read the present mark
document.getElementById(mark_id).innerHTML = "<input type=text id='" +data_mark + "' value='"+ mark + "' size=2>"; // Display text input

Here we are reading the value from the div tag and displaying the text box with a unique id. The same system is followed for all other two fields ( name & class ) but the gender field is to be handled separetly.

Selection of radio button

For the gender field ( data ) we can't display a text box and we have to give user an option to select Male or Female as sex. The present value ( of sex field ) we have to read and keep the respective radio button selected by default. User can change this selection and update the record.

Note that the pair of radio buttons of a record will have same name attribute but will have different id. So we will add one character 'F' to Female radio button and 'M' to mail radio button

var sex=document.getElementById(sex_id).innerHTML; 

if(sex=='male'){
document.getElementById(sex_id).innerHTML = "<input type=radio name='"+data_sex+"' value=male  id='" +data_sex+ "M' value='male' checked>Male <input type=radio name='"+data_sex+"'  id='" +data_sex+ "F' value='female' >Female  "; 
}else{
document.getElementById(sex_id).innerHTML = "<input type=radio name='"+data_sex+"' value=male  id='" +data_sex+ "M'>Male <input type=radio name='"+data_sex+"' value=female  id='" +data_sex+ "F' ' checked>Female "; 
}

In above code we read the default value of sex and compare it by using IF statement. Now if default value of sex is 'male' then the radio button with value = 'male' should be checked or selected. Similarly we have kept the Female radio button selected inside ELSE condition code block.

Update data

After entering the changes user will click the Update button. This button will pass the id value of the record to our Ajax script. Inside the Ajax function ajax(id), we will collect the id data and create variables to read the user updated data of the row.

var data_mark='data_mark'+ id;
var data_name='data_name'+ id;
var data_class='data_class'+ id;
var data_sexM='data_sex'+ id+'M';
var data_sexF='data_sex'+ id+'F';

Then by using getElementById() we will collect all the data of the record. Here is one sample.

var name = document.getElementById(data_name).value;

We will post all the data to our backend PHP processing script display-ajax.php. This posting is by using POST method using Ajax.

display-ajax.php

This PHP page operates at backend and update the database table. First we receive all the data posted to the script.

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

Next step is data validation. You may add more validation as per your requirement.

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

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

If the validation is passed then update the record based on the record id.

$count=$dbo->prepare("update student set mark=:mark,name=:name,class=:class,sex=:sex WHERE id=:id");
$count->bindParam(":mark",$mark,PDO::PARAM_INT,3);
$count->bindParam(":name",$name,PDO::PARAM_STR,50);
$count->bindParam(":class",$class,PDO::PARAM_STR,9);
$count->bindParam(":sex",$sex,PDO::PARAM_STR,6);

$count->bindParam(":id",$id,PDO::PARAM_INT,3);

if($count->execute()){
$no=$count->rowCount();
$message= " $no  Record updated <br>";
}else{
$message = print_r($dbo->errorInfo());
$message .= ' database error...';
$status='Failed';
}

Now return the data by using Json.

$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);

Displaying message and updated record.

Our JavaScript file ajax.js will receive the data and check the status bit for success of failure. If updation is successful then display the updated data inside each div tag and show a success message. On failure of udation it displays error message.

Part I : Edit single column of a record

Subscribe to our YouTube Channel here

Jonathan Dusza	10-06-2015
Nice tutorial. Did you have information on using a dropdown list?

skechav	12-09-2015
Years the quality of your content still rocks!!! Rarely I do find such an great explanation of the code presented and its functionality...Many thanks!

Antonis	30-12-2014
Nice very helpfull.

Editing MySQL table row data by PHP & Ajax

Giving Option to Edit all the data of a row.

Selection of radio button

Update data

display-ajax.php

Displaying message and updated record.

Subscribe