SQL string to replace content between 2 words

Question

I have searched for this and many others talk about replace command but I don`t wanna do a misstake with that.

My case is: I have a Wordpress blog and a script insert content in the posts. Now I wanna delete this new content but to keep the old one.

Its something like "This is content of a post about me" its the old content, and now the script insert another line, and the value of the field looks like "This is content of a post about me <div class="spnsor"><a [...]</div>".

In this part I have a different link from a post to post. But is the same construction with the same div and class.

I have over 2000 posts so is to much to edit every post.

How can I fix this from Mysql?

Its ok this command? UPDATE wp_posts SET post_content = REPLACE ( post_content, '<div class="spnsor">*</div>', '' )

there was a div tag looks like >div< class="sponsor"<*>/div – Cristescu Bogdan Feb 26 '11 at 18:46 — Cristescu Bogdan, Feb 26 '11 at 18:46

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

If you have access to the MySQL server, you can look here : How to do a regular expression replace in MySQL?

If not, the easiest way is to do the replacement in a php script.

EDIT:

a simple php script. I have not tested it, but it should work (try before on a local backup)

<?php

    $co = mysql_connect("your server", "your login", "your password");
    mysql_set_charset('utf8');
    mysql_select_db("database name", $co);

    $selectQuery = "SELECT ID, post_content FROM wp_posts";

    $res = mysql_query($selectQuery, $co) or exit(mysql_error());

    while($row = mysql_fetch_assoc($res))
    {
        $updateQuery = "UPDATE wp_posts SET post_content = '" . addslashes(preg_replace("/<div class=\"sponsor\">(.*)<\/div>/", '', $row['post_content'])) . "' WHERE ID = " . $row['ID'];
        mysql_query($updateQuery) or exit(mysql_error());
    }

    mysql_close($co);

?>

edited May 23 '17 at 12:04

Community

1
1

answered Feb 26 '11 at 19:43

Xavinou

802
1
6
18

I have access but I`m dump! I need the exactly command to run! – Cristescu Bogdan Feb 26 '11 at 19:46
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 're sleeping alone. No house no where to call a home. Tell me what I'm meant to s' at line 1 – Cristescu Bogdan Feb 26 '11 at 20:21
Try with `addslashes(preg_replace("/
(.*)<\/div>/", '', $row['post_content']))`
– Xavinou Feb 26 '11 at 20:28
Now it was ok, the link is gone but this change the post content. I use UTF8 for text, I`m romanian and my language has some characters different than english .... they disappeared and instead i have "?" – Cristescu Bogdan Feb 26 '11 at 20:36
OK, after mysql_select_db, add : `mysql_query("SET NAMES 'utf8'");` and `mysql_query('SET CHARACTER SET utf8');`. I have update the initial script ;) – Xavinou Feb 26 '11 at 20:50
The same effect ... I use this ... `mysql_select_db("jorjetta_blog", $co); mysql_query("SET NAMES 'utf8'"); mysql_query("SET CHARACTER SET utf8");` – Cristescu Bogdan Feb 26 '11 at 21:55
mysql_set_charset('utf8'); this is the solution! I put it before mysql_select_db – Cristescu Bogdan Feb 26 '11 at 22:11

SQL string to replace content between 2 words

1 Answers1