I want to update a record from Transactions table and post it to Approved table whilst updating the status of the Transaction approval from 0 to 1

Question

pending_approvals.php

<table id="example" class="display" style="width:100%">
    <thead>
      <tr>
        <th>Transaction ID</th>
        <th>Agent Email</th>
        <th>Sender</th>
        <th>Receiver</th>
        <th>Amount</th>
        <th>Actions</th>
      </tr>
    </thead>
    <tbody>
      <?php
          $trans = "SELECT * FROM transactions";
          $get_trans = mysqli_query($db, $trans);
          while($row = mysqli_fetch_array($get_trans)){?>
          <tr>
             <td><?php echo $row['transaction_id']; ?></td>
             <td><?php echo $row['agent_email'] ;?></td>
             <td><?php echo $row['sender']; ?></td>
             <td><?php echo $row['receiver']; ?></td>
             <td><?php echo $row['amount']; ?></td>
             <td>
               <a class="btn btn-success ti-eye" href="view_transaction.php?transaction_id=<?php echo $row['transaction_id']; ?>"></a>
               <a class="btn btn-info" href="approve.php?transaction_id=<?php echo $row['transaction_id']; ?>"
               onclick="return confirm('Are you sure you would like to confirm this transaction ?');"> Approve</a>
             </td>
          </tr>
      <?php } ?>
   </tbody>
</table>

approve.php

<?php 
include('config.php');
include_once('_header.php');

    $transaction_id = $_GET['transaction_id'];
    $approve = "INSERT INTO approvals SELECT * FROM transactions WHERE transaction_id = '$transaction_id'";
    $run = mysqli_query($db, $approve);
    if($approve){
        $approved = "UPDATE approvals SET approved = TRUE WHERE transaction_id = '$transaction_id'";
        $run = mysqli_query($db, $approve);
    }
?>

<?php include_once('_footer.php'); ?>

The transaction & approval tables are similar and my query is just inserting the data from the transaction table but not updating the approved column from 0 to 1 in the approval table

Answer 1

Firstly, as Magnus Eriksson mentioned, you are checking if $approve is essentially not false or null and, therefore, it will always pass as true because you are setting $approve to be the SQL query (as a string). I think you are meaning to check if ($run), as Magnus mentioned. Secondly, your INSERT INTO sql statement is incorrect syntax. The syntax should be like so:

INSERT INTO table ({column_name}, {another_column_name}) VALUES ({value}, {value});

You don't need a WHERE clause in an insert as inserting will essentially append a record to your table (and won't check to see if a condition is true in the case of how you are utilizing it) and the SELECT statement cannot be embedded into your INSERT statement in the way you tried to do it (I am not quite sure why you were trying to SELECT data there, so I am going to assume you were just trying to insert the transaction_id and, therefore, give you a valid INSERT sql statement that inserts that data--which I will demonstrate here in a bit). Thirdly, your UPDATE statement utilizes a TRUE value: please double check that the data type for the column was created as a boolean, otherwise that shouldn't work (also make sure boolean data types are even possible as I don't believe SQL Server or MySQL have that data type--they use BIT or TINYBIT, but I could be mistaken on that). Fourthly, I would like to point out a bigger problem with your code: it is SQL injection prone. If you go to the official PHP documentation on mysqli_query(), you will notice a big red warning message describing how mysqli_query() should never be used if you are inserting parameters (such as your $transaction_id). Someone with malicious intent could send a malicious value for your 'transaction_id' GET parameter and successfully SQL inject content. To fix this, I would suggest data binding your parameters, like so:

    // Make a connection to the DB:
    $conn = new mysqli($db_server_name, $db_username, $db_pswd, $db_name);

    // Check if connection was successful:    
    if ($conn->connect_error){
         die("Connection failed");
    }

    $sql = "INSERT INTO approvals SELECT * FROM transactions WHERE transaction_id = ?;";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $transaction_id);
    $stmt->execute();

    // See if it inserted successfully:
    if ($stmt->affected_rows > 0){
        // Success!
    } else {
        // Failed... ):
    }
    
    // Close connections:
    $stmt->close();
    $conn->close();

Binding your parameters with bind_param() ensures that they are treated as a string and thereby never treated as SQL (so I would also suggest binding your update statement as well).