Update mysql if statements (PHP)

Question

Im trying to build an easy textbased game in php to learn php. The problem is how do i UPDATE database table if certain parameters i met. 1 barracks cost 3000 and i want to check if the user got 3000 money or more. Only update then or say you need more money.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phpsamples";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = UPDATE tbl_registered_users
SET     barracks = IF(money = '>3000', barracks + 1)
WHERE   id = 1";

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

UPDATE ... SET barracks = barracks + (money > 3000), money = money - 3000 * (money > 3000) WHERE ... — Akina
– Akina, Commented Jul 2, 2020 at 7:33
Akinas set works, but you can do your code that way, you have to check it prior to update — nbk
– nbk, Commented Jul 2, 2020 at 7:38
@nbk no you don’t have to check upfront, you can also implement that check in the WHERE clause of the UPDATE statement. — C3roe
– C3roe, Commented Jul 2, 2020 at 8:23
he wants to decline the update, if the money isn't enough, that happoens if money is smaller than 300 no baracks is increased, but the update commands will not give an error so if ($conn->query($sql) === TRUE) will not be triggert — nbk
– nbk, Commented Jul 2, 2020 at 9:02

Michel · Accepted Answer · 2020-07-02 09:49:07Z

1

UPDATE table SET
   money = money - 3000,
   barracks = barracks + 1
WHERE id=1 
  AND money>= 3000

This will upp the barracks and reduce the money for user id=1 ONLY if he has 3000 or more money.

To check if the update applied (i.e. if the user indeed has the money) you can use mysqli_affected_rows(), which

Returns the number of rows affected by the last INSERT, UPDATE,REPLACE or DELETE query.

If it returns 0 there was no update because the WHERE clause wasn't met, so you can send the 'not enough money' message.

If it returns 1 the WHERE clause was met and the user has one more barracks.

answered Jul 2, 2020 at 9:49

Michel

4,1554 gold badges39 silver badges57 bronze badges

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

1 Comment

Michel Over a year ago

@RonvanderHeijden You're right, but I assume that if the user doesn't exist he can't login at all so he'll never come to the point of buying something.

Dharman · Accepted Answer · 2020-07-02 16:59:23Z

0

It is better to fetch the specific user by user id. Then check the parameters and update.

$query = $conn->query("select * from tbl_registered_users where id = 1");
if ($query->num_rows > 0) {
    $row = $query->fetch_assoc();
    if($row["money"] > 3000) {
       $conn->query("update tbl_registered_users set barracks = barracks + 1 where id = 1");
    }
}

edited Jul 2, 2020 at 16:59

Dharman♦

34k27 gold badges106 silver badges157 bronze badges

answered Jul 2, 2020 at 8:56

selesh20

91 bronze badge

Collectives™ on Stack Overflow

Update mysql if statements (PHP)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related