Fulltext search

Databases
1

I try to implement a search where 3 fields in the database (artist, title, publisher) need to be targed. I.o.w when I type 1, 2 or 3 phrases in the search field it should return results that where found in 1 of the 3 or in 2 or all 3 fields. For example when in the search Muse and Warner it should return all albums from Muse made on the Warner Bros label, but for some reason doesn’t the database return those results. It is working with the first 2 fields (artist and title) but not with the third. This is my products table:


CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(9) unsigned NOT NULL auto_increment,
  `category_id` int(4) NOT NULL,
  `subcategory_id` int(5) default NULL,
  `genre_id` int(5) default NULL,
  `artist` varchar(100) default NULL,
  `title` varchar(100) default NULL,
  `publisher` varchar(100) default NULL,
  `release_date` varchar(32) default NULL,
  `conditie` varchar(255) default NULL,
  `price` float(10,2) default NULL,
  `sale_price` float(10,2) default NULL,
  `viewed` int(11) NOT NULL default '0',
  `added` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `isNew` tinyint(1) NOT NULL default '1',
  `inStock` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`product_id`),
  fulltext(artist, title, publisher)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Am I doing something wrong or should I take a complete different approach. Because I was also reading that fulltext has its limitations.

  • MySQL requires that you have at least three rows of data in your result set before it will return any results.
  • By default, if a search term appears in more than 50% of the rows then MySQL will not return any results.

to name a few

2

Fix your query, get it working correctly and THEN and only THEN put it into your front end application.

Again, unless you are searching a text type field with large amounts of data you aren’t gaining anything by using a full text index search for the type of search you are doing.

Full text would be used when you are searching for a bunch of words contained in a paragraph, or a chapter of a book for instance.

Not saying that is the only time you would use it, just I think it is overkill for what you would be searching for in your application.

3

I don’t see why you’d need to implement a full text search here. why aren’t you merely searching those fields? you only have one value in each column for each row right?

WHERE artist=‘Muse’ and publisher=‘Warner’ would do what you are looking for wouldn’t it?

4

WHERE artist=‘Muse’ and publisher=‘Warner’ would do what you are looking for wouldn’t it?

But how do I get that coming from a form field?


			<cfquery name="getProducts" datasource="#application.dsn#">
				SELECT p.product_id, p.artist, p.title, p.publisher, p.category_id, c.category
				FROM products p
				LEFT JOIN categories c ON p.category_id = c.category_id
                WHERE 0 = 0 
                <cfif structKeyExists( Url, 'category' ) And Url.category Neq 0>>
                AND p.category_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( Url.category )#" />
                </cfif>
				<cfif isDefined( "Url.txtSearch" ) AND Url.txtSearch NEQ "">
				AND ( artist LIKE '&#37;#Url.txtSearch#%'
				OR title LIKE '%#Url.txtSearch#%' 
                OR MATCH (artist, title, publisher) AGAINST ('Url.txtSearch'in boolean mode))
				</cfif>
			</cfquery>

Here it is. It’s about the bottom part. category field is another field in the form and works great.