CODEWITHSUNDEEP
phpmysqlpdocollationmysql-error-1267
Atara
Atara

Reputation: 3569

Illegal mix of collations for operation 'like' with PDO bindValue()

I changed my code from mysql_query to pdo prepare(), bindValue() and execute()

but with the new code, when I bind Hebrew values to :t0 for the query

select * from product where ( prd_name_HEB like :t0 or prd_code like :t0 )

I get

General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (hebrew_general_ci,COERCIBLE) for operation 'like'

I cannot alter my whole DB to utf8_unicode_ci now.

I understand that I can use COLLATE in my query to set the collation, but I do not know where to add it.

  1. Please let me know what is the correct syntax, with "like" and "COLLATE" for the following query:

    select * from product where ( prd_name_HEB like :t0 or prd_code like :t0 )

  2. Is there a way to add collation to PDO bindValue() ?

Thanks.

Upvotes: 0

Views: 722

Answers (1)

Atara
Atara

Reputation: 3569

solved:

if searchString.containsHebrewCharacters()
  select * from product where ( prd_name_HEB like :t0 )
else
  select * from product where ( prd_name_HEB like :t0 or prd_code like :t0 )

Upvotes: 0

Related Questions