即使在这个php或sql中没有报告错误，这也不会更新

Question

Here is the page that is supposed to allow a logged in member to transfere an amount they choose to another user on the system i have been working on this for days and found little about it online that actually helps and only found a few snippets here and there that used prepared statements which failed when i edited it so i want to keep it all mysqli for now until i understand prepared statements better or i learn OOP Or PDO but for now i mainly wish to find out why this failes when it sais it works successfully and when i cant see any errors with this code also i have edited this many times so i have tryed various ways but i must be missing something i have also included the db_conx page as a line in this code and the other included line is just to do with checking if a user is logged in, any ideas what may be wrong with this as its not working.

    <form name="username" method="post" action="index.php"> 
    <input type="text" name="username" placeholder="Transfere To Username"/> <br />
    <input type="text" name="amount" placeholder="Amount To Send" style="centered"/><br />
    <input type="submit"  value="continue"/>
    </form>
   <?php
mysqli_report(MYSQLI_REPORT_STRICT);
session_start();
$username = $_SESSION['username'];
echo "<p><br>";
echo "Logged In As $username";
echo "<p><br>";
include_once("../../../php_includes/check_login_status.php");
require("db_conx.php");
if (isset($_POST["username"]) && !empty($_POST["amount"])) {
$username2 = preg_replace('#[^a-z0-9]#i', '', $_POST['username']);
$amount = preg_replace('#[^a-z0-9.]#i', '', $_POST['amount']);
$amount = (int)$amount;
$select_result = ("SELECT balance FROM users WHERE username = '$username' LIMIT 1") or die(mysqli_error($select_result));
$user_query = mysqli_query($db_conx, $select_result);
$numrows = mysqli_num_rows($user_query);
if ($numrows < 1) {
echo "Error Selecting Data Try <a href=\"index.php\">Again </a> Or Go <a href=\"../index.php\">Back</a>";
}
while ($select = mysqli_fetch_array($select_result, MYSQLI_ASSOC)) {
$available_balance = $select["balance"];
}
if($available_balance>=$amount){
$result = ("UPDATE users SET balance = balance - '$amount' WHERE username = '$username' LIMIT 1") or die(mysqli_error($result));
$user_query2 = mysqli_query($db_conx, $result);
$numrows2 = mysqli_affected_rows();
if ($numrows2 < 1) {
echo "Error Updating Data Try <a href=\"index.php\">Again </a> Or Go <a href=\"../index.php\">Back</a>";
}
$result2 = ("UPDATE users SET balance = balance + '$amount' WHERE username = '$username2' LIMIT 1") or die(mysqli_error($result2));
$user_query3 = mysqli_query($db_conx, $result2);
$numrows3 = mysqli_affected_rows();
if ($numrows3 < 1) {
echo "Error Updating Data Try <a href=\"index.php\">Again </a> Or Go <a href=\"../index.php\">Back</a>";
}}
echo "Amount Of $amount Has Been Transferred To $username2";   
} else {  
echo "No Amount Has Been Set, Try Again Or Go <a href=\"../index.php\">Back</a>";
}
?>

Answer 1

• Don't put table names in single quotes. Single quotes are for string literals and date literals.

  SELECT * FROM 'users' WHERE 'username' = $username -- WRONG
  
  SELECT * FROM users WHERE username = '$username' -- BETTER, BUT INSECURE
  
  SELECT * FROM users WHERE username = ? -- BEST
  

  The same is true for your UPDATE statements. Don't quote table names or column names, but do quote strings.

• For good examples of using prepared queries with parameters, the most popular and frequently-referenced post on StackOverflow about this is How can I prevent SQL injection in PHP?

• Check the return value from mysqli_query(). It will be false if there's a problem. If it returns false, then check mysqli_error() to find out what the problem is. For example, putting a quoted string where a table name should be.

  If you don't want to write code after each mysqli_query() to check the return value, you can just configure mysqli to throw an exception when an error occurs. Just do this once at the top of your PHP file, it'll remain in effect for the rest of the script.

  mysqli_report(MYSQLI_REPORT_STRICT);
  

• You should understand the difference between mysqli_num_rows() and mysqli_affected_rows(). mysqli_num_rows() counts the rows in the result set from a SELECT, and it only works after you have fetched the rows. Whereas mysqli_affected_rows() counts the rows changed by an INSERT, UPDATE, or DELETE.

  Checking the result from mysqli_affected_rows(), you may find that your UPDATEs are changing zero rows. This happens either if the WHERE clause matches no rows (that is, there is no user for $username or $username2), or if the SET clause specifies values that result in no change to the column. You should make sure $amount is not 0.