issue with a update query not updating

[alexandre]

i need an outside look on this , there is 3 update queries running one after the others.  the only query not processing is the one to update the user's balance and total received since it is the same query. here is the code

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
session_start();
// If the user is not logged in redirect to the login page...
if (!isset($_SESSION['loggedin'])) {
	header('Location: index.html');
	exit;
}
include '../donation-clash/includes/connect_db2.php';

if (!isset($_POST['voucher_code_in_transaction'])) {
  echo 'sorry it seems like there is no voucher code in this transaction';
	exit;
}
else if (isset($_POST['voucher_code_in_transaction']) && $_SERVER['REQUEST_METHOD'] == "POST") {
$stmt = $con->prepare('SELECT request_receiver_id, request_receiver_name FROM transactions WHERE voucher_code_in_transaction = ?');
$stmt->bind_param('s', $_POST['voucher_code_in_transaction']);
$stmt->execute();
$stmt->bind_result($request_receiver_id, $request_receiver_name);
$stmt->fetch();
$stmt->close();

if (($request_receiver_id == $_SESSION['id']) && $request_receiver_name == $_SESSION['name']) {
  $stmt = $con->prepare('SELECT userbalance, total_pifcoin_received FROM accounts WHERE id = ?');
  $stmt->bind_param('i', $_SESSION['id']);
  $stmt->execute();
  $stmt->bind_result($userbalance, $total_pifcoin_received);
  $stmt->fetch();
  $stmt->close();
  $stmt = $con->prepare('SELECT voucher_value FROM voucher_codes WHERE voucher_code = ?');
  $stmt->bind_param('s', $voucher_code_in_transaction);
  $stmt->execute();
  $stmt->bind_result($voucher_value);
  $stmt->fetch();
  $stmt->close();
  $userbalance = $userbalance + $voucher_value;
  $total_pifcoin_received = $total_pifcoin_received + $voucher_value;
  $stmt = $con->prepare("UPDATE accounts SET userbalance = ?, total_pifcoin_received = ? WHERE id = ?");
  $stmt->bind_param('ddi', $userbalance, $total_pifcoin_received, $_SESSION['id']);
  $stmt->execute();
  $stmt->close();
  $stmt = $con->prepare('UPDATE voucher_codes SET voucher_value = 0, voucher_status = 0 WHERE voucher_code = ?');
  $stmt->bind_param('s', $_POST['voucher_code_in_transaction']);
  $stmt->execute();
  $stmt->close();
  $stmt = $con->prepare('UPDATE transactions SET transaction_status = 0, transaction_lock = 1, transaction_accepted = 1 WHERE voucher_code_in_transaction = ?');
  $stmt->bind_param('s', $_POST['voucher_code_in_transaction']);
  $stmt->execute();
  $stmt->close();
  header('location: transaction_success.php');
  exit;
}
else {
  exit;
}
}
 ?>

i might be missing something , thats why i am asking for an outside look . 

[requinix]

Hint: It might not so much be that the query isn't working, but that the query is working except it's not actually changing the data...

[alexandre]

the variables containing the changed data are defined, it is two simple variables just above the first update query, where i add the value of of the voucher to the initialy fetched variables . until now i have been using this method to do this but i dont know right now it doesnt want to ..

[requinix]

Good so far. How do you get the value of that voucher? Because if, for some reason, you didn't happen to have a value for it, you'd be adding 0 to the balance and total received, thus making it look like nothing happened...

[alexandre]

i made the claiming system like this :

i added a button to the rows of data displayed and for each row the button have a hidden input with as value the voucher code of this row so,  when you press claim, you are sent to the claiming process page where the voucher value is pulled using the voucher code sent by hidden input. this code is unique so it can be targeted easily

Edited December 2, 2022 by alexandre

[requinix]

I meant in the code.

Good news: I've had my fun.

$stmt = $con->prepare('SELECT voucher_value FROM voucher_codes WHERE voucher_code = ?');
$stmt->bind_param('s', $voucher_code_in_transaction);

That query is not returning any results.

[alexandre]

i forgot the code where the pagination is done here :

<?php
include '../donation-clash/includes/connect_db1.php';
require_once 'pagination_clash.css';
require_once '../donation-clash/donation-clash0.css';
$pdo = pdoConnect();

const PER_PAGE = 15;

$page = $_GET['page'] ?? 1;

$offset = ($page - 1) * PER_PAGE;

