Trying to subtract amount from column in database, showing amount on all accounts

Question

Objective:

I am trying to subtract 0.05 from a column in my database on the push of an html button.

---

What's going wrong:

Everything is working fine, it is making the correct calculation and deleting the correct amount from my account, but it is displaying the new amount on every account in the database.

---

Example:

I have 10 in my account, and once i press the button, i now have 9.95 in my account. Unfortunately, now everyone has 9.95 in their accounts too!

---

Code:

Html button and script for onclick:

<button id="playbutton" onclick="myAjax();location.href='5game.html'">Play (-5&#162;)</button>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script type = "text/javascript">
function myAjax () {
$.ajax( { type : 'POST',
            data : { },
            url  : 'subtract5.php',              // <=== CALL THE PHP FUNCTION HERE.
            success: function ( data ) {
                alert( data );               // <=== VALUE RETURNED FROM FUNCTION.
            },
            error: function ( xhr ) {
                alert( "error" );
            }
        });
}
</script>

PHP Code for subtracting 0.05 (file name: subtract5.php):

<?php
session_start();

$servername = "localhost";
$username = "my username"; <not actually this, just confidential
$password = "my password"; <not actually this, just confidential
$dbname = "accounts";
$cash_amount = $_SESSION['cash_amount'];

// Create connection

$userid = $_SESSION['id'];

// You must enter the user's id here. /\

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch the existing value of the cash_amount against that particular user here. You can use the SELECT cash_amount from users where userid = $userid

$newAmount = $cash_amount - 0.05;

$sql = "UPDATE users SET cash_amount = $newAmount";
$result = $conn->query($sql);

if($result)
{
   echo "Query Executed Successfully!";
}
else
{
   echo mysqli_error($conn);
}

$conn->close();
?>

---

Prediction:

I think this has something to do with the user id field, i wasn't sure what to put in it.

---

Database layout: Database: Accounts, Table: Users, Columns: id | first_name | last_name | email | password | cash_amount | hash | active

---

Extra Question:

On the main page of my website, it shows the cash amount. Once it has been changed it does not update until i log out and log back in again. Is there a thing i can put in the top php code like "session restart" to check for new amounts every time i open the page?

---

Thanks:

Thanks so much for helping, as you can see i'm kind of a noob at this :)

Answer 1

You need to limit the update to only one user using a WHERE clause

$sql = "UPDATE users SET cash_amount = $newAmount WHERE user_id = $userid LIMIT 1";

The LIMIT is added for security.

Answer 2

In the where clause of the query WHERE id = $userid:

<?php
session_start();

$servername = "localhost";
$username = "my username"; <not actually this, just confidential
$password = "my password"; <not actually this, just confidential
$dbname = "accounts";
$cash_amount = $_SESSION['cash_amount'];

// Create connection

$userid = $_SESSION['id'];

// You must enter the user's id here. /\

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch the existing value of the cash_amount against that particular user here. You can use the SELECT cash_amount from users where userid = $userid

$newAmount = $cash_amount - 0.05;

$sql = "UPDATE users SET cash_amount = $newAmount WHERE id = $userid";
$result = $conn->query($sql);

if($result)
{
   echo "Query Executed Successfully!";
}
else
{
   echo mysqli_error($conn);
}

$conn->close();
?>