Is there any difference between SQL running in code vs the MySQL command line?

Question

I have a SQL update statement I am running from inside a PHP program. It is prepared and then executed. When I run it in PHP, it reports a constraint violation. When I run the exact same statement from the command line (which I am getting via dBug()), it works with no errors. This sounds impossible, so I keep looking for differences between the statements. The only thing I can see is that when I execute it with an array of arguments, they all appear to be chars, when some should be integers.

Any suggestions for where to look?

Here is the PHP version (I tweaked it a little to make it more legible):

$st =& $db->prepare("update tbl set uid=?, frequency=? other_id=? where id=$id");
$values = array($uid, $freq, $other_id);
$res =& $db->execute($st, $values);
new dBug ($res);

Answer 1

Probably the PHP client and MySQL client use different charsets.

The user-input data which does not have a counterpart in the client's character table may get converted into ????? which are likely to violate the constraint:

SELECT  CAST('абвгд' AS CHAR CHARSET LATIN1)

---
?????

, 'абвгд' here being Cyrillic characters.

Could you please provide some sample of the data you are trying to insert along with the table defintion?

Answer 2

Sounds improbable - there may be some difference in the SQL that is being run after the variables have been bound. Enable the general query log and compare the executed versions of the statements in the 2 scenarios.

C.

Answer 3

Well, hate to say it, but the problem was with my copy and paste. I was using dBug to show the values and sleep to pause while I read them, but since I could not copy and paste I had to retype the query. And I typo'd one of the values. Thanks for your efforts, but as usual: operator error.