• 0

[MySQL] Order By Relevance


Question

Hey,

So im trying to make a simple search feature, that orders the results by relevancy.

I found a query online, that said it did just that, but now im trying to run it, im getting a mysql error.

ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''posts' WHERE post_content LIKE '%test%' OR post_content LIKE '%string%' ORDER B' at line 1

QUERY:

SELECT *, ((CASE WHEN 'post_content' LIKE '%test%' THEN 1 ELSE 0 END) + (CASE WHEN 'post_content' LIKE '%string%' THEN 1 ELSE 0 END)) AS relevance FROM 'posts' WHERE post_content LIKE '%test%' OR post_content LIKE '%string%' ORDER BY relevance DESC

Can anyone shed some light on what this error is about?

Thanks in advance :D

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Heh, the table name was quoted mate. Try this.

SELECT *, ((CASE WHEN 'post_content' LIKE '%test%' THEN 1 ELSE 0 END) + (CASE WHEN 'post_content' LIKE '%string%' THEN 1 ELSE 0 END)) AS relevance FROM posts WHERE post_content LIKE '%test%' OR post_content LIKE '%string%' ORDER BY relevance DESC

Link to comment
Share on other sites

  • 0

Heh, the table name was quoted mate. Try this.

SELECT *, ((CASE WHEN 'post_content' LIKE '%test%' THEN 1 ELSE 0 END) + (CASE WHEN 'post_content' LIKE '%string%' THEN 1 ELSE 0 END)) AS relevance FROM posts WHERE post_content LIKE '%test%' OR post_content LIKE '%string%' ORDER BY relevance DESC

Field names are also not meant to be quoted using single quotes. Use:

SELECT *, ((CASE WHEN post_content LIKE '%test%' THEN 1 ELSE 0 END) + (CASE WHEN post_content LIKE '%string%' THEN 1 ELSE 0 END)) AS relevance FROM posts WHERE post_content LIKE '%test%' OR post_content LIKE '%string%' ORDER BY relevance DESC

OR

SELECT *, ((CASE WHEN `post_content` LIKE '%test%' THEN 1 ELSE 0 END) + (CASE WHEN `post_content` LIKE '%string%' THEN 1 ELSE 0 END)) AS `relevance` FROM `posts` WHERE `post_content` LIKE '%test%' OR `post_content` LIKE '%string%' ORDER BY `relevance` DESC

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.