Finding Related Article with the MySQL LIKE Statement

Question

I'm trying to get a list of related articles. Example

$title = $fetch_content[article_name]; // example out put "Hello World, Ask A Question"
$rel_title = "Select * from tbl_content WHERE status='t' and article_name like '%$title' order by id desc limit 5";

How to separate the "Hello World, Ask A Question" into single keywords.

Answer 1

It should be noted that..

like '%$title'

...will only match items like which end in 'title', so you might want to do...

LIKE '%$title%'

...if you simply want items that contain 'title'. I also presume that the $title variable has already been escaped. If not, do...

LIKE '%" . mysql_real_escape_string($title) . "%'

Answer 2

You can use the split function in PHP to break that title up into each word:

$title_words = split($title);

$rel_title = 'select * from tbl_content where status = \'t\' and (1=0';

foreach($title as $word)
{
    $word = str_replace(',', '', $word);
    $word = mysql_real_escape_string($word);
    $rel_title .= ' or article_title like \'%' . $word . '%\'';
}

$rel_title .= ')';

echo $rel_title;

I also used str_replace to remove the comma in the question. Obviously, you can apply this to other characters if you have them.

Answer 3

Bob, is your question more along the lines of how to do a full text search for the individual keywords in the article title? Some databases support text search; MySQL's is described in: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html