-2

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>
Jack
  • 1
  • 3
    You could use a trigger after delete like [this](https://stackoverflow.com/questions/27745023/trigger-to-insert-a-row-into-another-table-on-delete) – Ergest Basha Sep 10 '22 at 17:18
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Sep 10 '22 at 18:48
  • Thanks Ergest Basha & @Dharman. This will be just a locally hosted project and it’s only for a demo. But I highly appreciate your points and will use them in my future projects. – Jack Sep 12 '22 at 11:58
  • It doesn't matter. You should not learn how to write code this way at all. I don't know who taught you this, but it's a really bad thing to learn and you need to unlearn it as soon as possible – Dharman Sep 12 '22 at 12:00

2 Answers2

0

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

AnTh0Ny77
  • 172
  • 7
0

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.

Obedgiu Derrick
  • 11
  • 2