case insensitive search in php

Question

Hello!

I use a little search script in my site.

After post the search form, i get this variable:

$var = $_GET[$q] ;
$trimmed = trim($var);

Then i try to search in database:

$query = "select * from test where acc like \"%$trimmed%\""; 
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
.......

I try to search for " test " word. (In my database i have a row with " Test " word.) but found nothing. If i search " TEST " then nothing. Only then i get the result if i search exactly the word for in the database. (Test).

I read somewhere, that " LIKE " is case INsensitive.

Could you help me have can i modify the code, to search case Insensitive?

Thank you.

Answer 1

Doc on case sensitivity:

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation

Answer 2

Case-sensitivity depends on the collation of your table:

SHOW CREATE TABLE test

That's a start - checking whether your table's case-sensitive or not. I'll leave it to others to harp-on about how insecure your example code is.

Answer 3

This could help you.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

By the way, you should use the mysqli object to perform SQL request in PHP.