+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Searching my contents [MySQL] (/showthread.php?tid=7569)
Searching my contents [MySQL] - El Forum - 04-15-2008
[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
