Search code examples
phpmysqlsearchtextwords

Get special words from text

I'm trying to create a service in which the user will input some text data, like a blog post or something like that and my service will replace some "special words" in their text for links. for example:

Blog Post:

"...Pink Floyd were an English rock band that achieved international 
success with their progressive and psychedelic rock music..."

The output will be something like:

"...<a href="http://www.xxx.com?q=pink%20floyd">Pink Floyd</a> 
were an English <a href="http://www.xxx.com?q=rock%20band">rock band</a>
that achieved international success with their 
progressive and psychedelic rock 
<a href="http://www.xxx.com?q=music">music<a/>..."

Something like the wikipedia does!

The general algorithm seems to be very simple:

  1. Remove special characters from the text.
  2. Strip tags.
  3. Lowercase the whole text.
  4. Remove stop words.
  5. Consult the database for the words that left.
  6. Get the original text and replace the words that matched in the database.

But the problem is that, i'm handling with around 1 million words in my database. I'm using PHP and MySQL for that, by my knowledge PHP is probably not the best tool for text processing.

I'm afraid about the performance, depending of the traffic and the number of requests this algol could be VERY SLOW....

I tried to find another tools or methods to deal with this, i've found a lot of things, but i think none of then is the right tool for the job. I've found mysql full text search, search lucene, sphinx, solr. Correct me if i'm wrong but i think they are not the right tools for this kind of search.

Can someone suggest me how to deal with this properly.

Things to consider:

  1. The text unfortunately is in portuguese "pt-br", which i think will be a problem.
  2. It needs to get the full term, in the above exemple it will have to take "rock band" and not split then into "rock" and "band".
  3. I don't have deeper knowledge in other programming languages, but if this was the case i'm open for other technologies too!

Thanks

Solution

  • Don't worry about performance in this problem.

    You can always:

    • Use indexes in db
    • Use cache (memcached for example) for stop words database or output text
    • Use sphinx (search engine, works with MySQL)
    • Keep formated text in database (the best solution if don't plain to extend your stop words database)

    Also example of code to search stop words in db:

    ...
$words = strip_tags($origin);
$words = strtolower($words);
// strip anything (symbols, numbers, etc), keep only words
...
$words = explode(' ', $words);
$found = DB::query(Database::SELECT, "SELECT url, word FROM `stop_words` WHERE word IN (".implode(',',$words).")")->execute()->as_array(); // Kohana framework Database module
$patterns = array();
$replacement = array();
foreach($found AS $row)
{
  $patterns[] = '/('.$row['word'].')/i';
  $replacement[] = '<a href="'.$row['url'].'">$1</a>';
}
$origin = preg_replace($patterns, $replacement, $origin);