Related News Using PHP, phpmorphy and MySQL

EminH August 14, 2013 at 14:28

Related News Using PHP, phpmorphy and MySQL

I want to share a method for determining “similar” entries. I think it will be useful for blogs or news resources.
The purpose of this post is to show the principle, implementation may not be entirely comme il faut, since the author is not prof. a programmer, but an amateur.

So the challenge

News is stored in a MySQL table of the type: It is

necessary for each news, when displayed on the page, to determine the most similar from the same table.
Here we are interested in the content of the title, lead, body fields. For simplicity, we will assume that we are creating everything from scratch and will not consider the need to process existing records.

Tags field

We add the tags field (in fact, these are pseudo-tags, but they will not be displayed anywhere on the site - this field is necessary only for comparing texts). Indicate the field type as VARCHAR (512) and add an index of type fulltext (FULLTEXT (tags)).

Pseudo-tag generation

We will generate pseudo-tags from the title, lead, body fields before writing the news to the database (immediately before the INSERT statement). To do this, download phpmorphy and dictionaries from here .

To exclude unimportant words (stop words), create an array of $ stopwords, we will use a text file for stop words (for example , save it as stopwords.txt).

$stopwords=explode("\n", file_get_contents("stopwords.txt"));

Next, connect phpmorphy and its dictionaries, combine title, lead and body and run all the words through phpmorphy.

Pseudo-tag generation


$lowercaseLetters = array("'а'", "'б'", "'в'", "'г'", "'д'", "'е'", "'ё'", "'ж'", "'з'", "'и'", "'й'", "'к'", "'л'", "'м'", "'н'", "'о'", "'п'", "'р'", "'с'", "'т'", "'у'", "'ф'", "'х'", "'ц'", "'ч'", "'ш'", "'щ'", "'ъ'", "'ы'", "'ь'", "'э'", "'ю'", "'я'");
$uppercaseLetters = array("'А'", "'Б'", "'В'", "'Г'", "'Д'", "'Е'", "'Ё'", "'Ж'", "'З'", "'И'", "'Й'", "'К'", "'Л'", "'М'", "'Н'", "'О'", "'П'", "'Р'", "'С'", "'Т'", "'У'", "'Ф'", "'Х'", "'Ц'", "'Ч'", "'Ш'", "'Щ'", "'Ъ'", "'Ы'", "'Ь'", "'Э'", "'Ю'", "'Я'");
function cyrUpper($str)
{
global $lowercaseLetters;
global $uppercaseLetters;
return str_replace("'", "", preg_replace($lowercaseLetters, $uppercaseLetters, $str));
}
function cyrLower($str)
{
global $lowercaseLetters;
global $uppercaseLetters;
return str_replace("'", "", preg_replace( $uppercaseLetters,$lowercaseLetters, $str));
}
function cleanUP ($new_string)
{ 
//$new_string=nl2br($new_string);
 $new_string= str_replace("-"," ",$new_string);
  $new_string= str_replace("\r\n"," ",$new_string);
  $new_string= str_replace("\r"," ",$new_string);
    $new_string= str_replace("\n"," ",$new_string);
  $new_string= str_replace("."," ",$new_string);
$new_string = ereg_replace("[^0-9 абвгдеёжзийклмнопрстуфхцчшщъыьэюяАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ]", "",$new_string );
return $new_string;
}
require_once( 'morphy/src/common.php');
$text=cleanUP($_REQUEST[title]." ".$_REQUEST[lead]." ".$_REQUEST[body]." ");
$aText = explode(' ',$text);
        $aPort = array();
        $aMorph = array();
        foreach ($aText as $word)
            $aMorph[] = cyrUpper($word);//нужно в вин1251 давать не сьедение
        // set some options
        $opts = array(
            'storage' => PHPMORPHY_STORAGE_FILE,
            // Extend graminfo for getAllFormsWithGramInfo method call
            'with_gramtab' => false,
            // Enable prediction by suffix
            'predict_by_suffix' => true,
            // Enable prediction by prefix
            'predict_by_db' => true );
      $dir = 'morphy/dicts';
$lang = 'ru_RU';
        // Create descriptor for dictionary located in $dir directory with russian language
        $dict_bundle = new phpMorphy_FilesBundle($dir, 'rus');
        // Create phpMorphy instance
        try {
            $morphy = new phpMorphy($dict_bundle, $opts);
        } catch(phpMorphy_Exception $e) {
            throw new Exception('Error occured while creating stemmer instance: ' . $e->getMessage());
        }
        try {
            if($getroot==22)
                $pseudo_root = $morphy->getPseudoRoot($aMorph);//можно либо взять корни слов
            else
                $pseudo_root = $morphy->getBaseForm($aMorph);//либо базовую форму
            //для нашей задачи $getroot=TRUE
        } catch(phpMorphy_Exception $e) {
            throw new Exception('Error occured while text processing: ' . $e->getMessage());
        }
foreach ($pseudo_root as $roots){
$slovo=cyrLower($roots[0]);
if (strlen( $slovo)>3 && !in_array($slovo,$stopwords) && count($roots)==1 ) {
$tags.=$slovo." ";  }
}
}

The resulting list of tags in the variable $ tags is written in acc. table field. As a result, for each news in this field there will be a list of words that we will use for comparison.

Example

Source text

Samsung has begun production of solid-state hard drives using three-dimensional memory V-NAND. The technology allows to increase the volume of drives, and also provides 2 times higher speed of information transfer and increases the reliability of devices up to 10 times. Currently created SSD disks with a volume of 480 and 960 GB, only for corporate servers. As for home computers, there were no specific release dates.

Generated word list:

device only increase technology company solid-state time created speed reliability server production allow to increase provide transfer memory volume start drive moment corporate specific computer touch use information hard drive high release three-dimensional

SQL query

Now the most interesting is this SQL query will be used to determine similar records:

 SELECT * FROM news WHERE  MATCH (tags)  AGAINST ('[ список псевдо тэгов ]'  ) > [значение релевантности]

Here, the relevance value is the “similarity” of the texts - experiment (start with one)

Tags:

Related News Using PHP, phpmorphy and MySQL