Page 1 of 1

Silly FullText question

Posted: Fri Aug 11, 2006 7:49 am
by panic!
FULLTEXT not working!
I'm having some problems with something that should be very simple indeed.

first of all I've a table called `bu_companies` with 23 columns with various information about the company...

basically I want some of the fields to be searchable via a fulltext search, so I set the table up as MyISAM so I can create a fulltext index

and then ran this query

Code: Select all

ALTER TABLE `bu_companies`ADD FULLTEXT(`company_name`, `description`);
which ran fine.

I have two records in the database:

one with a company_name of `h3omedia` another with `alaska design`

if i run eithe rof these queries they return 0 rows.

Code: Select all

SELECT * FROM `bu_companies`
        WHERE MATCH(`company_name`) AGAINST ('alaska')
I just don't get it!

:(.

Help would be very much appreaciated :)

Posted: Fri Aug 11, 2006 7:57 am
by jamiel
What do you get from

Code: Select all

SHOW CREATE TABLE `bu_companies`
I don't trust FULLTEXT Matches ... using LIKE %foo% is much easier.

Posted: Fri Aug 11, 2006 8:05 am
by JayBird
jamiel wrote:I don't trust FULLTEXT Matches ... using LIKE %foo% is much easier.
Your kidding right! Image

Posted: Fri Aug 11, 2006 8:07 am
by panic!
ahh I dunno if i want to use LIKE, it seems really slow and brings back loads of random stuff :S

Posted: Fri Aug 11, 2006 8:25 am
by jamiel
Expect oddities like the one you have now then. Whats the result of your show create table?

Posted: Fri Aug 11, 2006 9:14 am
by panic!
very true! but I think I should get it sorted this way :)

Code: Select all

CREATE TABLE `bu_companies` (
  `idc` int(7) NOT NULL auto_increment,
  `idu` int(7) NOT NULL,
  `company_name` varchar(64) NOT NULL,
  `company_contact` varchar(64) NOT NULL,
  `address_1` varchar(32) NOT NULL,
  `address_2` varchar(32) NOT NULL,
  `address_3` varchar(32) NOT NULL,
  `town` varchar(32) NOT NULL,
  `county` varchar(32) NOT NULL,
  `postcode` varchar(32) NOT NULL,
  `telephone` varchar(25) NOT NULL,
  `mobile` varchar(25) NOT NULL,
  `fax` varchar(25) NOT NULL,
  `email` varchar(100) NOT NULL,
  `website` varchar(255) NOT NULL,
  `category_list` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `keywork` text NOT NULL,
  `accreditation` text NOT NULL,
  `imagefile` varchar(255) NOT NULL,
  `publicactive` tinyint(1) NOT NULL,
  `last_update` datetime NOT NULL,
  `last_ip` varchar(16) NOT NULL,
  UNIQUE KEY `idc` (`idc`),
  FULLTEXT KEY `company_name` (`company_name`),
  FULLTEXT KEY `company_name_2` (`company_name`),
  FULLTEXT KEY `company_name_3` (`company_name`),
  FULLTEXT KEY `company_name_4` (`company_name`),
  FULLTEXT KEY `company_name_5` (`company_name`),
  FULLTEXT KEY `company_name_6` (`company_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;