issue with a random number being inserted in the place of the right value in a query

[alexandre]

so everything is going well until that insert query where i try to insert the value of $_POST['voucher_code5'] in the collumn voucher_code_in_transaction, everything else is getting inserted well but that code. if i echo the posted code just before the query it gives me the right voucher code but if i let it insert, it will insert always the same number coming from no where... i do not understand what is happening,

<?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: ../phplogin/index.html');
	exit;
}
include '../donation-clash/includes/connect_db2.php';

if (((!isset($_POST['transaction_target'])) || !isset($_POST['voucher_code5'])) || !isset($_POST['voucher_password5'])) {
	exit;
}
else if ((((isset($_POST['transaction_target'])) && isset($_POST['voucher_password5'])) && isset($_POST['voucher_code5'])) && $_SERVER['REQUEST_METHOD'] == "POST") {
if (empty($_POST['voucher_password5'])) {
	// One or more values are empty.
	exit('Please fill the password field');
}
if (empty($_POST['transaction_target'])) {
	// One or more values are empty.
	exit("Please fill user's name field");
}
if (empty($_POST['voucher_code5'])) {
	// One or more values are empty.
	exit("Please fill voucher'code field");
}
if (preg_match('/^[0-9]+$/', $_POST['voucher_code5']) == 0) {
    exit('only numbers are allowed!');
}
}

  $stmt = $con->prepare('SELECT voucher_password, voucher_value, owner_id, owner_name FROM voucher_codes WHERE voucher_code = ? AND voucher_status AND voucher_lock = 0');
$stmt->bind_param('i', $_POST['voucher_code5']);
$stmt->execute();
$stmt->bind_result($voucher_password2, $voucher_value, $owner_id, $owner_name);
$stmt->fetch();
$stmt->close();
if ($voucher_password2 == null) {
  $transaction_allowed = false;
  echo 'verify your voucher and try again';
  exit;
}
elseif (password_verify($_POST['voucher_password5'], $voucher_password2)) {
  $transaction_allowed = true;
}
else {
  echo 'verify your voucher informations and try again';
  exit;
}
if (((($transaction_allowed == true) && $owner_id != $_SESSION['id']) || $owner_name != $_SESSION['name']) || $voucher_value <= 0) {
  $transaction_allowed = false;
  echo ' lil sneaky cannot win on me , because it is me marleau, ciao';
  exit;
}
else if (((($transaction_allowed == true) && $owner_id == $_SESSION['id']) && $owner_name == $_SESSION['name']) && $voucher_value > 0)  {
$stmt = $con->prepare('SELECT id FROM accounts WHERE username = ?');
$stmt->bind_param('s', $_POST['transaction_target']);
$stmt->execute();
$stmt->bind_result($target_id);
$stmt->fetch();
$stmt->close();
  $stmt = $con->prepare('INSERT INTO transactions
(voucher_code_in_transaction,
  request_sender_id,
request_sender_name,
request_receiver_id,
request_receiver_name,
transaction_amount_by_sender) VALUES (?, ?, ?, ?, ?, ?)');
	$stmt->bind_param('iisisd', $_POST['voucher_code5'], $_SESSION['id'], $_SESSION['name'], $target_id, $_POST['transaction_target'], $voucher_value);
	$stmt->execute();
	$stmt->close();
	$stmt = $con->prepare('UPDATE voucher_codes SET voucher_lock = 1 WHERE voucher_code = ?');
	$stmt->bind_param('i', $_POST['voucher_code5']);
	$stmt->execute();
	$stmt->close();
  $stmt = $con->prepare('UPDATE transactions SET transaction_sent = 1 WHERE voucher_code_in_transaction = ?');
	$stmt->bind_param('i', $_POST['voucher_code5']);
	$stmt->execute();
	$stmt->close();
  header('location: transaction_request_sent.php');
  exit;
}

?>

 

[mac_gyver]

what is the number being inserted? do you have error handling for all the database statements that can fail - connection, query, prepare, and execute? are any of the columns in this table defined as unique indexes and could be producing a duplicate index error, that you are not handling? does your table have a datetime field that automatically gets the current datetime so that you would know when the data you see was actually inserted?

BTW - this series of insert/update queries must be part of a transaction, so that you will only commit them if they all succeed without any errors, and will roll them back (or withhold committing them) if there's an error in any of them.

[alexandre]

the voucher code collumn is unique as well as the transaction id and the current time is getting inserted as creation date and another collumn is set on current time on update for when the transaction will be claimed by the targeted user as completion date. for the error handling , i am still trying to learn the best way to do so , i handle errors as they come up when i am testing but as you see the code there, it is running till the insert query, if the password is wrong or is not set i guess would be a result of a failed query i just wrote a error message to see where the code is failing but apart of that i am not sure , i think you talked about naming the exact error code in the error handling and i have no idea about those error code yet. for the number being inserted it is : 

2147483647

while the number that should be inserted should be this:

72414625804506422

i forgot to say that , whatever voucher code i am trying to use for the transaction it is always inserting that same number and is indeed giving a duplicate entry every single time.

Edited November 30, 2022 by alexandre

[Barand]

Looks like you are trying to store a 19 digit number in an INT field with a maximum value of 2147483647. Your voucher code is actually a string that consists of numeric characters (like a phone number) so store it as one, say VARCHAR(20);

[alexandre]

i thought it might be a storage shortage for the data that was supposed to be inserted, so i even was thinking that i could use the numerics and letters for generating the voucher codes , and would store it as a varchar(20). but just like that , if i am using number type for input in the sender page, should it be a int or big int collumn? or it would still work as a varchar collumn even tho this is for number input ?

[Barand]

If you intend calculating a total of the voucher codes or using them in some other arithmetic operation then keep it as a number (BIGINT) but if it's just a code, then it's a string.

[alexandre]

you are right, i dont know why i dont think about those things , it so much make sense.

[alexandre]

thank you for the help , it worked perfectly.