StuartBombay
Programmer
I thought fulltext sounded like the way to go at first, but now I think it's not the best option when searching for proper names. I want to be able to search against first, last, email username or employee number. Ideally it could find based on part of a name or employee number.I tried putting fulltext indexes on all those fields but had 2 problems. First was if I wrote the query to search more than 1 colum:
I get the error '#1191 - Can't find FULLTEXT index matching the column list'. Also, I had to have the entire name or employee number - including leading 0's.
I can't figure out why I get that error, but maybe it's moot because I shouldn't use FULLTEXT anyway?
If I use a LIKE query instead, how many columns can I reasonable search on, and do I need to index those columns? Would it be with 'INDEX' then?
BTW, here is the table:
Yeah, that 'full' is a problem. I need to change it to 'preferred' before I write much more code!
I appreciate any input/suggestions. Maybe there's an option I haven't considered, I'm pretty green at this.
Code:
select * from people where match(username, emplid) against ('drknow')
I get the error '#1191 - Can't find FULLTEXT index matching the column list'. Also, I had to have the entire name or employee number - including leading 0's.
I can't figure out why I get that error, but maybe it's moot because I shouldn't use FULLTEXT anyway?
If I use a LIKE query instead, how many columns can I reasonable search on, and do I need to index those columns? Would it be with 'INDEX' then?
BTW, here is the table:
Code:
CREATE TABLE IF NOT EXISTS people (
username varchar(15) NOT NULL,
emplid varchar(10) default NULL,
`first` varchar(30) NOT NULL,
middle varchar(10) default NULL,
`last` varchar(40) NOT NULL,
`full` varchar(50) NOT NULL,
locnum varchar(10) default NULL,
prognum varchar(10) default NULL,
positionnum varchar(10) default NULL,
jobcode varchar(10) default NULL,
PRIMARY KEY (username),
KEY emplid (emplid),
FULLTEXT KEY emplidFT (emplid),
FULLTEXT KEY fullFT (`full`),
FULLTEXT KEY usernameFT (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I appreciate any input/suggestions. Maybe there's an option I haven't considered, I'm pretty green at this.