Full Text searches

[FlashNinja]

Now I'm developing a search engine for my site, which of course requires databases. I'm using FULL TEXT to search my database and return the results.

 

Here's the query I'm using:

 

"SELECT * FROM tag WHERE MATCH ( name, tag1, tag2, tag3, tag4, tag5 ) AGAINST ('dragon')";

 

And here's the SQL table:

 

CREATE TABLE IF NOT EXISTS `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag1` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag2` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag3` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag4` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag5` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  FULLTEXT KEY `name_2` (`name`,`tag1`,`tag2`,`tag3`,`tag4`,`tag5`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tag`
--

INSERT INTO `tag` (`id`, `name`, `tag1`, `tag2`, `tag3`, `tag4`, `tag5`) VALUES
(1, 'Dave', 'dragon', 'roost', '', '', ''),
(2, 'Dave2', 'dragon', 'island', 'roost', '', '');

 

When using the query above to search for 'dragon' in the database I'm expecting the query to return any rows that contain 'dragon', but this is not the case. The query returns nothing, when to my knowledge it should be returning both rows currently in the database. What is it that I have done incorrectly here and is preventing me getting the results I want?

[fenway]

FTS is weird and magical -- like dragons.  It misbehaves on very small datasets -- this is a known limitation.

[FlashNinja]

Is there any other way you can think of producing a non linear search in SQL? I was previously using LIKE to take the search entry from php and run it through the LIKE query but it only read tags in a linear fashion.

 

For example:

 

Two entries in the database

 

Row 1:  'dragon', 'roost'

Row 2: 'dragon', 'island' 'roost'

 

Entering a search term for 'dragon' produces both those results, but entering the search term 'dragon roost' will only produce the first row. Is there any method that will allow me to have both rows appear if a user enters two tags that match into the search?

[QuickOldCar]

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

 

I use Boolean Full-Text Searches, you insert all your search terms together into the query statement.

 

EXACT PHRASE wrapped with double quotes

"word"

 

OR just words with spaces

word word

 

AND words with a + means must contain

+word

 

MUST CONTAIN multiple + words would narrow the search down more to contain just those words

+word +word

 

EXCLUDE words with a - would exclude that word

word -word

 

or can do combinations

"word word" +word -word

 

There are more but these above are the most useful

 

 

So to your answer, if you did +dragon +roost or even +roost +dragon , that would get you the desired results the same

 

What i do is explode the search terms, if not within quotes....add a + in front of each, but only if there is not already a -  or + in front of it.

 

you can try my simple search out

http://dynaindex.com/search.php?s=listen+free+music+-gospel

 

If you wanted to get complex, in my full Index I use multiple mysql queries in if/else statements depending on what the user selects in the form, is a default query using must contain if none selected

 

Here is the query I use in my simple search

$trimmed_words is my search terms (all get a plus placed before each word, except the dashes)

$exlusions is an array of words I never want to see within the results, all use a -word

 

So I just add a string of words and use this as my safe filter

 

if($filter == "off"){
$exlusions = '';
} else {
$exlusions = " -sex -sexy -nude -nudity";//and piles more
}

 

$result = mysql_query("SELECT * FROM posts WHERE status='publish' AND MATCH (title,post_content) AGAINST ('$trimmed_words$exlusions' IN BOOLEAN MODE) GROUP BY ID ASC Limit $startrow,$posts_per_page");

 

Be sure to look over the manual about making indexes in mysql, minimum word lengths, stopwords