0

I have the following query working but trying to add ORDER BY relevance DESC. Can anyone tell me the proper syntax as I have no success putting that to work and receive no matching results:

$query =
"(SELECT postid as id, title as name, 'post' as type FROM post WHERE MATCH (title, content) AGAINST ('$keywords' IN BOOLEAN MODE))          
UNION 
(SELECT pdid as id, pdname as name, 'product' as type FROM product WHERE MATCH (pdname, pddesc, pddetail) AGAINST ('$keywords' IN BOOLEAN MODE))";

This is what I have tried:

$query =
"(SELECT postid as id, title as name, 'post' as type MATCH (title, content) AGAINST ('$keywords' IN BOOLEAN MODE) as relevance FROM post
WHERE MATCH (title, content) AGAINST ('$keywords' IN BOOLEAN MODE))         
UNION 
(SELECT pdid as id, pdname as name, 'product' as type MATCH (pdname, pddesc, pddetail) AGAINST ('$keywords' IN BOOLEAN MODE) as relevance FROM product
WHERE MATCH (pdname, pddesc, pddetail) AGAINST ('$keywords' IN BOOLEAN MODE))
ORDER BY relevance DESC
";
halfer
  • 19,824
  • 17
  • 99
  • 186
Michael Eugene Yuen
  • 2,470
  • 2
  • 17
  • 19
  • Is this (and your prior deleted question) all working now? – halfer Jul 06 '15 at 09:45
  • Yes, halfer!! :) I am new to this and was focusing on the proper syntax while overlooking some basic error. – Michael Eugene Yuen Jul 06 '15 at 09:47
  • Great. Will you make an answer containing your last query? You can self-accept it then, which marks the question as resolved. In relation to the answer, if you had a missing comma, it sounds like that would be a SQL syntax error, but that you were not detecting errors in your PHP. Some error detection code will help a lot here. – halfer Jul 06 '15 at 09:54
  • 1
    No excuse. I will have to accept my own answer in two-days due to reputation. – Michael Eugene Yuen Jul 06 '15 at 09:59

1 Answers1

1
$query =
"(SELECT postid as id, title as name, 'post' as type, MATCH (title, content) AGAINST ('$keywords' IN BOOLEAN MODE) as relevance FROM post
WHERE MATCH (title, content) AGAINST ('$keywords' IN BOOLEAN MODE))         
UNION 
(SELECT pdid as id, pdname as name, 'product' as type, MATCH (pdname, pddesc, pddetail) AGAINST ('$keywords' IN BOOLEAN MODE) as relevance FROM product
WHERE MATCH (pdname, pddesc, pddetail) AGAINST ('$keywords' IN BOOLEAN MODE))
ORDER BY relevance DESC
";

I have found out a missing comma after the 'as type'. After edition, it's working fine now.

Problem solved and is working well.

Michael Eugene Yuen
  • 2,470
  • 2
  • 17
  • 19