"From what I have read, with my current collation, default is case-insensitive matches but its not that way... "
Main Topics
Browse All TopicsTrying to perform a search using fulltext on a mysql 4.x database and php.
The collation of the database is latin1_sweedish_ci
the query is:
$query = $_POST["query"];
$SQL = "SELECT * FROM claims where MATCH(IA,claim_no,id,assig
Database:
mysql> show create table claims\G;
**************************
Table: claims
Create Table: CREATE TABLE `claims` (
`id` int(11) NOT NULL auto_increment,
`status` enum('pending','complete')
`date_received` datetime NOT NULL default '0000-00-00 00:00:00',
`date_loss` datetime NOT NULL default '0000-00-00 00:00:00',
`IA` varchar(64) NOT NULL default '',
`claim_no` varchar(32) NOT NULL default '0',
`assigned_by` varchar(64) NOT NULL default '',
`claims_rep` varchar(64) NOT NULL default '',
`insurance_phone` varchar(16) NOT NULL default '',
`insurance_company` varchar(64) NOT NULL default '',
`claims_rep_email` varchar(32) NOT NULL default '',
`claims_rep_phone` varchar(16) NOT NULL default '',
`claimant_name` varchar(64) NOT NULL default '',
`claimant_business_phone` varchar(16) NOT NULL default '',
`claimant_home_phone` varchar(16) NOT NULL default '',
`claimant_address` varchar(128) NOT NULL default '',
`claimant_city` varchar(32) NOT NULL default '',
`claimant_state` varchar(32) NOT NULL default '',
`claimant_zip` varchar(10) NOT NULL default '0',
`IVCV` varchar(8) NOT NULL default '',
`vechicle_year` varchar(4) NOT NULL default '0',
`vechicle_make` varchar(16) NOT NULL default '',
`vechicle_model` varchar(16) NOT NULL default '',
`VIN` varchar(64) NOT NULL default '',
`vechicle_location` text NOT NULL,
`vechicle_plate_id` varchar(16) NOT NULL default '',
`vechicle_loss_detail` text NOT NULL,
`vechicle_special_instruct
`deductable` varchar(16) NOT NULL default '',
`insured_name` varchar(64) NOT NULL default '',
`insured_business_phone` varchar(16) NOT NULL default '',
`insured_home_phone` varchar(16) NOT NULL default '',
`insured_address` varchar(128) NOT NULL default '',
`insured_city` varchar(32) NOT NULL default '',
`insured_state` varchar(32) NOT NULL default '',
`insured_zip` varchar(10) NOT NULL default '0',
`description` varchar(128) NOT NULL default '',
PRIMARY KEY (`id`),
FULLTEXT KEY `id` (`claim_no`,`IA`,`assigned
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=428 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
Now, when i perform a search, its only returing case-sensitive matches.. i am wanting it to return case-insensitive matches. From what I have read, with my current collation, default is case-insensitive matches but its not that way...
I am soo lost as to why it wont return case-insensitive matches.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I think you should double check your collation - perhaps set a case-insensitive collation on this table explicitly. Try a simpler query (perhaps just using '=' rather than fulltext) that matches on only one field just to check that it's all acting as expected.
Also, please tell me that you're really escaping your query in order to avoid an SQL injection attack?
Business Accounts
Answer for Membership
by: ellandrdPosted on 2007-04-28 at 12:43:05ID: 18995156
>>i am wanting it to return case-insensitive matches.
efman/5.0/ en/fulltex t-search.h tml
by default it should return case-insensitive matches:
quoted from the MATCH documentation on MySQL website:
"By default, the search is performed in case-insensitive fashion. However, you can perform a case-sensitive full-text search by using a binary collation for the indexed columns. For example, a column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches."
Have you looked at this yet:
http://dev.mysql.com/doc/r
Ellandrd