Hi everyone. I'm using the following query to search a MySQL MyISAM table and am looking for the phrase "account manager".
Code:
SELECT id, text, title, MATCH(title, text) AGAINST('account manager') AS relevance FROM faq ORDER BY relevance DESC
The table it is searching looks like this
Code:
CREATE TABLE IF NOT EXISTS `faq` (
`id` int(255) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(255) unsigned NOT NULL,
`title` varchar(40) NOT NULL,
`text` text NOT NULL,
`views` int(255) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `topic_id` (`topic_id`),
FULLTEXT KEY `fulltext` (`title`,`text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `faq`
--
INSERT INTO `faq` (`id`, `topic_id`, `title`, `text`, `views`) VALUES
(1, 1, 'I can''t find the product I want', 'If you can''t find the product you want to buy then it hasn''t been allocated to your account.\r\nThe I-Tel site aims to reduce clutter and so only displays products that you have made us aware of. If you need any additional items added to your account then please contact your account manager for prices.', 0),
(2, 1, 'I''m over my credit limit', 'The website has been designed to keep you within your agreed credit limit. Should you want to place an order that will take you over your credit limit then you will not be allowed to order.\r\nOnce you pay off old invoices your available credit will increase. If you have made a payment that hasn''t cleared then please contact your account manager to let them know.\r\nIf you are desperate for an order then please contact us and we may, at our discretion, allow you to place an order.', 0),
(4, 1, '"Incorrect password" when logging in', 'The most common reason for this error is that you may be using the wrong password to access the I-Tel site.\r\nThe second major reason for this error is that you are actually using the wrong username. Your username is usually the first three letters of your company name followed by three numbers e.g. HOP001.\r\nIf you are still struggling to log in then please contact your account manager. They will be able to reset your password immediately.', 0),
(5, 1, 'You do not have a security question', 'When you have forgotten your password and you want to reset it manually you will have come to the ''Forgot Password'' page. This asks you for your username and email address and will automatically retrieve your security question.\r\nIf you haven''t set a security question then you will receive the error "You do not have a security question set". If you receive this error please contact us immediately so that we can reset your password for you.', 0),
(6, 1, 'Who is the "Free quote" page for?', 'The "Free Quote" page is aimed at prospective customers visiting the site. They can list their products and current prices allowing one of our sales representatives to get back to them with prices.\r\nExisting customers are still welcome to use this page but it is quicker and easier to contact their account manager directly.', 0),
(7, 4, 'Who is my account manager?', 'If you are uncertain who your account manager is then log in to the website. The first page you come to will display an image of your account manager and all of their contact details.\r\nIf you do not wish to use your online account you can call us at any time and we will let you know who your account manager is and any other details you require.', 0);
Even though the last entry says "Who is my account manager" it retrieves this in last position...? Even though I would deem this as MOST relevant. How can I structure the query to give higher priority to the 'title' cell of the table while still obviously consulting the 'text' cell.
