如何从数据库中捕获ID以更新状态？

Question

I'm trying to update the status to 'Approved' from pending by clicking the update button. However when i click on the button it does not respond and change the status. But if I hardcode the ID, the status is able to update. How do I get the ID capture from the database and update the status once click on the button?

//edit1.php:
<?php

    $con= mysqli_connect('127.0.0.1','root','');

    if(!$con)
    {
        echo 'Not Connected To Server';
    }

    if(!mysqli_select_db($con,'satsform1'))
    {
        echo 'Database Not Selected';
    }

    $ID = 'ID';

    $sql = "update handover set status= 'Approved' where ID = '$ID'";

    if(!mysqli_query($con,$sql))
    {
        echo 'Not Submitted';
    }
    else
    {
        echo "<meta http-equiv='refresh' content='0;url=index3.php'>";
    }

?>

<?php
//fetch3.php
$connect = mysqli_connect('127.0.0.1','root','', 'satsform1');
$output = '';
if(isset($_POST["query"]))
{
 $search = mysqli_real_escape_string($connect, $_POST["query"]);
 $query = "
  SELECT * FROM handover 
  WHERE name LIKE '%".$search."%'
  OR staffno LIKE '%".$search."%' 
  OR date LIKE '%".$search."%'
  OR email LIKE '%".$search."%'
  OR mobno LIKE '%".$search."%'
  OR roles LIKE '%".$search."%'
  OR location LIKE '%".$search."%'
  OR flightno LIKE '%".$search."%'
  OR flightdate LIKE '%".$search."%'
  OR seatno LIKE '%".$search."%'
  OR class LIKE '%".$search."%'
 ";
}
else
{
 $query = "
  SELECT * FROM handover ORDER BY ID
 ";
}
$result = mysqli_query($connect, $query);
if(mysqli_num_rows($result) > 0)
{
 $output .= '
  <div class="table-responsive">
   <table class="table table bordered">
    <tr>
     <th>ID</th>
     <th>Staff Name</th>
     <th>Staff Number</th>
     <th> Status </th>
    </tr>
 ';
 while($row = mysqli_fetch_array($result))
 {
  $output .= '
   <tr>
    <td>'.$row["ID"].'</td>
    <td>'.$row["name"].'</td>
    <td>'.$row["staffno"].'</td>

    <td>'.$row["status"].'</td>

    // this is the update button where use the edit1.php to update the status by ID
    <td> <form action="edit1.php" method="GET">
            <button type="submit">update </button>

        </form>
    </td>

   </tr> 
  ';
 }
 echo $output;
}
else
{
 echo 'Data Not Found';
}

?>

I expect the output to be able to update the status to 'Approved' into the database after clicking the update button.

Answer 1

Try this:

//edit1.php:
<?php

    $con= mysqli_connect('127.0.0.1','root','');

    if(!$con) {
        echo 'Not Connected To Server';
    }

    if(!mysqli_select_db($con,'satsform1')) {
        echo 'Database Not Selected';
    }

    if ( isset( $_GET['update_id'] ) && is_numeric( $_GET['update_id'] ) ) {
        $ID = $_GET['update_id'];

        $sql = "update handover set status= 'Approved' where ID = '$ID'";

        if(!mysqli_query($con,$sql)) {
            echo 'Not Submitted';
        }
        else {
            echo "<meta http-equiv='refresh' content='0;url=index3.php'>";
        }
    }

?>

<?php
//fetch3.php
$connect = mysqli_connect('127.0.0.1','root','', 'satsform1');
$output = '';
if(isset($_POST["query"])) {
    $search = mysqli_real_escape_string($connect, $_POST["query"]);
    $query = "
        SELECT * FROM handover
        WHERE name LIKE '%".$search."%'
        OR staffno LIKE '%".$search."%'
        OR date LIKE '%".$search."%'
        OR email LIKE '%".$search."%'
        OR mobno LIKE '%".$search."%'
        OR roles LIKE '%".$search."%'
        OR location LIKE '%".$search."%'
        OR flightno LIKE '%".$search."%'
        OR flightdate LIKE '%".$search."%'
        OR seatno LIKE '%".$search."%'
        OR class LIKE '%".$search."%'
    ";
}
else {
    $query = "
        SELECT * FROM handover ORDER BY ID
    ";
}
$result = mysqli_query($connect, $query);
if(mysqli_num_rows($result) > 0) {
    $output .= '
        <div class="table-responsive">
            <table class="table table bordered">
                <tr>
                <th>ID</th>
                <th>Staff Name</th>
                <th>Staff Number</th>
                <th> Status </th>
                </tr>
    ';
    while($row = mysqli_fetch_array($result)) {
        $output .= '
            <tr>
                <td>'.$row["ID"].'</td>
                <td>'.$row["name"].'</td>
                <td>'.$row["staffno"].'</td>

                <td>'.$row["status"].'</td>

                // this is the update button where use the edit1.php to update the status by ID
                <td>
                    <form action="edit1.php" method="GET">
                        <input type="hidden" name="update_id" value="'.$row["ID"].'">
                        <button type="submit">update </button>
                  </form>
                </td>

            </tr>
        ';
    }
    echo $output;
}
else {
    echo 'Data Not Found';
}

?>

Adding the input field as type=hidden and set the ID in the value of this field. Before update query, I have checked that the id is set or not if it's set only then run the update query otherwise do nothing. You may add else as well as per requirement.