Welcome Guest, Not a member yet? Register   Sign In
Searching my contents [MySQL]
#1

[eluser]KeyStroke[/eluser]
Hi,

I've setup a simple database with a the table "topic" which has a "title" field, "body" field and a few other fields.

What I'm trying to do here is make a search function that:
1. searches the title and the body of topics.
2. order results like this:
a. first topics with the search keywords in their title
b. second topics with the search keywords in their "body"
c. topic date

I'm basically looking for something like this (not my code):
Code:
$sql = "SELECT posts.*,
                CASE WHEN posts.title REGEXP $term THEN 1 ELSE 0 END AS keyword_in_title,
                MATCH ( posts.title, posts.body ) AGAINST ($term) AS mysql_score
                FROM posts
                WHERE ( posts.title REGEXP $term OR posts.body REGEXP $term )
                ORDER BY keyword_in_title DESC, mysql_score DESC, posts.date DESC LIMIT 0, 10";

But a query that works with InnoDB tables. This one doesn't because it relies on FULLTEXT indexing, which only MyISAM supports.


Any idea?


Your help is much appreciated Smile




Theme © iAndrew 2016 - Forum software by © MyBB