MySQL and PHP: MATCH() AGAINST() Case-Sensitivity Issue

lleblanc65
lleblanc65 used Ask the Experts™
on
I am having trouble with a PHP MySQL search engine I built for my website using the MATCH() AGAINST() method. My searches should return results without regard to case-sensitivity, but this is not the case. If I search for Camera, camera, or CAMERA I get different (sometimes no) results for each. My columns are collated to latin1_swedish_ci. Each column has a FULLTEXT index. I'm using the BOOLEAN mode as well. Using
$_GET['searchterm'];

Open in new window

to grab the search term from my form...

This is my current MySQL query...

  $query = ("SELECT * FROM `table` WHERE MATCH(title,price,category) AGAINST
('$searchterm' IN BOOLEAN MODE)")or die(mysql_error()); 

Open in new window


Any thoughts? What am I missing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Fulltext search in mysql is definitely not case-sensitive, so I can't explain why you are getting what seem to be case-sensitive results. Can you post the create table ddl and also a small example population so I can check into it?

Ruben.

Author

Commented:
	CREATE TABLE `table` 
 `Title` varchar(250) NOT NULL,
 `Category` varchar(15) NOT NULL,
 `Price` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Open in new window


Ruben, is this what you were looking for DDL wise? Working on populations now...
Project Lead
Commented:
see this is what happens when you mix different column types in the query.... try removing price from the search column and check the results ....
Sandeep KothariProject Lead

Commented:
so basically I want you to try this...
$query = ("SELECT * FROM `table` WHERE MATCH(title,category) AGAINST
('$searchterm' IN BOOLEAN MODE)")or die(mysql_error()); 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial