Riffola,
The easiest way to accomplish this is to use two tables, one which contains the news articles (their titles, their ids, etc.) and one which contains an index of title words and id's. When a new article is inserted, you need to explode the articles title and insert a record for each word in the title into the index table.
eg.
| ARTICLE ID | TITLE_WORD |
| 50 | Alpha |
| 50 | Beta |
| 50 | Gamma |
| 50 | Delta |
Excuse the ASCII art🙂
To get similar articles, you'd then do this:
<?php
// name the index and article tables
$articleTable = "newsDB";
$indexTable = "indexDB";
// explode the current title by spaces
$expTitle = explode(" ", $title);
// loop through all but the last title words
for ($i = 0 ; $i != (count($expTitle) - 1) ; $i++) {
// add the title word to the search string
$searchTitle .= "'" . $expTitle[$i] . "',";
}
// add the last title word to the search string
$searchTitle .= "'" . $expTitle[$i] . "'";
// create the search query
$sql = "SELECT $articleTable.title AS article_title,$articleTable.id AS article_id,count($indexTable.title_word) AS words_matched,$indexTable.article_id AS index_id,$indexTable.title_word AS index_word FROM $newTable,$indexTable WHERE $indexTable.title_word IN ($searchTitle) AND $articleTable.id != $id AND $articleTable.id = $indexTable.id GROUP BY $articleTable.id ORDER BY words_matched";
// place code to excute the query, and return, process, and display the results here
?>
The above query should give you a list of all articles (except the current) which match the title of the current article based on title words ordered by the total number of words matched. For further info on how to optimize this search process a bit (by removing noise words, etc.), read Clay Johnson's tutorial on how to put a simple search engine together:
http://www.phpbuilder.com/columns/clay19990421.php3
HTH.
Cheers,
Geoff A. Virgo
