Hello Developer, In this tutorial, You will learn to edit and update data in SQL Table with form using php & mysql. Even You will know more to update existing records of an table using MySQLi procedure, MySQLi Object-oriented, PDO & prepared statement with a new concept & an example.
Steps to Edit and Update Data using HTML Form with PHP & MYSQL
In this step, you will learn to update data dynamically with an advanced coding concept that will help you to improve your coding skills for writing smart & standard code in PHP.
Before getting started, make sure that the local server (xamp/wamp) must be installed in your system and start it if it is not being started already.
Learn Also –
Preview an Image before Uploading using PHP
PHP Image Gallery With MySQL Database
You can test yourself to update data with the following folder structure –
codingstatus/
|__database.php
|__table.php
|__form.php
|__developers.php
|1. Connect PHP to MySQL database
You can use the following database connection query to connect PHP to the MySQL database
- $hostName – It contains host name
- $userName – It contains database username
- $password – It contains database password
- $databaseName – It contains database name.
File Name – database.php
<?php
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new mysqli($hostName, $userName, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>3. Create Edit Button in HTML Table
Before creating Edit button , you have insert from data and display them in the HTML Table. You you have not done it, you can do it with the help of the following tutorials –
Insert Data into Database using PHP & MySQL
Display data in HTML table using PHP & MySQL
Then you have to add thr following code in the HTML Table in which you have already show records.
File Name – table.php
<td><a href="form.php?edit=<?php echo $data['id']; ?>" class="btn btn-success">Edit</a></td>
2. Edit and update Data using PHP & MySQLi
Now, go through the next points –
Edit Data –
You have to uderstand the following points to edit data –
- First of all, Include database.php file
- First of all, include a database connection file database.php
- assign $conn to a new variable $db and table name to another variable $table
- access edit id with $_GET[‘edit’] and check it is set or not using isset() method. then implements the next point within if condition. this step will be executed when you click edit button in HTML table.
- validate $_GET[‘edit’] using validate() function and assign it to the $id.
- declare $id in an associative array and assign to the $condition. but array key name must be same as column name i.e id
- call edit_data() function & asiign to the $editData.
edit_data() – This fuction accepts three parameters like $db, $tableName, $column & $condition and It will be executed while you click edit button and return data based on the specified id.
Update Data –
You have to uderstand the following points to update data –
- First of all, define extract($_POST) and $_GET[‘edit’] is set using isset() method. If it is true then implements the next all points within the if condition.
- get input data and declare them into an associative array then assign to the variable $inputData
- access and validate $_GET[‘edit’] then assign to the $id.
- declare $condition in an associative array then assign to the $condition
- call update_data() and assign it to the $result
- After that, redirect to the form.php
update_data() – This fuction accepts three parameters like $db, $tableName, $inputData & $condition and It will be executed while you submit the form after changing the input value. Also, It will update data successfully based on the specified id and return an message.
File Name – developers.php
<?php
include("database.php");
$db= $conn;
$tableName="developers";
if(isset($_GET['edit'])){
$id = validate($_GET['edit']);
$condition= ['id' =>$id];
$columns= ['id', 'fullName','gender','email','mobile', 'address','city','state'];
$editData = edit_data($db, $tableName, $columns, $condition);
}
function edit_data($db, $tableName, $columns, $condition){
if(empty($db)){
$msg= "Database connection error";
}elseif (empty($columns) || !is_array($columns)) {
$msg="columns Name must be defined in an indexed array";
}elseif (!is_array($condition)) {
$msg= "Condition data must be an associative array";
}
elseif(empty($tableName)){
$msg= "Table Name is empty";
}else{
$columnName = implode(", ", $columns);
$conditionData='';
$i=0;
foreach($condition as $index => $data){
$and = ($i > 0)?' AND ':'';
$conditionData .= $and.$index." = "."'".$data."'";
$i++;
}
$query = "SELECT ".$columnName." FROM $tableName";
$query .= " WHERE ".$conditionData;
$result = $db->query($query);
$row= $result->fetch_assoc();
return $row;
if($row== true){
if ($result->num_rows > 0) {
$row= mysqli_fetch_all($result, MYSQLI_ASSOC);
$msg= $row;
} else {
$msg= "No Data Found";
}
}else{
$msg= mysqli_error($db);
}
}
return $msg;
}
// update data
extract($_POST);
if(isset($update) && isset($_GET['edit'])){
$updateDate = date("Y-m-d H:i:s");
$inputData = [
'fullName' => validate($fullName) ?? "",
'gender' => $_POST['gender']?? "",
'email' => validate($email) ?? "",
'mobile' => validate($mobile) ?? "",
'address' => validate($address) ?? "",
'city' => validate($city) ?? "",
'state' => validate($state)?? "",
'created_at' => $updateDate ?? ""
];
$id = validate($_GET['edit']);
$condition= ['id' =>$id];
$result= update_data($db, $tableName, $inputData, $condition);
header("location:form.php");
}
function update_data($db, $tableName, $inputData, $condition){
$data = implode(" ",$inputData);
if(empty($db)){
$msg= "Database connection error";
}elseif(empty($tableName)){
$msg= "Table Name is empty";
}elseif(trim( $data ) == ""){
$msg= "Empty Data not allowed to update";
}elseif(!is_array($inputData) && !is_array($condition)){
$msg= "Input data & condition must be in array";
}else{
// dynamic column & input value
$cv=0;
$columnsAndValue='';
foreach ($inputData as $index => $data) {
$comma= ($cv>0)?', ':'';
$columnsAndValue .= $comma.$index." = "."'".$data."'";
$cv++;
}
// dynamic condition
$conditionData='';
$c=0;
foreach($condition as $index => $data){
$and = ($c>0)?', ':'';
$conditionData .= $and.$index." = "."'".$data."'";
$c++;
}
// update query
$query = "UPDATE ".$tableName;
$query .= " SET ".$columnsAndValue;
$query .= " WHERE ".$conditionData;
$execute= $db->query($query);
if($execute=== true){
$msg= "Data was updated successfully";
}else{
$msg= $query;
}
}
return $msg;
}
function validate($value) {
$value = trim($value);
$value = stripslashes($value);
$value = htmlspecialchars($value);
return $value;
}
?>4 .Update data using HTML Form
Now, you create a new HTML form to update data. But You can use only a single form to insert & update data. So, Here I have used form.php that is created using the following bootsrap 4 CDN.
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
You have to inlcude PHP script file in which you have written code for editing & updating data
<?php include("developers.php"); ?>When you click edit button in HTML table then form.php will be appeard with existing input value based on passing id by the query string.
Input values will be fetched from the SQL table and stored to the variable $editData in the form of an array. So, You will have to get each input data values like the following code
| Input Fields | Input Values |
| Full Name | $editData[‘fullName’] |
| Gender | $editData[‘gender’] |
| $editData[’email’] | |
| Mobile | $editData[‘mobile’] |
| Address | $editData[‘address’] |
| city | $editData[‘city’] |
| State | $editData[‘state’] |
<?php
include("developers.php");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<br><br>
<div class="container">
<div class="row">
<div class="col-sm-4">
<h3 class="text-primary"> HTML Form to Insert Data</h3>
<p><?php echo !empty($result)? $result:''; ?></p>
<!--=== HTML Form==-->
<form method="post" >
<div class="form-group">
<input type="text" class="form-control" placeholder="Full Name" name="fullName" value="<?php echo $editData['fullName']??''; ?>">
</div>
<div class="form-group">
<div class="form-check-inline">
<input type="radio" class="form-check-input" name="gender" value="male" <?php echo isset($editData['gender']) && ($editData['gender']=='male')?'checked':''; ?>>Male
</div>
<div class="form-check-inline">
<input type="radio" class="form-check-input" name="gender" value="female" <?php echo isset($editData['gender']) && ($editData['gender']=='female')?'checked':''; ?>>Female
</div>
</div>
<div class="form-group">
<input type="email" class="form-control" placeholder="Email Address" name="email" value="<?php echo $editData['email']??''; ?>">
</div>
<div class="form-group">
<input type="text" class="form-control" placeholder="Mobile Number" name="mobile" value="<?php echo $editData['mobile']??''; ?>">
</div>
<div class="form-group">
<textarea class="form-control" name="address" placeholder="Address">
<?php echo $editData['address']??''; ?>
</textarea>
</div>
<div class="form-group">
<input type="text" class="form-control" placeholder="City" name="city" value="<?php echo $editData['city']??''; ?>">
</div>
<div class="form-group">
<input type="text" class="form-control" placeholder="State" name="state" value="<?php echo $editData['state']??''; ?>">
</div>
<button type="submit" name="<?php echo empty($editData)?'save':'update'; ?>" class="btn btn-primary">Save</button>
</form>
<!--=== HTML Form=== -->
</div>
</div>
</div>
</body>
</html>
5. Test Yourself to update data
After Implementing previous steps, You can test it yourself to open the following URL in your web browser
https://localhost/codingstatus/table.php
More methods to update data using PHP & MYSQL
Update data using MySQLi Procedure
Edit data using MySQLi Procedure –
<?php
include('database.php');
if(isset($_GET['edit']))
{
$id = $_GET['edit'];
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers WHERE id= $id";
$result = mysqli_query($conn, $query);
$editData= mysqli_fetch_assoc($result);
$fullName = $editData['fullName'];
$email = $editData['email'];
$gender = $editData['gender'];
$mobile = $editData['mobile'];
$address = $editData['address'];
$city = $editData['city'];
$state = $editData['state'];
}
?>Update data using MySQLi Procedure
<?php
if(isset($_POST['update']) && isset($_GET['edit'])){
$id = $_GET['edit'];
$fullName= $_POST['fullName'];
$gender= $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address =$_POST['address'];
$city =$_POST['city'];
$state =$_POST['state'];
$query = "UPDATE developers SET
fullName='$fullName',
gender = '$gender',
email = '$email',
mobile = '$mobile',
address = '$address',
city = '$city',
state = '$state'
WHERE id = '$id'";
$execute= mysqli_query($conn, $query);
if($execute== true){
header("location:display-data.php");
}else{
echo $conn->error;
}
echo $conn->error;
}
?>Update Data using MySQLi Object-oriented
Edit Data using MySQLi Object-oriented
<?php
if(isset($_GET['edit']))
{
$id = $_GET['edit'];
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers WHERE id= $id";
$result = $conn->query($query);
$editData= $result->fetch_assoc();
$fullName = $editData['fullName'];
$email = $editData['email'];
$gender = $editData['gender'];
$mobile = $editData['mobile'];
$address = $editData['address'];
$city = $editData['city'];
$state = $editData['state'];
}update data using MySQLi Object-oriented
<?php
// update data
if(isset($_POST['update']) && isset($_GET['edit'])){
$id = $_GET['edit'];
$fullName= $_POST['fullName'];
$gender= $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address =$_POST['address'];
$city =$_POST['city'];
$state =$_POST['state'];
$query = "UPDATE developers SET
fullName='$fullName',
gender = '$gender',
email = '$email',
mobile = '$mobile',
address = '$address',
city = '$city',
state = '$state'
WHERE id = '$id'";
$execute= $conn->query($query);
if($execute== true){
header("location:display-data.php");
}else{
echo $conn->error;
}
echo $conn->error;
}
?>Update Data Using Prepared Statement & MySQLi
Edit data using prepared statement & MySQLi
<?php
if(isset($_GET['edit']))
{
$id = $_GET['edit'];
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers WHERE id= ?";
$prepared = $conn->prepare($query);
$prepared->bind_param('i', $id);
$prepared->execute();
$result= $prepared->get_result();
$editData= $result->fetch_assoc();
$fullName = $editData['fullName'];
$email = $editData['email'];
$gender = $editData['gender'];
$mobile = $editData['mobile'];
$address = $editData['address'];
$city = $editData['city'];
$state = $editData['state'];
}
?>Update data using prepared statement & MySQLi
<?php
// update data
if(isset($_POST['update']) && isset($_GET['edit'])){
$id = $_GET['edit'];
$fullName= $_POST['fullName'];
$gender= $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address =$_POST['address'];
$city =$_POST['city'];
$state =$_POST['state'];
$query = "UPDATE developers SET
fullName='$fullName',
gender = '$gender',
email = '$email',
mobile = '$mobile',
address = '$address',
city = '$city',
state = '$state'
WHERE id = ?";
$prepared = $conn->prepare($query);
$prepared->bind_param('i', $id);
$execute= $prepared->execute();
if($execute== true){
header("location:display-data.php");
}else{
echo $conn->error;
}
echo $conn->error;
}
?>