Database type int use value for calculation with

Question

I am working (learning) on the CodeIgniter platform. The code is not much different from plain PHP except for the mysql syntax

Problem

A user has a balance of $100. He then signs up for a tournament. The entry fee from the tournament is $50. Thus:

1. Get Balance From DB
2. Subtract 5$0 from value retrieved from DB
3. Update database with new value.

The above should be fairly simple but I am getting strange results.

Games_model

public function get_balance($userID)
    {
        $sql = "SELECT balance FROM users WHERE userID = '$userID'";
        $stmnt = $this->db->query($sql);
        if($stmnt->num_rows() > 0){
           $balance =  $stmnt->result();
           return $balance;
    }
return false;
    }

    public function update_balance($userID){
        //get balance
        $balance = $this->get_balance($userID);
        //charge for pool entry
        $newBalance = (int)$balance - (int)50;
        var_dump($newBalance);
        echo '<h1>'.$newBalance.'</h1>';
        $this->db->set('balance', (int)$newBalance);
        $this->db->where("userID", $userID); //table column field, second argument
        $this->db->update('users');

    }

Games Controller

//Charge for picks #TODO add a check if user has enough funds to enter!!!!!
            echo $this->games_model->update_balance($this->session->userID);
            //Transferring data to Model uploaded
           echo $this->games_model->record_picks($data['picks']);

            $this->load->view('templates/header', $data);
            $this->load->view('games/record_picks', $data);
            //$this->load->view('templates/upcoming_fixtures_tbl', $data['games']);
            $this->load->view('templates/footer', $data);
        }

What I did

The type of balance in the db was of type int() however getting that value and subtracting 50 gave wrong results. I then changed balance field to type varchar() and tried to subtract 50. Still wrong result.

Finally I attempted type casting, as you can see in code above, however it still produces wrong result.

Result I am getting

In this example I get the user's balance which is 150. I then attempt to minus 50 from it. And the result I get back is....-49 which is real strange.

Any help much appreciated.

UPDATE:

I have debugged the method get_balance() and can confirm the correct balance is retrieved. The problem happens in the update_balance() method.

Update 2:

When I try to echo $balance = $this->get_balance($userID); inside the update_balance() method, I get an array to string conversion. So I suspect that is where the problem is.

Severity: Notice Message: Array to string conversion Filename: models/Games_model.php Line Number: 130

Update 3

var_dump() of method get_balance()

array (size=1) 0 => object(stdClass)[41] public 'balance' => string '-49' (length=3)

Answer 1

Hope this will help you :

Note : set your field type int instead of typecasting the balance column in table

return single row instead. your get_balance should be like this :

public function get_balance($userID)
{
  $this->db->select('balance');
  $this->db->from('users');
  $this->db->where('userID',$userID);
  $query = $this->db->get();
  if ($query->num_rows() > 0)
  {
     return $query->row()->balance;
  }

  return false;
}

Your update_balance method should be like this :

public function update_balance($userID)
{
    $balance = $this->get_balance($userID);

    $newBalance = ! empty($balance) ? ($balance - 50) : NULL;
    var_dump($newBalance);
    echo '<h1>'.$newBalance.'</h1>';

    $this->db->where("userID", $userID); 
    $this->db->update('users',['balance' => $newBalance]);
}