LIKE - Case Insensitive issue - PHPBuilder Forums

LIKE - Case Insensitive issue

baffled_in_UK

Ina previous thread :
http://www.phpbuilder.com/board/showthread.php?threadid=10231040&highlight=case+sensitive
it states that MySQL searches with LIKE are case-insensitive by default.

If that is the case can some please tell me why this search

$sql = "SELECT count(id) FROM table WHERE title LIKE '%$search%') ";

does not work if the case values differ.

For example, in my table I have a record where the title is 'Family Tree'. A search by Family returns the record but a search by family does not.

Thanks

tekky

$sql = "SELECT count(id) FROM table WHERE title LIKE '%$search%'";

long shot... but try removing the ) after '%$search%'
.....

barand

I've found LIKE to be case-insensitive.

However you could try

$sql = "SELECT count(id) FROM table WHERE LOWER(title) LIKE '%$search%') ";

and always ensure your $search is lowercase too.

hth

ultraslacker

What is the datatype of title? Was the column created with the BINARY attribute?

baffled_in_UK

The field was not created with the BINARY atrribute

tekky

Did you try removing the extra ) you had in there? not sure how picky SQL is on () but you had a ) closing a non existant (

jweissig

hello,

You can read the mysql manual sections

6.3.2.1 String Comparison Functions
6.3.2.2 Case-Sensitivity

I assume you are doing something like "$search was found 54 time in database" based on the count(id) in the select query. You can use "count(id) as total FROM" so that the count data will be called total.

You need to use a "GROUP BY" when using a count().. possibly you don't when just using one comun but try this out..

$sql = "SELECT count(id) as total FROM table WHERE title LIKE '%$search%' GROUP BY total";

let me know if this works,
- justin

tekky

only need GROUP BY if you select more than jsut the COUNT() clause