Topic: multiple keyword search order

[poelinca]

create table keyword_relevance
( id tinyint not null primary key auto_increment
, Description longtext
);
insert into keyword_relevance (Description) values
 ('Hello world')
,('hello friends from around the world')
,('friends from around the world')
,('hello my friends')
,('the keyword is not present');


Oki so up until now i was retreving my search results by "SELECT description FROM keyword_relevance WHERE description REGEXP '(hello|world)';"

Now i need to order this result based on the occurence of the search keywords , the more different keywords found in one row the higher will "rank" in the search result query . Question is , can it be done from the sql query ? or do i need to work the php bit to search for each keyword in a different query then test they'r id's and ...

[mapleleaf]

If you put a fulltext index on the Description MYSQL will sort by relevance.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

[poelinca]

oki thanks , i'll try it out when i get to work and i'll let everibody know how it whent

[poelinca]

CREATE TABLE `ci_articles` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `content` longtext,
  `date` int(11) default NULL,
  `author` varchar(255) default NULL,
  `cat` int(11) default NULL,
  `is_page` tinyint(4) default NULL,
  `permalink` varchar(255) default NULL,
  `page_desc` varchar(255) default NULL,
  `page_key` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `title` (`title`,`page_key`,`page_desc`,`permalink`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8


running the query
SELECT * FROM ci_articles WHERE MATCH(title, page_key) AGAINST('sasa')

produces
Error Number: 1191

Can't find FULLTEXT index matching the column list

[fenway]

That's because you don't have a full-text index on just those 2 columns.

[poelinca]

CREATE TABLE `ci_articles` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `content` longtext,
  `date` int(11) default NULL,
  `author` varchar(255) default NULL,
  `cat` int(11) default NULL,
  `is_page` tinyint(4) default NULL,
  `permalink` varchar(255) default NULL,
  `page_desc` varchar(255) default NULL,
  `page_key` varchar(255) default NULL,
  `search_content` longtext NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `title` (`title`,`page_key`)
) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8


running the query
SELECT * FROM ci_articles WHERE MATCH(title, page_key) AGAINST('sasa')

produces
Error Number: 1096

No tables used

SELECT *

[poelinca]

yup works now after i restarted mysql , thanks .

[poelinca]

hey , works like a charm , thanks second error was made by my mistake sorry to fill the forum with uninportant stuff