Relevance gives me: Incorrect arguments to MATCH

[StefanRSA]

Hi, been going with MySql for some time now (without any formal training!)... This is the first time I am dealing with a search where I am trying to do relevance... Now... After playing around to get the code working... I get the following MySql printed on the screen... Can you help please? Here is my code??? What am I doing wrong?

 

$squery = ("SELECT ads.subcatid AS subcatid, adsubcat.linkname AS sublink, ads.catid AS catid, 
  search_town.townLabel AS town, search_town.townFileName AS townlink, search_prov.provLabel,
  search_prov.provDirLabel AS provDirLabel, ads.id AS id,
  ads.header AS header, ads.location AS location, ads.sfield AS sfield,
  ads.text AS text, ads.R_rand AS R_rand, ads.addate AS addate,
  ads.r_option AS r_option, ads.userid AS userid, ads.adnr AS adnr,
  ads.username AS username, adfields.f_value AS fieldvalue,
  match (text, adfields.f_value) against ('%$trimmed%') as relevance
  FROM ads
  JOIN adsubcat ON adsubcat.id=ads.subcatid
  JOIN adcat ON adcat.id=ads.catid 
  JOIN adfields ON adfields.ad_id=ads.id
  JOIN search_town ON search_town.townLabel=ads.location
  JOIN search_region ON search_region.regionId=search_town.relRegionId
  JOIN search_prov ON search_prov.provId=search_region.relProvId
  WHERE match (text, adfields.f_value) against ('%$trimmed%' IN BOOLEAN MODE) 
  Order by relevance Desc") or die(mysql_error());
  $allads=mysql_query($squery) or die(mysql_error());

[mikosiko]

in how many tables of that select you have a field named 'text' ?

 

I will try to write the match line(s) this way

match (ads.text, adfields.f_value) against ('%$trimmed%') as relevance

[StefanRSA]

Thanks mikosiko,

 

Good guess! Only ads table had the filed text... And it did not work... Same result

Thanks for helping!

[mikosiko]

I haven't used MATCH too much ... but according to the manual it say :

 

"The search string must be a literal string, not a variable or a column name. There are three types of full-text searches"

 

it maybe the cause of your problem (no tested in my side)

[StefanRSA]

Thanks for the help.... I am sure the search string criteria is OK cause it works fine without the match...

[StefanRSA]

Ok... I found some sort of solution... But still have a problem!?!?!

Apparently a MATCH cannot do a search on multiple columns in different tables... So we have to make use of the useful OR.... Now... My problem is... When testing this in phpMyAdmin the following code works fine....

SELECT ads.subcatid AS subcatid, adsubcat.linkname AS sublink, ads.catid AS catid, 
  search_town.townLabel AS town, search_town.townFileName AS townlink, search_prov.provLabel,
  search_prov.provDirLabel AS provDirLabel, ads.id AS id,
  ads.header AS header, ads.location AS location, ads.sfield AS sfield,
  ads.text AS text, ads.R_rand AS R_rand, ads.addate AS addate,
  ads.r_option AS r_option, ads.userid AS userid, ads.adnr AS adnr,
  ads.username AS username, adfields.f_value AS fieldvalue,
  match (adfields.f_value) against ('nissan') AS relevance,
  match (ads.text) against ('nissan') AS relevance1
  FROM ads
  JOIN adsubcat ON adsubcat.id=ads.subcatid
  JOIN adcat ON adcat.id=ads.catid 
  JOIN adfields ON adfields.ad_id=ads.id
  JOIN search_town ON search_town.townLabel=ads.location
  JOIN search_region ON search_region.regionId=search_town.relRegionId
  JOIN search_prov ON search_prov.provId=search_region.relProvId
  WHERE (match (adfields.f_value) against ('nissan' IN BOOLEAN MODE) OR match (ads.text) against ('nissan' IN BOOLEAN MODE)) 
  GROUP BY ads.id Order by relevance Desc,relevance1 Desc

 

When I put this in a php file to get the results with all the nice to look at features I still get the dreadful "Incorrect arguments to MATCH"

 

Does anybody out there know why this is not doing what it should in a Php file???

The code in my Php file looks as follow:

$squery = ("SELECT ads.subcatid AS subcatid, adsubcat.linkname AS sublink, ads.catid AS catid, 
  search_town.townLabel AS town, search_town.townFileName AS townlink, search_prov.provLabel,
  search_prov.provDirLabel AS provDirLabel, ads.id AS id,
  ads.header AS header, ads.location AS location, ads.sfield AS sfield,
  ads.text AS text, ads.R_rand AS R_rand, ads.addate AS addate,
  ads.r_option AS r_option, ads.userid AS userid, ads.adnr AS adnr,
  ads.username AS username, adfields.f_value AS fieldvalue,
  match (adfields.f_value) against ('nissan') AS relevance,
  match (ads.text) against ('nissan') AS relevance1
  FROM ads
  JOIN adsubcat ON adsubcat.id=ads.subcatid
  JOIN adcat ON adcat.id=ads.catid 
  JOIN adfields ON adfields.ad_id=ads.id
  JOIN search_town ON search_town.townLabel=ads.location
  JOIN search_region ON search_region.regionId=search_town.relRegionId
  JOIN search_prov ON search_prov.provId=search_region.relProvId
  WHERE (match (adfields.f_value) against ('nissan' IN BOOLEAN MODE) OR match (ads.text) against ('nissan' IN BOOLEAN MODE)) 
  GROUP BY ads.id Order by relevance Desc,relevance1 Desc") or die(mysql_error());
  $allads=mysql_query($squery) or die(mysql_error());

[StefanRSA]

Got this one... I had two look ups doing the same thing... I just did not fix the other