MySQL to PHP Integer Math

Question

I have a MySQL table with an integer column in it called count which I'm trying to do some basic math on and then update the value. Here is an example of one such task I'm trying to do.

$sql = 'SELECT count FROM triggers WHERE id = ?';
$params = array($id);
$result = sqlsrv_query($connection, $sql, $params);

$row = sqlsrv_fetch_array($result);

$count = intval($row[0]);
if ($count = 0)
   return -1;
$newCount = $count - 1;

$sql = 'UPDATE triggers SET count = ? WHERE id = ?';
$params = array($newCount, $id);
$result = sqlsrv_query($connection, $sql, $params);

The problem I'm having is that $newCount always equals -1, so the database value of count is updated to -1. In one particular example run, $count was equal to 84, so it passed the if statement. But then instead of $newCount being set to 83, it gets set to -1.

I have a feeling this has something to do with the MySQL value being treated as a string instead of an integer, but I thought intval() would solve that. I also tried using (int) instead, but no difference.

It's possible that count is a reserved word, and needs backticks in your query (this making it fail and be "false" which is casted to 0, then 1 is subtracted from it). Also, I'd recommend you attempt to update the value directly in the query. Example: http://stackoverflow.com/questions/16277339/decrement-value-in-mysql-but-not-negative — John V., Feb 13 '17 at 05:49

lc. · Accepted Answer · 2017-02-13T06:01:05.863

Your problem is if ($count = 0). In PHP, = is the assignment operator and == is the equality comparison operator.

tl;dr: You meant if ($count == 0).

(Or if $count === 0. Although since $count is guaranteed to be an integer here, it won't really matter in this case. Using ===, and only explicitly using == where you intend to invoke type fiddling may also be a good habit to get into.)

Explanation: Let's step through your code. What's happening here is:

$count = intval($row[0]); sets $count to whatever value from the database.
if ($count = 0) assigns the value of 0 to $count, regardless of anything else. It then tests this value of 0 which is falsey, causing the if statement block to be skipped and return -1 is never executed. This is the same as would happen if you directly wrote if (0).
$newCount is assigned a value of $count - 1, or -1 in all cases since $count will always be 0.
The query is then executed, UPDATEing the row with, none other than our friend -1.

score 1 · Answer 2 · answered Feb 13 '17 at 05:54

1

The line

if ($count = 0)

should read

if ($count === 0)

This is because you are assigning the value rather than using a comparison operator. Here is some useful reading regarding operators in php http://www.w3schools.com/php/php_operators.asp Hope that helps!

answered Feb 13 '17 at 05:54

Inkdot

266
1
6

1

Just to note the difference between the two operators suggested in the above answers, == will return true after type juggling (string, int, double, bool etc.. values) while === will return true only if they are of the same type (in this case integer). – Inkdot Feb 13 '17 at 06:03

score 1 · Answer 3 · answered Feb 13 '17 at 06:30

Your mistake is well defined by Ic in your answer list.

I think your code is little bit longer one, It can be done with shorter form with in one query instead of imposing two queries- first select and get count value and update it, you can achieve this by single query like below-

UPDATE  triggers
    // if is like ternary operator condition, true value , false value 
    SET count = IF(count > 0, count - 1, count)
WHERE   id = ?

MySQL to PHP Integer Math

3 Answers3