Jump to content

Match And Against For Searching


drisate

Recommended Posts

Hey guys i am trying to performe a search in PHP using MySQL. The search must be by relavancy and i am having a hard time understanding the fulltext way of doing it. The querry i put together does not return any SQL error so the syntaxe must be good ... The problem is that it's currently not returning any row. I made the querry with 'cc' as the keyword so normaly it should return the inserted row provided below 'cc' existe in 'ccmedia' and 'ccmedia.ca'

 

 SELECT * , (
( 1.3 * (
MATCH (
nom)
AGAINST (
'+cc+'
IN BOOLEAN
MODE ) ) ) + ( 0.6 * (
MATCH (
site)
AGAINST (
'+cc+'
IN BOOLEAN
MODE ) ) )
) AS relevance
FROM entreprises
WHERE (

MATCH (
nom, site)
AGAINST (
'+cc+'
IN BOOLEAN
MODE )
)
ORDER BY relevance DESC
LIMIT 0 , 30

 

CREATE TABLE IF NOT EXISTS `entreprises` (
`eid` int(9) NOT NULL AUTO_INCREMENT,
`mid` int(9) NOT NULL,
`nom` varchar(255) NOT NULL,
`tel` varchar(255) NOT NULL,
`site` varchar(255) NOT NULL,
`categorie_p` int(9) NOT NULL,
`categorie_s` varchar(255) NOT NULL,
`adresse` varchar(255) NOT NULL,
`cp` varchar(255) NOT NULL,
`id_ville` int(9) NOT NULL,
`id_province` int(9) NOT NULL,
`id_pays` int(9) NOT NULL,
PRIMARY KEY (`eid`),
FULLTEXT KEY `nom` (`nom`),
FULLTEXT KEY `tel` (`tel`),
FULLTEXT KEY `site` (`site`),
FULLTEXT KEY `adresse` (`adresse`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `entreprises` (`eid`, `mid`, `nom`, `tel`, `site`, `categorie_p`, `categorie_s`, `adresse`, `cp`, `id_ville`, `id_province`, `id_pays`) VALUES
(1, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1);

Edited by drisate
Link to comment
Share on other sites

Met bet is you are being hit by:

 

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.

 

From http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

Add more test rows and see if what comes of it.

Link to comment
Share on other sites

Thanks for your help but adding lines did not solve the problem

 

INSERT INTO `entreprises` (`eid`, `mid`, `nom`, `tel`, `site`, `categorie_p`, `categorie_s`, `adresse`, `cp`, `id_ville`, `id_province`, `id_pays`) VALUES
(1, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(2, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(3, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(4, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(5, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(6, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(7, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1),
(8, 1, 'CCMédia', '450-654-3281', 'ccmedia.ca', 1, '-2-3-', '92a rue lamartine', 'T0A 5T7', 1, 1, 1);

 

My query is

SELECT * , ( ( 1.3 * (MATCH (nom) AGAINST ('cc' IN BOOLEAN MODE) ) ) + ( 0.6 * (MATCH (site) AGAINST ('cc' IN BOOLEAN MODE) ) ) ) AS relevance FROM entreprises WHERE ( MATCH (nom, site) AGAINST ( 'cc' IN BOOLEAN MODE)) ORDER BY relevance DESC LIMIT 0 , 30

 

i also tryed with + arround the keyword but same result

 

SELECT * , ( ( 1.3 * (MATCH (nom) AGAINST ('+cc+' IN BOOLEAN MODE) ) ) + ( 0.6 * (MATCH (site) AGAINST ('+cc+' IN BOOLEAN MODE) ) ) ) AS relevance FROM entreprises WHERE ( MATCH (nom, site) AGAINST ( '+cc+' IN BOOLEAN MODE)) ORDER BY relevance DESC LIMIT 0 , 30

Link to comment
Share on other sites

Did you even read what I pasted?

 

If your query matches 50% or more of the rows it will return nothing. Simply adding the same row 10 times, will give you nothing. You have to change the data that is being searched by to be different in order to get results.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.