Why does this UPDATE prepared statement doesn't work, but works when used in SQL db

Question

I have the following statement:

$stmt = $con->prepare("UPDATE accounts SET loggedin = ? WHERE TIMESTAMPDIFF(SECOND,lastcheck, NOW()) >= ? AND loggedin = ?");
$stmt->bind_param('iii', 0, 61, 0);

And it gives 500 internal server error. When I tried the same on phpmyadmin running the sql thing like this:

UPDATE accounts SET loggedin = 0 WHERE TIMESTAMPDIFF(SECOND,lastcheck, NOW()) >= 61 AND loggedin = 1

Worked without any problems.

Where is my problem?

Here's my error:

PHP Fatal error: Uncaught Error: Cannot pass parameter 2 by reference

I'm on a shared hosting and I can't find the log. Give me 1 minute so I contact the support. aaaaaaaaaand support is offline for today — Roberto Carlos, Jan 25 '19 at 22:51
Why do you bind four parameters? You only have preparation for three... — XraySensei, Jan 25 '19 at 22:53
Hello, it's only three ('iii', 0, 61, 0) the ('iii') is to declare that those 3 are ints — Roberto Carlos, Jan 25 '19 at 22:54
It should not make a difference, but you're preparing `0,61,0` but running the equivalent of `0,61,1` via phpmyadmin. ...but "500 internal server error" is an http error, not a MySQL one. — Uueerdo, Jan 25 '19 at 22:55

score 2 · Accepted Answer · answered Jan 25 '19 at 23:02

2

bind_param doesn't take values, it only takes parameters. You need to use variables. Something like:

$stmt->bind_param('iii', $s, $ss, $sss); 
$s = 0;
$ss= 61;
$sss = 0;

should resolve it.

answered Jan 25 '19 at 23:02

user3783243

5,368
5
22
41

Thank you!, this fixed the problem. – Roberto Carlos Jan 25 '19 at 23:03

Why does this UPDATE prepared statement doesn't work, but works when used in SQL db

1 Answers1