在MySQL字段中搜索并替换多个HTML和文本片段

Question

我注意到替换对于这个问题的目的是什么。整体而言，这些部分按照MySQL数据库中1000个条目的顺序出现。我需要理解处理多个替换（如何这样做）以及是否通过php函数或通过mysql中的命令行来执行。

替换空虚以下（代码可以在地方有所不同）：

<div style="margin-top: 3em;"> 
<h3>The Incandescent Light Bulb Lives!</h3> 
<strong><strong><strong> </strong></strong></strong> 
<div style="float: left; background-color: #c5c5c5; width: 320px; margin: 0px 8px 22px 0px; padding: 8px; text-align: center;"> 

<img src="images/image.jpg" alt="alt_text" width="292" height="250" /> 

<strong>Listen Now or Download for Later</strong> 

换上不同的音频标签结构

{audio}Why Congress Cannot Impose A Uniform Rule Of Tyranny ||http://mikesmith.com/mikes_audio/Dec_2011/20111219_church__sponsored.mp3{/audio} 

以下替换空虚如下：

<a class="jcepopup" dir="ltr" href="images/stories/Allison/help_with_audio_player.jpg" target="_blank">Help with Audio Player</a> 

删除该部分中的所有html：

</div> 
<div style="margin-top: 3em;"> 

2011 Mike Smith 

Hey folks, its Mike Mike Smith with today's update. 

</div> 
<div style="margin-top: 3em;"> 

The incandescent lightbulb is back, it is legal and the ban, set to begin on January 1, 2012 has been repealed. That is the news across the wires today but it is only partially true. The Republicans in the house stuffed a provision in the $1 Trillion omnibus spending bill that prevents the Department of Energy from spending any money to enforce the ban which is still on the books. This begs the question for conservatives to answer: if Congress can forbid and or defund unconstitutional activities that regulatory agencies are making (like enforcing light bulb bans) then why cant the same Congress just un-fund enforcement of say The Endangered Species Act or the equally nefarious activities of the NLRB?Why couldnt Congress not fund ObamaCare? Why couldnt Congress un fund GM or AIG or bailouts to Fannie Mae and Freddie Mac? The answer is of course, Congress CAN ban those expenditures or just not fund them but this would take political courage and outside of standing shoulder to shoulder with Tom Edison what courage have we seen from them? The same can be said of almost any agency or activity that federal regulators are engaging in and people are demanding relief from. This seems like a perfect way to teach some constitutionalism to new members AND to secure Congressional conservatives budget cutter bonafides too but alas, the light bulb act seems to be a loss leader for censuring big government by starving it of funds. 

The next time you hear rigorous debate about how best to minimize the impact that the Feds have in say our public schools remember that their activity is funded by the Congress that proved it does not have to spend a dime on anything it does not want to including other bright ideas like studying monkeys high on the DEAs cocaine. Now who is the dim bulb that appropriated funds for that!? 

</div> 

替换该部分具有不同的视频嵌入结构：

<div style="clear: both; margin-top: 8px; margin-bottom: 8px;"><object width="720" height="420" classid="clsid:02bf25d5-8c17-4b23-bc80-d3488abddc6b" codebase="http://www.apple.com/qtactivex/qtplugin.cab#version=6,0,2,0"><param name="src" value="http://mikesmith.com/mikesmith_movies/smithdoc/smith_hd_121911.mov" /><param name="autoplay" value="false" /><param name="cache" value="true" /><embed width="720" height="420" type="video/quicktime" src="http://mikesmith.com/mikesmith_movies/smithdoc/smith_doc_hd_121911.mov" autoplay="false" cache="true" /></object></div> 

替换空虚以下：

<div style="float: right; margin-left: 8px; margin-bottom: 8px; clear: both;"></div> 
<div style="clear: both;"> 
<table style="background-color: #cccccc; border-collapse: collapse; border-color: #000000; border-style: solid;" summary="summary" border="3" cellspacing="0" cellpadding="8"> 
<tbody> 
<tr valign="top"> 
<td style="width: 100%;" valign="middle">Become a <a title="Become a 24/7 Backstage Pass member today and access exclusive members-only audio, video, and more." href="index.php/join-24-7/view-available-memberships" target="_blank">Become a 24/7 Backstage Pass member today.</a> to hear all of Mike's past interviews with Professor Gutzman, Ron Paul and more as well as exclusive access to the Post Show Show, Church Doctrine, and subscriber-only downloads.</td> 
</tr> 
</tbody> 
</table> 
</div> 
</div> 
{sidebar id=51} 

Answer 1

这里是它是如何可以做到

写一个PHP脚本实现以下伪代码（完整的代码，它会更好，询问了一些自由职业者的工作现场）：

运行一个查询SELECT fields to replace FROM table
通过所有记录迭代，完成替换操作，此起彼伏：

while($row=mysql_fetch_assoc($res)) { 
    $data = $row['field']; 
    $data = preg_replace('pattern','replace', $data); 
    $data = preg_replace('pattern','replace', $data); 
    $data = preg_replace('pattern','replace', $data); 
    // and so on, whaever replacements you need. 

    // same goes for the other fields, if any: 
    $data1 = $row['field1']; 
    $data1 = preg_replace('pattern','replace', $data1); 
    $data1 = preg_replace('pattern','replace', $data1); 

    //and finally, by having all your replacements done, run an UPDATE query 

    $data = mysql_real_escape_string($data); 
    $data1 = mysql_real_escape_string($data1); 
    $sql = "UPDATE table SET field='$data', field1='$data1' WHERE id=".$row['id']; 
    mysql_query($sql) or trigger_error(mysql_error()." ".$sql); 
} 

Answer 2

让我们第二个作为一个例子，因为它是最短的。然后我会再一起展示一个简短的例子。

UPDATE `table` SET `column` = REPLACE(`column`,'{audio}Why Congress Cannot Impose A Uniform Rule Of Tyranny ||http://mikesmith.com/mikes_audio/Dec_2011/20111219_church__sponsored.mp3{/audio}','') 
WHERE `column` LIKE '%{audio}Why Congress Cannot Impose A Uniform Rule Of Tyranny ||http://mikesmith.com/mikes_audio/Dec_2011/20111219_church__sponsored.mp3{/audio}%'; 
UPDATE `table` SET `column` = REPLACE(`column`,'BAD TEXT','') 
WHERE `column` LIKE '%BAD TEXT%'); 

这是否合理并解决问题？