CODEWITHSUNDEEP
phpmysqlmysqli
krisz44g
krisz44g

Reputation: 61

PHP while() function is running my query 3 times

My problem is that: I have a users table in MySQL. I made a query with MySQLi which looks like:

if($stmt = $mysqli->prepare("SELECT condition,name,money FROM users WHERE fbid = ?")){

 $stmt->bind_param('s',$_SESSION['FBID']);

 $stmt->execute();

 $stmt->store_result();

 $num_of_rows = $stmt->num_rows;

 $stmt->bind_result($condition,$name,$money);

 while ($stmt->fetch()) {

And here's my problem, because I want different users to add equal usernames to their ids. So, my code is the following:

if($_GET['name']!='' && $money>'500'){
$stmt2 = $mysqli->prepare("UPDATE users SET `condition` = `condition` + 5, `money` = `money` - 5 WHERE fbid = ? AND name = ?");
 $stmt2->bind_param("ss", $_SESSION['FBID'],$_GET['name']);
 $stmt2->execute();
 $stmt2->close();

I want to update only that value in the database where user ID = $_SESSION[fbid] and name = $_GET[name]. So if I have an account with id 1922838445 and I have three names, for example, John, Joe, and Jill and $_GET[name]=='Joe' then update only that value at the same ids. It works until that point that update only the got value, but it does that 3 times... Because of while () maybe??? How can I fix it?

The two code samples have to come one after!! Because of checking the value of money..

Upvotes: 3

Views: 81

Answers (2)

Barmar
Barmar

Reputation: 780713

There's no need for the first SELECT and the loop, just put the condition on money into the UPDATE query itself.

if ($_GET['name'] != '') {
    $stmt = $mysqli->prepare("
        UPDATE users 
        SET condition = condition + 5, money = money - 5
        WHERE fbid = ? AND name = ?
        AND money > 500")
    $stmt->bind_param("ss", $_SESSION['FBID'], $_GET['name']);
    $stmt->execute();
}

Upvotes: 3

Ctznkane525
Ctznkane525

Reputation: 7465

Try this instead to get only one record:

It gets only the record you need because it has a WHERE clause on both parameters that you plan to update, instead of only a partial match on the fbid like you had before.

Before you were getting 3 records because you had a partial key search, then looping through the records and updating the same record over and over against, regardless of the value of the second part of the key in the record you were looping against.

if($stmt = $mysqli->prepare("SELECT condition,name,money FROM users WHERE fbid = ? AND name = ?")){

 $stmt->bind_param("ss", $_SESSION['FBID'],$_GET['name']);

 $stmt->execute();

Upvotes: 1

Related Questions