Trying 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
ned_by,cla
ims_rep,in
surance_co
mpany,clai
mant_name,
claimant_c
ity,claima
nt_state,c
laimant_zi
p,insured_
name,insur
ed_city,in
sured_zip,
vechicle_y
ear,vechic
le_make,ve
chicle_mod
el,vechicl
e_plate_id
) against ('+ " . $query . "' IN BOOLEAN MODE)";
Database:
mysql> show create table claims\G;
**************************
* 1. row **************************
*
Table: claims
Create Table: CREATE TABLE `claims` (
`id` int(11) NOT NULL auto_increment,
`status` enum('pending','complete')
NOT NULL default 'pending',
`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
ions` text NOT NULL,
`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
_by`,`clai
ms_rep`,`c
laimant_na
me`,`vechi
cle_year`,
`vechicle_
make`,`vec
hicle_mode
l`,`VIN`,`
vechicle_p
late_id`,`
insured_na
me`)
) 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.