如何从MySQL数据库中查找和替换文本中的单词？

A have a sentence:

有一句话:

"How to find and replace word in text from mysql database?"

“如何在mysql数据库中查找和替换文本中的单词?”

And MySQL table words, with to 3 column id, word and replaceWord. I have more than 4000 words in databese.

和MySQL表的单词,用3列id,word和replaceWord。我在数据库中有超过4000个单词。

Table:

id     word     replaceWord
 1     text     sentence
 2     word     letter
 3     mysql    MySQL
 4     ..       ...
 5     ..       ...
 6     ..       ...

Result:

"How to find and replace letter in sentence from MySQL database?"

“如何从MySQL数据库中查找和替换句子中的字母?”

I know how to do this without database, but i need database.

我知道如何在没有数据库的情况下这样做,但我需要数据库。

 <?php
$text="How to find and replace word in text from mysql database?";
$replaceWord=array(  "text" => "sentence", "word" => "letter", "mysql" => "MySQL");
echo strtr($tekst, $replaceWord);
?>

4 个解决方案

#1

update YourTable, Words
    set YourTable.YourColumn = replace(YourTable.YourColumn, Words.word, Words.replaceWord)

#2

//load all replacements
$result = mysql_query("SELECT * FROM YourTableNameHere");
//replace all words
$words = array();
$replacewords =array();
while ($row = mysql_fetch_assoc($result)) {
    $words[] = $row['word'];
    $replacewords[] = $row['replaceword'];
}
$text = str_replace($words,$replacewords);

If you need preg_replace as well: You must add the column isPattern to your table, then you can do this:

如果你还需要preg_replace:你必须将列isPattern添加到你的表中,然后你可以这样做:

//load all replacements
$result = mysql_query("SELECT * FROM YourTableNameHere");
//replace all words
$words = array();
$replacewords = array();
$preg_words = array();
$preg_replacewords = array();
while ($row = mysql_fetch_assoc($result)) {
    if(!$row['isPattern']){        
        $words[] = $row['word'];
        $replacewords[] = $row['replaceword'];
    }
    else{
        $preg_words[] = $row['word'];
        $preg_replacewords[] = $row['replaceword'];
    }
}
$text = str_replace($words,$replacewords);
$text = $preg_replace($preg_words,$preg_replacewords);

#3

select REPLACE(fieldOrString, SearchString, ReplaceString) from table

#4

Here's a terrible idea in terms of scalability: you could just iterate over the sentence word by word, and on each one do a query for where the word exists in 'word'. If it does exist (returns data) then do a replace with the content from 'replaceWord'

在可伸缩性方面,这是一个糟糕的想法:你可以逐字逐句地迭代句子,并在每一个上查询单词在'word'中的位置。如果确实存在(返回数据),则用'replaceWord'中的内容替换

EDIT: Not totally database based, but more so than your current version.

编辑:不完全基于数据库,但比你当前的版本更多。

#1

update YourTable, Words
    set YourTable.YourColumn = replace(YourTable.YourColumn, Words.word, Words.replaceWord)

#2

//load all replacements
$result = mysql_query("SELECT * FROM YourTableNameHere");
//replace all words
$words = array();
$replacewords =array();
while ($row = mysql_fetch_assoc($result)) {
    $words[] = $row['word'];
    $replacewords[] = $row['replaceword'];
}
$text = str_replace($words,$replacewords);