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
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
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.