Update Data in SQL Table with Form Using PHP & MYSQL

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.

update data using php & mysql

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’]
Email$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;

}

?>