Silly FullText question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Silly FullText question

Post 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 :)
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

jamiel wrote:I don't trust FULLTEXT Matches ... using LIKE %foo% is much easier.
Your kidding right! Image
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Post by panic! »

ahh I dunno if i want to use LIKE, it seems really slow and brings back loads of random stuff :S
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Expect oddities like the one you have now then. Whats the result of your show create table?
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

Post 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 ;
Post Reply