Avatar of lleblanc65
Flag for United States of America asked on

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

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

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?
MySQL ServerPHPMicrosoft SQL Server

Avatar of undefined
Last Comment
Sandeep Kothari

8/22/2022 - Mon

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?



	CREATE TABLE `table` 
 `Title` varchar(250) NOT NULL,
 `Category` varchar(15) NOT NULL,
 `Price` int(10) NOT NULL

Open in new window

Ruben, is this what you were looking for DDL wise? Working on populations now...
Sandeep Kothari

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sandeep Kothari

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck