PHP Query subtraction not working

Question

Hello I am trying to subtract a users credit's after a transaction but something keeps going wrong when updating.while testing accepted_bidder = 15 and credit row in customer is 100. for some reason when I update it is -15 rather than 75 anyone know why this may be?

output: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, array given in C:\xampp\htdocs\payment2.php on line 20

Notice: Undefined variable: credit in C:\xampp\htdocs\payment2.php on line 26

<?php
session_start();
require 'config.php';


$id = $_SESSION['login_user'];
$jobid    = $_POST['job_id'];
$poster_id    = $_POST['poster_id'];
$accepted_bidder    = $_POST['accepted_bidder'];
$accepted_bid    = (int) $_POST['accepted_bid'];
$poster_id = $_POST['poster_id'];

$query = "SELECT credit FROM `customer` WHERE email_adress = '$id'";

$success = $conn->query($query);


$result = mysqli_fetch_array($success);

while($row = mysqli_fetch_array($result)):
$credit = (int)$row['credit'];
endwhile;

//var_dump($result);
//var_dump($accepted_bid); 
$updated_credit = $credit - $accepted_bid;
//echo $updated_credit;


$query2   = "UPDATE job SET start_escrow = '1' WHERE job_id = '$jobid'";
$success2 = $conn->query($query2);

$query3   = " UPDATE customer SET credit = '$updated_credit' WHERE email_adress = '$id'";
$success3 = $conn->query($query3);



if (!$success) {
    die("Couldn't enter data: ".$conn->error);

}

echo "Thank You For Contacting Us <br>";
 //header("location: myjobs.php");



$conn->close();




?>

Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Apr 19 '18 at 17:06
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Apr 19 '18 at 17:06
Note: Try and get out of the habit of declaring SQL statements in throw-away variables that are used only once. It's a lot easier to follow code where the query is supplied directly to the function, and there's no longer a chance of messing up and sending in `$sql3` instead of the visually similar `$sql8`. — tadman, Apr 19 '18 at 17:06
This code also has race conditions. When adjusting columns try and do the operation atomically, like `UPDATE customer SET credit=credit-?`, instead of as two queries which can run out of sync. — tadman, Apr 19 '18 at 17:07
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Apr 19 '18 at 17:08
It's highly probable your query failed and I'd bet it's related to the typo: `email_adress` instead of `email_address`. Using email addresses as keys is also an issue here since people can and will change those, and re-keying your database when they do is a giant hassle. — tadman, Apr 19 '18 at 17:09
You're overwriting `$credit` each time through the `while` loop, so it will only have the value from the last row. Is that intentional? Or can the query only return one row? If so, why use a loop? — Barmar, Apr 19 '18 at 17:12

score 3 · Accepted Answer · answered Apr 19 '18 at 17:08

$result is not the result of the query, it's the first row you read using:

$result = mysqli_fetch_array($success);

So when you then do:

while ($row = mysqli_fetch_array($result))

you're trying to use that row as the query result.

Replace these two lines:

$success = $conn->query($query);
$result = mysqli_fetch_array($success);

with

$result = $conn->query($query);

PHP Query subtraction not working

1 Answers1