0

I tried to use CAST(column_name AS BINARY) in a PDO SELECT query and got a syntax error "PHP Parse error". I think the problem is in the last statement but do not know how to fix it. What should I do to correct this error?

DB:

tblrecord (firstname, lastname, score)

PHP:

$firstname = trim('Mary');

$lastname = trim('Lamb');

$sql = "SELECT * FROM tblrecord WHERE CAST(TRIM(firstname) AS BINARY) = ? AND CAST(TRIM(lastname) AS BINARY)  = ?";

$stmt = $connection->prepare($sql);

$stmt->execute( array( CAST($firstname AS BINARY), CAST($lastname AS BINARY) ) );
Rachel Smiths
  • 155
  • 11
  • 3
    `CAST(expression AS TYPE)` is a MySQL function, not a PHP function. Looks like you want `(binary)$firstname`/`(binary)$lastname` per https://www.php.net/manual/en/language.types.type-juggling.php – Sean Aug 21 '19 at 00:23
  • @Sean comment is your solution, but why are you casting to `BINARY`? – Nick Aug 21 '19 at 02:00
  • @Nick because the collation of varchar in the database was set incorrectly as latin1_swedish_ci (I cannot change this because it will involve many related changes) and the string from PHP is utf8 so when comparing a string with special characters I can't do a straight comparison and have to use binary comparison. – Rachel Smiths Aug 21 '19 at 17:06
  • @Sean Thank you. It solves the problem. I did a search before but didn't see that I could use (binary) as binary casting in PHP. – Rachel Smiths Aug 21 '19 at 17:36
  • @RachelSmiths the decisions of others always make our lives harder! In the long run though you will probably want to make the change... – Nick Aug 21 '19 at 22:44
  • @Nick, that's the right course of action. – Rachel Smiths Aug 21 '19 at 23:41

0 Answers0