SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: full text

  1. #1
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    full text

    I have this table
    Code:
    CREATE TABLE `images` (
      `id` tinyint(4) NOT NULL auto_increment,
      `cid` tinyint(3) NOT NULL default '0',
      `name` varchar(20) NOT NULL default '',
      `description` varchar(255) NOT NULL default '',
      `src` varchar(150) NOT NULL default '',
      `height` smallint(4) NOT NULL default '0',
      `width` smallint(4) NOT NULL default '0',
      `transp` enum('no','yes') NOT NULL default 'no',
      `keywords` text NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `cid` (`cid`),
      FULLTEXT KEY `name` (`name`),
      FULLTEXT KEY `description` (`description`),
      FULLTEXT KEY `keywords` (`keywords`)
    )
    but when I run this query I get an error.
    Code:
    #sql
    select
    	id
    	, name
    	, description
    	, src
    	, match (name, description, keywords) against ('waking') as score
    from
    	images
    where
    	match (name, description, keywords) against ('waking')
    order by
    	score desc
    
    #error
    Can't find FULLTEXT index matching the column list
    Now I can use full text searching fine on another table but in phpMyAdmin in the indexes list, all the indexs have a cardinality value but in the above table it says none. Could this be causing problems or have I just made a stupid error somewhere?

    Also the table it works on contains ~500 records, this table contains one. I know that'll effect the search results only having one record but that wouldn't be causing the error would it?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i've never used fulltext searching, but my interpretation
    of the error message is that you need a fulltext index
    on the three columns

    so instead of
    Code:
    FULLTEXT KEY `name` (`name`),
    FULLTEXT KEY `description` (`description`),
    FULLTEXT KEY `keywords` (`keywords`)
    i think it wants
    Code:
    FULLTEXT INDEX threesome (name,description,keywords)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •