building a search

[chucklarge]

Hello,
I am building a search and am wondering how to search across multiple fields.
I have several tables joined and want to search across those, but do i need to create a huge set of OR statements for each field to search?

For example, to search the company name i am doing this

company_info.compname REGEXP '$searchfield'

I am hoping there is an easier way to search entire tables instead of doing something like this

company_info.compname REGEXP '$searchfield' OR company_info.address1 REGEXP '$searchfield' OR company_info.address2 REGEXP '$searchfield' ...

Here is an example table

CREATE TABLE `company_info` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `compname` varchar(50) NOT NULL,
  `address1` varchar(100) NOT NULL,
  `address2` varchar(100) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  `zip` varchar(30) NOT NULL,
  `country` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1 AUTO_INCREMENT=1 ;

Thanks,
Chuck

[bradgrafelman]

Not sure on what MySQL server version you're running, but the MATCH AGAINST full-text search might be what you're after. Example:

SELECT * FROM `company_info` WHERE MATCH (compname, address1, address2, city, state, zip, country) AGAINST ('$searchfield')

Now, to do this, you'll have to create FULLTEXT indeces on the fields you want to search, I believe.