CODEWITHSUNDEEP
phpmysql
user3115724
user3115724

Reputation: 1

Update mysql if statements (PHP)

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

Upvotes: 0

Views: 49

Answers (2)

selesh20
selesh20

Reputation: 9

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

Upvotes: 0

Michel
Michel

Reputation: 4157

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.

Upvotes: 1

Related Questions