CODEWITHSUNDEEP
phpmysqlsearchsearch-engine
Manish
Manish

Reputation: 1972

Search algorithms or tool for searching from database

I have this database table:

Column       Type

source       text            
news_id      int(12)             
heading      text            
body         text        
source_url   tinytext
time         timestamp
news_pic     char(100) 
location     char(128)
tags         text
time_created timestamp
hits         int(10)

Now I was searching for an algorithm or tool to perform a search for a keyword in this table which contains news data. Keyword should be searched in heading,body,tags and number of hits on the news to give best results.

Upvotes: 0

Views: 1952

Answers (1)

user428517
user428517

Reputation: 4193

MySQL already has the tool you need built-in: full-text search. I'm going to assume you know how to interact with MySQL using PHP. If not, look into that first. Anyway ...

1) Add full-text indexes to the fields you want to search:

alter table TABLE_NAME add fulltext(heading);
alter table TABLE_NAME add fulltext(body);
alter table TABLE_NAME add fulltext(tags);

2) Use a match ... against statement to perform a full-text search:

select * from TABLE_NAME where match(heading, body, tags, hits) against ('SEARCH_STRING');

Obviously, substitute your table's name for TABLE_NAME and your search string for SEARCH_STRING in these examples.

I don't see why you'd want to search the number of hits, as it's just an integer. You could sort by number of hits, however, by adding an order clause to your query:

select * from TABLE_NAME where match(heading, body, tags, hits) against ('SEARCH_STRING') order by hits desc;

Upvotes: 3

Related Questions