################################################################################
#  DETERMINE NUMBER OF PAGES                                                   #
################################################################################
    $res = $pdo->query("SELECT COUNT(*)
                        FROM transactions
                       ");
    $total_pages = ceil($res->fetchColumn()/PER_PAGE);


################################################################################
#  CREATE PAGE LINKS                                                           #
################################################################################
    $prev = $page==1 ? 1 : $page - 1;
    $next = $page==$total_pages ? $total_pages : $page + 1;
    $prev_vis = $page==1 ? 'hidden' : 'visible';
    $next_vis = $page==$total_pages ? 'hidden' : 'visible';

    $pagelinks = "<a href='?page=$prev' class='page-link' style='visibility:$prev_vis'><i class='fa fa-caret-left'></i></a>\n";
    for ($p=1; $p<=$total_pages; $p++) {
        $current = ($p == $page) ? 'current-page' : '';
        $pagelinks .= "<a href='?page=$p' class='page-link $current' >$p</a>\n";
    }
    $pagelinks .= "<a href='?page=$next' class='page-link' style='visibility:$next_vis'><i class='fa fa-caret-right'></i></a>\n";


################################################################################
#  CREATE TABLE OF RANKED DATA                                                 #
################################################################################
    $sql = "
            SELECT  voucher_code_in_transaction, request_sender_name, request_receiver_name, transaction_amount_by_sender, creation_date, completion_date
            FROM transactions
            WHERE
             request_receiver_id = ?
             AND
            transaction_status = 1
             ORDER BY creation_date DESC
            LIMIT ?, ?
            ";
    $res = $pdo->prepare($sql);
    $res->execute([ $_SESSION['id'], $offset, PER_PAGE ]);

    $ranked_data = "";
    foreach ($res as $row) {
				$ranked_data .= "<tr>
        <th>transaction initiator</th><th>initiator amount</th><th>request receiver</th><th>transaction creation date</th><th>claim transaction</th>
        </tr>\n";
         $ranked_data .= "


             <tr>

                 <td class='remain1'>" . $row['request_sender_name']. "</td>
                 <td class='remain2'>" . $row['transaction_amount_by_sender']. "</td>
                 <td class='remain4'>" . $row['request_receiver_name']. "</td>

                 <td class='remain1'>" . $row['creation_date']. "</td>

<td> <form action='tran_claim.php' method='post' enctype='multipart/form-data'><input class='input_placeholder2' type='hidden' name='voucher_code_in_transaction' value='". $row['voucher_code_in_transaction'] . "'>
<button class='voucher_submit2' type='submit' formenctype='text/plan' formmethod='POST'>claim transaction</button>
</form></td>
             </tr>

        \n";
     }

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Pagination Sample</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="../phplogin/style.css" type="text/css">
</head>
<body>

        <h1>all your transactions received in pending</h1>

    <div class='pagenav'>
        <?=$pagelinks?>
    </div>
    <div class="rankingtrue2">
		<div class='ranking_wrapper'>
			 <table class='rankingtable3'>
            <?= $ranked_data ?>
        </table>
    </div>
	</div></div>
</body>
</html>

i used the pagination model that barand provided me.

[alexandre]

1 minute ago, requinix said:

I meant in the code.

Good news: I've had my fun.

$stmt = $con->prepare('SELECT voucher_value FROM voucher_codes WHERE voucher_code = ?');
$stmt->bind_param('s', $voucher_code_in_transaction);

That query is not returning any results.

how is that ? 

[requinix]

Almost had my fun.

This query works, right?

$stmt = $con->prepare('SELECT request_receiver_id, request_receiver_name FROM transactions WHERE voucher_code_in_transaction = ?');
$stmt->bind_param('s', $_POST['voucher_code_in_transaction']);

There's a big difference between the two of them.

[alexandre]

yup this one works or it would not go through the rest of the code because of the condition right after

[alexandre]

in fact all other queries works perfectly vouchers and transactions are getting updated only the accounts query is stalling there. if i was setting a condition that avoid continuing the script if the accounts query isnt successful , i am pretty sure it would stop there

[requinix]

This might be one of those times when you should step away from the computer for an hour or so, get something to eat, watch TV, and come back to the code with a fresh pair of eyes.

As a reminder, this works:

$stmt = $con->prepare('SELECT request_receiver_id, request_receiver_name FROM transactions WHERE voucher_code_in_transaction = ?');
$stmt->bind_param('s', $_POST['voucher_code_in_transaction']);

and this does not work:

$stmt = $con->prepare('SELECT voucher_value FROM voucher_codes WHERE voucher_code = ?');
$stmt->bind_param('s', $voucher_code_in_transaction);

 

[alexandre]

i just dont understand why it would not be working since i use the same setup to update the voucher table down below and it is getting updated, i could be using the value from the transaction of the transaction table but i preferred opting for the direct source of the funds instead of a kind of copy of the data.

 

[requinix]

Okay, now I'm done.

$stmt->bind_param('s', $voucher_code_in_transaction);

Where is the $voucher_code_in_transaction variable coming from?

[alexandre]

but you are right this query effectively returns nothing ... i am lost again 😅

[alexandre]

1 minute ago, requinix said:

Okay, now I'm done.

$stmt->bind_param('s', $voucher_code_in_transaction);

Where is the $voucher_code_in_transaction variable coming from?

this variable is in in the transaction table , it is the collumn holding the voucher codes linked with their transaction

[requinix]

I'm not talking about a column in a table. I'm talking about the PHP variable you have there called $voucher_code_in_transaction. Where is the line of PHP code that declares that variable?

[alexandre]

dang thank you i dont know what i was thinking , and i might be tired .. how can someone be so blind 😂

[requinix]

Like I said, sometimes you need to step away from the computer for a while. I really did mean to do that, you know. Wasn't even joking.

[alexandre]

i believe you now, it is just hard to get away from the laptop before i have fixed my issue, if i do go away i just feel like i am wasting time .. but all in all i appreciated your help.😄