skip to Main Content

I have data in a table called "assignedwork_tb". I want to delete the entry from "assignedwork_tb" table which is equal to the current session’s "Request_ID" (Request_ID is the primary key of "assignedwork_tb" table). In the same time, I want to INSERT that deleted entry INTO another table called "completed_tb" table. The linking parameter should be "Request_ID" All tables are in same SQL database.
I tried the following method but it wasn’t successful. Can anyone please help me? Thanks in advance.

define('TITLE', 'Assigned Work Orders');
define('PAGE', 'work');
include('includes/header.php'); 
include('../dbConnection.php');
session_start();
 if(isset($_SESSION['is_login'])){
  $tEmail = $_SESSION['tEmail'];
 } else {
  echo "<script> location.href='Login.php'; </script>";
 }
?>
<div class="col-sm-9 col-md-10 mt-5">
  <?php 
 $sql = "SELECT * FROM assignedwork_tb";
 $result = $conn->query($sql);
 if($result->num_rows > 0){
  echo '<table class="table">
  <thead>
    <tr>
      <th scope="col">Req ID</th>
      <th scope="col">Request Info</th>
      <th scope="col">Name</th>
      <th scope="col">Address</th>
      <th scope="col">City</th>
      <th scope="col">Mobile</th>
      <th scope="col">Employer</th>
      <th scope="col">Assigned Date</th>
      <th scope="col">Action</th>
    </tr>
  </thead>
  <tbody>';
  while($row = $result->fetch_assoc()){
    echo '<tr>
    <th scope="row">'.$row["request_id"].'</th>
    <td>'.$row["request_info"].'</td>
    <td>'.$row["requester_name"].'</td>
    <td>'.$row["requester_add2"].'</td>
    <td>'.$row["requester_city"].'</td>
    <td>'.$row["requester_mobile"].'</td>
    <td>'.$row["assign_trans"].'</td>
    <td>'.$row["assign_date"].'</td>
    <td><form action="viewassignwork.php" method="POST" class="d-inline"> <input type="hidden" name="id" value='. $row["request_id"] .'><button type="submit" class="btn btn-warning" name="view" value="View"><i class="far fa-eye"></i></button></form>
    <form action="" method="POST" class="d-inline"> <input type="hidden" name="id" value='. $row["request_id"] .'><button type="submit" class="btn btn-secondary" name="delete" value="Delete"><i class="far fa-trash-alt"></i></button></form>
    <form action="" method="POST" class="d-inline"><input type="hidden" name="id" value='. $row["request_id"] .'><button type="submit" class="btn btn-success" name="completed" value="Completed"><i class="far fa-check-square"></i></button></form>
    </td>
    </tr>';
   }
   echo '</tbody> </table>';
  }else {
    echo "0 Result";
  }
  if(isset($_REQUEST['delete'])){
    $sql = "DELETE FROM assignedwork_tb WHERE request_id = {$_REQUEST['id']}";
    if($conn->query($sql) === TRUE){
      // echo "Record Deleted Successfully";
      // below code will refresh the page after deleting the record
      echo '<meta http-equiv="refresh" content= "0;URL=?deleted" />';
      } else {
        echo "Unable to Delete Data";
      }
    }
    if(isset($_REQUEST['completed'])){
      $sql5 = "DELETE FROM assignedwork_tb WHERE request_id = {$_REQUEST['id']} OUTPUT [deleted]. (`rno`, `request_id`, `request_info`, `request_desc`, `requester_name`, `requester_add1`, `requester_add2`, `requester_city`, `requester_state`, `requester_zip`, `requester_email`, `requester_mobile`, `assign_trans`, `assign_date`, `rImage`, `orifilename`) INTO completed_tb (`rno`, `request_id`, `request_info`, `request_desc`, `requester_name`, `requester_add1`, `requester_add2`, `requester_city`, `requester_state`, `requester_zip`, `requester_email`, `requester_mobile`, `assign_trans`, `assign_date`, `rImage`, `orifilename`)";
      if($conn->query($sql5) === TRUE){
        // echo "Record Deleted Successfully";
        // below code will refresh the page after deleting the record
        echo '<meta http-equiv="refresh" content= "0;URL=?completed" />';
        } else {
          echo "Unable to Mark as Completed";
        }
      }
  ?>
</div>
</div>
</div>

2

Answers


  1. Use prepare statement fisrt .

    Maybe you should use trigger or just make the reverse : SELECT and INSERT then DELETE ^^

    Be sure you dont use Foreign key between the 2 tables because you won’t save anything with a deleted key in this case

    Login or Signup to reply.
  2. You can’t delete and insert what’s no longer there. You would rather insert the record in the completed_tb then delete it from the other table based on the id you mentioned.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search