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

Question

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'

• prd_code collation is latin1_swedish_ci
• prd_name_HEB collation is hebrew_general_ci
• :t0 collation is unknown.

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.

Answer 1

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 )