CODEWITHSUNDEEP
phpmysqlsqlcase-insensitive
Edoardo
Edoardo

Reputation: 475

MySQL prepared statements and case sensitivity

in the past I used the query method of the mysqli class. Now I am rewriting a lot of code using the prepare method. I have a table with the collation utf8mb4_bin (case sensitive) but the user should perform case insensitive query, the past query is this

SELECT SQL_CALC_FOUND_ROWS * FROM utenti WHERE nome LIKE _utf8mb4 ? COLLATE utf8mb4_unicode_ci ORDER BY id_utente ASC

I know I can not use this syntax with prepared statements, whats is the best way? I thought forcing stored value and query value to lowercase, but I think is not the best way to do it.

Upvotes: 1

Views: 804

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562911

You should be able to apply a case-insensitive collation to the column, and then compare that to your parameter:

SELECT * FROM utenti 
WHERE nome COLLATE utf8mb4_unicode_ci LIKE ? 
ORDER BY id_utente ASC

P.S.: I recommend to remove SQL_CALC_FOUND_ROWS. It has no purpose if you don't use LIMIT. It can only make the performance suck.

Upvotes: 2

geco17
geco17

Reputation: 5294

All you need to do is convert your value to uppercase in php and wrap your column with upper in the query.

select * from table where upper(column) like ?

With the ? being set to the uppercase value of the string you're looking for.

Upvotes: 1

Related Questions