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