Hello. The database has a table slovo , its structure:

 id - auto_increment name - само слово( уникальный )

There is a variable: $ text , which contains some text.

There is an entry in the slovo table: id = 1, name = Apple, id = 2, name = tree

 $text = "В корзине лежит яблоко. В поле стоит дерево.";

Question: how to determine whether the variable contains words that are in the slovo table, and if so, select these words in <span id="id_slovo">слово</span> , where id_slovo is the id value for this word from base.

PS Given that there can be phrases.

I would be very grateful for any useful information.

  • Does the database support SQL? - Mikhail Vaysman
  • @MikhailVaysman, Yes, of course, mysql database - iKey
  • Russian morphology need to be considered? - Mikhail Vaysman
  • @MikhailVaysman, preferably of course ... It would be great. But not necessarily. The main thing is that the register (uppercase, lowercase) did not pay attention, then the network, the words apple and Apple - were taken as the same thing. - iKey
  • add this information to the question - Mikhail Vaysman

7 answers 7

 //Сначала нужно найти все слова, бьем фразу в массив по пробелам $words = explode(' ', $text); //Затем проверяем каждое слово: foreach($words as $word) { $word = trim($word); //убираем пробелы //Проверяем в базе, любимым адаптером выполняя запрос //Само собой никто не мешает заменить ILIKE на LIKE или = $wordId = $msSql->fetchOne("SELECT id FROM slove WHERE name ILIKE '{$word}'"); //Если в базе такое слово есть, то заменяем его в тексте на нужный span if (!empty($wordId)) { $text = str_replace($word, "<span id='$wordId'>$word</span>", $text); } }
  • And how in this case to be with phrases? - iKey
  • You can do the same with crutches. You take 2 consecutive words, 3 consecutive words, 4 ... in general, until you get bored. Of course it will work, but very badly. Slowly and the phrase "apple on the tree" will not be found if there are "apples on the tree" in the database. Means mysql full-text search I do not know how to solve, probably nothing. If the project is not old, and such full-text search functionality is really needed, then the word plate needs to be moved to postgres. There are native full-text search mechanisms that will simply find an elegant apple on a tree. - Denis Matafonov
  • And in general - postgres is exactly the same as mysql for beginners, but there are a lot of cool things that are not in mysql. This I do not start holivar if that :) And there and there I did a lot of things. - Denis Matafonov
  • @DenisMatafonov, say pzhl, but where do the commas and periods from $text explode when explode into an array? Rather, they do not fall into the array? - TimurVI
  • one
    @TimurVI any punctuation marks can be cut in the place where the spaces are removed, something like $word = trim($word, [' ', ',', '.', ';']); - Denis Matafonov
 $text = "В корзине лежит яблоко. В поле стоит дерево."; // все слова из таблицы, присутствующие в тексте получаем одним запросом $result = mysql_query("SELECT id, name FROM slovo WHERE INSTR('" . mysql_escape_string($text) . "', name)"); // проходим по всем словам и делаем замену while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $text = str_ireplace($row['name'], '<span id="word-' . $row['id'] . '">' . $row['name'] . '</span>', $text); }

    PHP MySQL PDO

    I think that such a solution is suitable.

      class ConnectPDO { private $connect = array( 'login' => 'DB_USER', 'password' => 'DB_PASS', 'db' => 'slovo_db' ); private $dbc; public function __construct() { $db_name=$this->connect['db']; $this->dbc = new PDO("mysql:host=localhost;dbname=$db_name;charset=utf8", $this->connect['login'], $this->connect['password']); } } $connection=new ConnectPDO(); $sth = $connection->dbc->prepare("SELECT * FROM `slovo` WHERE `name` LIKE %:name% "); $data = array(); $text = "asd sad asd as dasd asd asdasdas das das das"; //explode делает массив из фразы с которым далее очень просто работать $text_aray = explode(" ", $text); foreach ($text_aray as $each) { $sth->execute(array('name'=>$each)); $result = $sth->fetchAll(); $data[] = $result; } // отобразить все слова из базы foreach ($data as $data_block){ foreach ($data_block as $each_row){ echo '<span id="'.$each_row['id'].'">'.$each_row['name'].'</span><br>'; } }

    parameters that need to be replaced with their own:

     DB_USER юзер mysql DB_PASS пароль mysql slovo_db база mysql
    • one
      opening a new connection in pdo in a loop is a very bad practice. This is a very bad example of using a prepared expression. never do that. - Alex
    • then you can simply create a connection in __construct and use an array to transfer `%: name%", array ('name' => $ each)); `what is the best practice - fonjeekay
    • this is by no means best practice! I recommend once again to read about the principles of the PDO. No need to prepare the request again. It is enough to prepare the request only once and then only pass the parameters. You prepare the request every time in the cycle. - Alex
    • true. but without reconnection. agree. - fonjeekay
    • You did not understand. 1 - no need to re-do the connection to the database, especially in the cycle (you corrected this). 2 - in pdo it is enough just to prepare a request once and then only substitute parameters into a prepared request. And for some reason you do it every time. - Alex
     $text = "В корзине лежит яблоко. В поле стоит дерево."; //заменяем несколько пустых символов на один (чтобы избежать ситуации, когда из-за нескольких пробелов сочетания не будут найдены) $text = preg_replace('/\s{2,}/', ' ', $text); //подключаемся к базе данных (на примере MYSQL, подправите под свою базу) и выбираем // все слова из таблицы, присутствующие в тексте получаем одним запросом, сортируем по уменьшению длины слов (фраз). // Это нужно, чтобы исключить вариант когда при наличии 2-х слов/фраз "яблоко", "лежит яблоко" после замены первого, // второе не получится уже заменить, так как между ними уже будет span try{ $link = new PDO( 'mysql:host=your-hostname;dbname=your-db;charset=utf8mb4', 'your-username', 'your-password', array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => false ) ); $handle = $link->prepare('SELECT id, name FROM slovo WHERE INSTR(?, name) ORDER BY CHAR_LENGTH(name) DESC'); $handle->bindValue(1, $text, PDO::PARAM_STR); $handle->execute(); $result = $handle->fetchAll(PDO::FETCH_OBJ); // проходим по всем словам и делаем замену foreach($result as $row){ //экранируем служебные символы " . \ + * ? [ ^ ] $ ( ) { } = ! < > | : - " $word = preg_quote($row->name); //используем регулярное выражение чтобы: //1) после замены регистр слов не был изменён //2) были заменены полностью слова а не только вхождения, например при слове "дерево", слово "деревообработка" останутся не тронутыми $text = preg_replace('/(^|\W)(' . $word . ')($|\W)/iu', '\\1<span id="word-' . $row->id . '">\\2</span>\\3', $text); } } catch(PDOException $ex){ print($ex->getMessage()); }

    if you want to use morphology (for example, the phpMorphy library), then I would recommend:

    1) if the number of records in the slovo table is not large, do a full selection

    $ handle = $ link-> prepare ('SELECT id, name FROM slovo ORDER BY CHAR_LENGTH (name) DESC'); $ handle-> execute ();

    2) if the number of records in the slovo table is large, then to reduce the replacement time, add a column in the slovo table into which to put the word root (can be automatically through the library) and filter INSTR (?, New_column) by it.

    3) After when sorting words before

     $word = preg_quote($row->name);

    make download all variants of the word, and after make preg_replace for each option.

    The decision was not formalized as a class, since, as I understand it, this will be part of your task. here is focused only on the main points.

    • And if the name will contain one of the service characters RegEx? - P. Fateev
    • @ P.Fateev added escaping of service characters using preg_quote - Alex
    • if you put cons, then please write for what, perhaps it is very easy to fix .... - Alex

    example

      <?php # there is no information about where this text is from... we have it in # varriable $text $text = "В корзине лежит яблоко. В поле стоит дерево."; #one row result from mysqli_fetch_assoc $replacement_arr = array("id"=>1, "name"=>"яблоко"); echo 'before:'.$text."\n\t"; f_process_text($text, $replacement_arr); echo 'after:'.$text."\n"; function f_process_text(&$text, $replacement_arr){ #set morphological phrases for $replacement_arr["name"]; $phrases = f_get_phrases($replacement_arr["name"]); list($patterns, $replacements) = f_get_prepared_fo_replacement_arrs($phrases, $replacement_arr["id"]); $text = preg_replace( $patterns, $replacements, $text); } #return array function f_get_phrases($words){ $result = array(); $result[] = $words; #to add phrases it is recomended to use [phpMorphy](http://phpmorphy.sourceforge.net/) return $result; } function f_get_prepared_fo_replacement_arrs($phrases, $id){ $patterns = array(); $replacements = array(); foreach($phrases as $phrase){ $patterns[] = '/'.$phrase.'/'; $replacements[] = '<span id="'.$id.'">'.$phrase.'</span>'; } return array($patterns, $replacements); } ?>

      Here is my box office:

       <?php /** * Поиск и замена фраз в строке * User: Rochfort */ class PhrasesReplacement { /** * Текст * @var string */ protected $text; /** * Массив фраз для замены * @var array */ protected $phrases; /** * Массив подстрок для замены * @var array */ protected $patterns = []; /** * Массив подстрок на замену * @var array */ protected $replacements = []; /** * PhrasesReplacement constructor. * @param string $text * @param array $phrases */ public function __construct(string $text = '', array $phrases = []) { if (!empty($text)) { $this->text = $text; } if (!empty($phrases)) { $this->phrases = $phrases; } } /** * Получить текст с заменами * @return string */ public function getReplacedText() { if (empty($this->patterns) || empty($this->replacements)) { list($this->patterns, $this->replacements) = $this->getPatterns(); } return preg_replace( $this->patterns, $this->replacements, $this->text); } /** * Формируем массивы для поиска и замены * @return array */ protected function getPatterns() { $patterns = []; $replacements = []; foreach ($this->phrases as $phrase) { $name = preg_quote($phrase['name']); $patterns[] = '/('.$name.')/iu'; $replacements[] = '<span id="'.$phrase['id'].'">$1</span>'; } return [$patterns, $replacements]; } /** * @return string */ public function getText() { return $this->text; } /** * @param string $text * @return PhrasesReplacement */ public function setText(string $text) { $this->text = $text; return $this; } /** * @return array */ public function getPhrases() { return $this->phrases; } /** * @param array $phrases * @return PhrasesReplacement */ public function setPhrases(array $phrases) { $this->patterns = []; $this->replacements = []; $this->phrases = $phrases; return $this; } }

      Work with him like this:

       $text = "В корзине лежит яблоко. В поле стоит дерево."; $phrases = [ ['id' => 1, 'name' => 'корзине лежит'], ['id' => 2, 'name' => 'дерево'], ]; $replacer = new PhrasesReplacement($text,$phrases); echo $replacer->getReplacedText() . "\n"; $text2 = "В корзине ЛеЖаТ Яблоки. В поле стоит дерево."; echo $replacer->setText($text2)->getReplacedText() . "\n"; $phrases = [ ['id' => 1, 'name' => 'лежат яблоки'] ]; echo $replacer->setPhrases($phrases)->getReplacedText() . "\n";

      Well and, accordingly, it will be most convenient to get the values ​​from the database like this:

       $dsn = 'mysql:dbname=dbname;host=127.0.0.1;port=3306'; $user = 'root'; $pass = 'root'; $pdo = new PDO($dsn, $user, $pass); $sql = 'SELECT `id`, `name` FROM `slovo`;'; $stmt = $pdo->prepare($sql); $stmt->setFetchMode(PDO::FETCH_ASSOC); $stmt->execute(); $phrases = $stmt->fetchAll();

        One option :

         $dsn = 'mysql:host=localhost;dbname=...'; $user = '...'; $password = '...'; $text = "В корзине лежит яблоко. В поле стоит дерево."; // исходная строка $max_count_of_replace = count(explode(' ', $text)); // максимальное кол-во замен в исходной строке $opt = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]; try { $pdo = new PDO($dsn, $user, $password, $opt); } catch (PDOException $e) { die('Ошибка: ' . $e->getMessage()); } $sql = 'SELECT `id`, `name` FROM `slovo` WHERE INSTR(:text, `name`)'; $stmt = $pdo->prepare($sql); $stmt->bindValue(':text', $text, PDO::PARAM_STR); $stmt->execute(); // заменяем слова из таблицы, присутствующие в $text $count_of_replace = 0; foreach ($stmt as $row) { $text = str_ireplace($row['name'], "<span id=\"{$row['id']}\">{$row['name']}</span>", $text, $count); $count_of_replace += $count; if ($count_of_replace > $max_count_of_replace) { break; } } echo $text; // результат

        The idea is to use the MySQL function: INSTR() + added a limit on the maximum number of replacements.

        The second option :

        We do not use INSTR() , for example, if you want independence from the DBMS.

         <?php // параметры подключения // ... $text = "В корзине лежит яблоко. В поле стоит дерево."; // исходная строка $max_count_of_replace = count(explode(' ', $text)); // максимальное кол-во замен в исходной строке $opt = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]; try { $pdo = new PDO($dsn, $user, $password, $opt); } catch (PDOException $e) { die('Ошибка: ' . $e->getMessage()); } $sql = 'SELECT `id`, `name` FROM `slovo`;'; $stmt = $pdo->prepare($sql); $stmt->execute(); $count_of_replace = 0; foreach ($stmt as $row) { $text = str_ireplace($row['name'], "<span id=\"{$row['id']}\">{$row['name']}</span>", $text, $count); $count_of_replace += $count; if ($count_of_replace > $max_count_of_replace) { break; } } echo $text; // результат