Search problem

[djroadstar]

I have a problem with my search function. At my external webserver the search function is case sensitive and at my local webserver it,s not case sensitive. The Collation is latin1_swedish_ci. I don,t want to be my search function is case sensitive. I,m also using "FULLTEXT index".

This is the code for the search function:

[code]
$sql = "SELECT *,
MATCH(field1, field2, field3, field4)
AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM tbl_tabel
WHERE MATCH(field1, field2, field3, field4)
AGAINST ('$searchstring' IN BOOLEAN MODE) and status='active' ORDER BY score DESC";
[/code]

A live preview of my search function is: http://www.koopeenpaard.nl/includes/page.php?page=5&name=Zoeken

[Tyche]

Are you sure the Collation on the external webserver is latin1_swedish_ci (which is case-insensitive - thats what the _ci stands for) and not say latin1_bin (which effectively forces case sensitivity) ?

The only way I know of turning off the case-sensitivity is to change the Collation - you can do that at the column,table or database level - to change the column Collation (and char set) use

ALTER TABLE tbl_tabel MODIFY COLUMN field1  CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

[djroadstar]

[quote author=Tyche link=topic=124278.msg514697#msg514697 date=1169898227]
Are you sure the Collation on the external webserver is latin1_swedish_ci (which is case-insensitive - thats what the _ci stands for) and not say latin1_bin (which effectively forces case sensitivity) ?

The only way I know of turning off the case-sensitivity is to change the Collation - you can do that at the column,table or database level - to change the column Collation (and char set) use

ALTER TABLE tbl_tabel MODIFY COLUMN field1  CHARACTER SET latin1 COLLATE latin1_swedish_ci ;


[/quote]

The table type is MyISAM and field type is latin1_swedish_ci

[Tyche]

Assuming one of your fields used in the MATCH AGAINST is a number (such as price)  then
it looks like a known MySQL bug (or feature according to the MySQL developers)  - see http://bugs.mysql.com/bug.php?id=9907

but basically when a  numeric field is converted to a string using implicit conversions then the collation set becomes binary (and therefore case sensitive) 

It does state that it will be fixed in v 5.1

You could try adding explicit conversions of the numeric fields - but I can't confim if that would work

If this isn't the issue then I'm not sure what is.