Match And Against For Searching

[drisate]

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 October 16, 2012 by drisate

[premiso]

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.

[drisate]

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

[premiso]

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.