PHP while() function is running my query 3 times

Question

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..

Barmar · Accepted Answer · 2018-01-19 20:52:20Z

3

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();
}

answered Jan 19, 2018 at 20:52

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ctznkane525 · Accepted Answer · 2018-01-19 20:45:40Z

1

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();

edited Jan 19, 2018 at 20:45

answered Jan 19, 2018 at 20:33

Ctznkane525

7,5453 gold badges20 silver badges41 bronze badges

9 Comments

GrumpyCrouton Over a year ago

Perhaps it would be good for you to explain your answer. Why does this work the way it does? How does it solve the problem? Why is OPs code not working as expected?

Ctznkane525 Over a year ago

is that better?

GrumpyCrouton Over a year ago

Much :) I assume with this update you are also much more likely to get upvotes and as such more rep. Thanks for the edit!

Barmar Over a year ago

Mysqli doesn't have named parameters. You're confusing it with PDO.

GrumpyCrouton Over a year ago

Along with what @Barmar said, I believe you are also using bind_param() incorrectly. (I haven't used MySQLi in a while)

|

Collectives™ on Stack Overflow

PHP while() function is running my query 3 times

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related