Jump to content


Photo

MATCH AGAINST problem...


  • Please log in to reply
2 replies to this topic

#1 kodlcan

kodlcan
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 31 August 2006 - 11:58 PM

I have followin':

CREATE TABLE `PKZ_ciselnik` (
  `ciselnik_PKZ` int(10) unsigned NOT NULL default '0',-- id
  `ciselnik_nazevzbozi` varchar(255) NOT NULL default '',-- product name
  `ciselnik_nazevpodskupiny` varchar(255) NOT NULL default '',-- subclass name
  `ciselnik_nazevvyrobce` varchar(255) NOT NULL default ''-- manufacturer
) ENGINE=MyISAM DEFAULT CHARSET=cp1250;

CREATE TABLE `lekarny` (-- reseller
  `lekarna_id` tinyint(3) unsigned NOT NULL default '0',--id
  `lekarna_jmeno` varchar(255) NOT NULL default ''--name
) ENGINE=MyISAM DEFAULT CHARSET=cp1250;

CREATE TABLE `zbozi_vse` (
  `zbozi_PKD` int(10) unsigned NOT NULL default '0',--id
  `zbozi_mnozstvi` int(11) NOT NULL default '0',--amount
  `zbozi_cenasdph` float unsigned NOT NULL default '0',--price
  `zbozi_lekarna` tinyint(3) unsigned NOT NULL default '0',--drugstore number
  `zbozi_SID` int(10) unsigned NOT NULL default '0'--drugstore internal number
) ENGINE=MyISAM DEFAULT CHARSET=cp1250;

The problem is that I need custom this working schema to work with Score parameter to sort it by relevancy:
I have this: and it works...

SELECT ciselnik_PKZ, ciselnik_nazevzbozi, ciselnik_nazevvyrobce, lekarna_jmeno, zbozi_mnozstvi, zbozi_cenasdph FROM PKZ_ciselnik, zbozi_vse, lekarny WHERE (PKZ_ciselnik.ciselnik_PKZ = zbozi_vse.zbozi_PKD AND zbozi_vse.zbozi_lekarna = lekarny.lekarna_id) AND MATCH(ciselnik_nazevzbozi, ciselnik_nazevvyrobce) AGAINST('+"$query1 +$query2' IN BOOLEAN MODE);

but how to custom it like this: ??

SELECT *, MATCH(title, body) AGAINST('$keyword') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('$keyword')
ORDER BY score DESC

The problem is how to acomplish to implement the part where two tables are joined with WHERE with MATCH...AGAINST part. When I simply rewrite sample with my data like this "WHERE (PKZ_ciselnik.ciselnik_PKZ = zbozi_vse.zbozi_PKD AND zbozi_vse.zbozi_lekarna = lekarny.lekarna_id) AND MATCH (ciselnik_nazevzbozi, ciselnik_nazevvyrobce) AGAINST ('paralen')AS Score ORDER BY Score;
mySQL reports error in the "...AS Score part".


Could anyone help me with??

ThnX a lot.

Kodl

#2 kodlcan

kodlcan
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 01 September 2006 - 12:02 AM

AGAINST('+"$query1 +$query2...... BAD ONE.
this is correct:
AGAINST('+$query1 +$query2...

I made this one when I rewrote this here. This is not the problem. :D

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 September 2006 - 02:11 PM

You can order by the value returned by MATCH() -- the optimizer won't have to run it twice, AFAIK.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users