MySQL到PHP整数数学

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.

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:

  1. $count = intval($row[0]); sets $count to whatever value from the database.
  2. 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).
  3. $newCount is assigned a value of $count - 1, or -1 in all cases since $count will always be 0.
  4. The query is then executed, UPDATEing the row with, none other than our friend -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!

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 = ?