How Can I Do This Simple MYSQL Search/Replace?

[MatthewWoodward]

Hey,


I am wondering if anyone can help me construct a quick mysql query to remove some data en masse.


Here are some example links from posts & pages bolding the bits I want to remove in 1 swift query-

<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" [B]onClick="_gaq.push(['_trackEvent', 'Affiliate', 'SocialNetworkAutoPoster', '/income-report/monthly-income-growth-traffic-report-january-2015/',, false]);"[/B] target="_blank">Social Network Auto Poster</a>


<li><strong><a href="http://www.matthewwoodward.co.uk/tips/download-private-seo-tool-free-rankcracker/" [B]onClick="_gaq.push(['_trackEvent', 'Affiliate', 'RankCracker', '/income-report/monthly-income-growth-traffic-report-january-2015/',, false]);"[/B] rel="nofollow" target="_blank">RankCracker</a></strong> - $139.13</li>


<a href="/SEOPowerSuite" [B]onClick="_gaq.push(['_trackEvent', 'Affiliate', 'SEOPowerSuite', '/tools-of-the-trade/',, false]);"[/B] rel="nofollow" target="_blank" class="recommend"><strong>SEO Powersuite (Limited free version available)</strong></a>


<a [B]onClick="_gaq.push(['_trackEvent', 'Affiliate', 'BuyProxies', '/tools-of-the-trade/',, false]);"[/B] href="/BuyProxies" rel="nofollow" target="_blank" class="recommend"><strong>Buyproxies</strong></a>

As you can see there are some variance in the links, sometimes elements are in a differnet order, sometimes there is a custom class etc


Is there a SQL query I could execute that would remove all of the bold onclick="" elements across all posts/pages in one swoop rather than me manually editing around 1500 links?


Thanks

 

[lancis]

There is no regexp replacement in MySQL, and so such a query does not exists.

There is a fast alternative:
1. mysqldump your database to a text file
2. replace the pattern using some text editor
3. insert the contents back into database

 

[MatthewWoodward]

Hi,

Now that sounds like a solution I could work with - I guess the regex here is just the * to construct the command?

 

[lancis]

This is all they have regex-wise: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Only matching, no replacing.

 

[mazak85]

You don't need regex - a simple query is enough

SET @var1 = '<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'SocialNetworkAutoPoster\', \'/income-report/monthly-income-growth-traffic-report-january-2015/\',, false]);" target="_blank">Social Network Auto Poster</a>';

SELECT CONCAT(substr(@var1, 1, LOCATE('onClick',@var1) - 1), substr(@var1, LOCATE(']);"',@var1) + 5, LENGTH(@var1)));

works with all the examples

SET @var1 = '<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'SocialNetworkAutoPoster\', \'/income-report/monthly-income-growth-traffic-report-january-2015/\',, false]);" target="_blank">Social Network Auto Poster</a>';
SET @var2 = '<li><strong><a href="http://www.matthewwoodward.co.uk/tips/download-private-seo-tool-free-rankcracker/" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'RankCracker\', \'/income-report/monthly-income-growth-traffic-report-january-2015/\',, false]);" rel="nofollow" target="_blank">RankCracker</a></strong> - $139.13</li>';
SET @var3 = '<a href="/SEOPowerSuite" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'SEOPowerSuite\', \'/tools-of-the-trade/\',, false]);" rel="nofollow" target="_blank" class="recommend"><strong>SEO Powersuite (Limited free version available)</strong></a>';
SET @var4 = '<a onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'BuyProxies\', \'/tools-of-the-trade/\',, false]);" href="/BuyProxies" rel="nofollow" target="_blank" class="recommend"><strong>Buyproxies</strong></a>';


SELECT CONCAT(substr(@var1, 1, LOCATE('onClick',@var1) - 1), substr(@var1, LOCATE(']);"',@var1) + 5, LENGTH(@var1)))
union all
SELECT CONCAT(substr(@var2, 1, LOCATE('onClick',@var2) - 1), substr(@var2, LOCATE(']);"',@var2) + 5, LENGTH(@var2)))
union all
SELECT CONCAT(substr(@var3, 1, LOCATE('onClick',@var3) - 1), substr(@var3, LOCATE(']);"',@var3) + 5, LENGTH(@var3)))
union all
SELECT CONCAT(substr(@var4, 1, LOCATE('onClick',@var4) - 1), substr(@var4, LOCATE(']);"',@var4) + 5, LENGTH(@var4)));

and the result

<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" target="_blank">Social Network Auto Poster</a>
<li><strong><a href="http://www.matthewwoodward.co.uk/tips/download-private-seo-tool-free-rankcracker/" rel="nofollow" target="_blank">RankCracker</a></strong> - $139.13</li>
<a href="/SEOPowerSuite" rel="nofollow" target="_blank" class="recommend"><strong>SEO Powersuite (Limited free version available)</strong></a>
<a href="/BuyProxies" rel="nofollow" target="_blank" class="recommend"><strong>Buyproxies</strong></a>

 

[MatthewWoodward]

You don't need regex - a simple query is enough

SET @var1 = '<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'SocialNetworkAutoPoster\', \'/income-report/monthly-income-growth-traffic-report-january-2015/\',, false]);" target="_blank">Social Network Auto Poster</a>';

SELECT CONCAT(substr(@var1, 1, LOCATE('onClick',@var1) - 1), substr(@var1, LOCATE(']);"',@var1) + 5, LENGTH(@var1)));

works with all the examples

SET @var1 = '<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'SocialNetworkAutoPoster\', \'/income-report/monthly-income-growth-traffic-report-january-2015/\',, false]);" target="_blank">Social Network Auto Poster</a>';
SET @var2 = '<li><strong><a href="http://www.matthewwoodward.co.uk/tips/download-private-seo-tool-free-rankcracker/" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'RankCracker\', \'/income-report/monthly-income-growth-traffic-report-january-2015/\',, false]);" rel="nofollow" target="_blank">RankCracker</a></strong> - $139.13</li>';
SET @var3 = '<a href="/SEOPowerSuite" onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'SEOPowerSuite\', \'/tools-of-the-trade/\',, false]);" rel="nofollow" target="_blank" class="recommend"><strong>SEO Powersuite (Limited free version available)</strong></a>';
SET @var4 = '<a onClick="_gaq.push([\'_trackEvent\', \'Affiliate\', \'BuyProxies\', \'/tools-of-the-trade/\',, false]);" href="/BuyProxies" rel="nofollow" target="_blank" class="recommend"><strong>Buyproxies</strong></a>';


SELECT CONCAT(substr(@var1, 1, LOCATE('onClick',@var1) - 1), substr(@var1, LOCATE(']);"',@var1) + 5, LENGTH(@var1)))
union all
SELECT CONCAT(substr(@var2, 1, LOCATE('onClick',@var2) - 1), substr(@var2, LOCATE(']);"',@var2) + 5, LENGTH(@var2)))
union all
SELECT CONCAT(substr(@var3, 1, LOCATE('onClick',@var3) - 1), substr(@var3, LOCATE(']);"',@var3) + 5, LENGTH(@var3)))
union all
SELECT CONCAT(substr(@var4, 1, LOCATE('onClick',@var4) - 1), substr(@var4, LOCATE(']);"',@var4) + 5, LENGTH(@var4)));

and the result

<a href="http://www.matthewwoodward.co.uk/get/social-network-auto-poster/" rel="nofollow" target="_blank">Social Network Auto Poster</a>
<li><strong><a href="http://www.matthewwoodward.co.uk/tips/download-private-seo-tool-free-rankcracker/" rel="nofollow" target="_blank">RankCracker</a></strong> - $139.13</li>
<a href="/SEOPowerSuite" rel="nofollow" target="_blank" class="recommend"><strong>SEO Powersuite (Limited free version available)</strong></a>
<a href="/BuyProxies" rel="nofollow" target="_blank" class="recommend"><strong>Buyproxies</strong></a>

Wow that's quite a chunk of code thank you - how can I run that as a SQL query to find/replace in the wp_posts table?

Please pm me your paypal address

 

[spectrejoe]

Wow that's quite a chunk of code thank you - how can I run that as a SQL query to find/replace in the wp_posts table?

Please pm me your paypal address

Pretty straight forward aren't you?


Keep up the good work though, always interesting to read your blog

 

[mazak85]

Wow that's quite a chunk of code thank you - how can I run that as a SQL query to find/replace in the wp_posts table?

Didn't notice you want to update WP posts. There can be multiple links in a single post and updating all of them is a bit tricky. But there is another easy way to do it

1. login to phpMyAdmin, go to the table wp_posts and export is as a SQL file
2. make a copy of the downloaded file
3. download http://www.editpadlite.com
4. open the .sql file, go to the 'Search' menu, select 'Multi-Line Search Panel' and then 'Prepare to Search'
5. at the bottom of the screen you should see the search and replace window
6. select the 'Regex' option
7. on the left side put

onClick.*]\);"

8. click the 'Highlight all' icon [the magnifying glass] and scroll through the file to see if everything is ok [if only the correct parts are highlighted]
9. if everything is ok click 'Replace all' [on the left side of 'Highlight all']
10. save the file
11. go back to phpMyAdmin and delete everything from wp_posts [you can use the SQL editor and type 'delete from wp_posts']
12. import the saved file

I can't guarantee you it will work but it should.

Please pm me your paypal address

That's not necessary - I help because I like it not to earn money

 

[MatthewWoodward]

Pretty straight forward aren't you?


Keep up the good work though, always interesting to read your blog

Haha yeah I am!

Didn't notice you want to update WP posts. There can be multiple links in a single post and updating all of them is a bit tricky. But there is another easy way to do it

1. login to phpMyAdmin, go to the table wp_posts and export is as a SQL file
2. make a copy of the downloaded file
3. download http://www.editpadlite.com
4. open the .sql file, go to the 'Search' menu, select 'Multi-Line Search Panel' and then 'Prepare to Search'
5. at the bottom of the screen you should see the search and replace window
6. select the 'Regex' option
7. on the left side put

onClick.*]\);"

8. click the 'Highlight all' icon [the magnifying glass] and scroll through the file to see if everything is ok [if only the correct parts are highlighted]
9. if everything is ok click 'Replace all' [on the left side of 'Highlight all']
10. save the file
11. go back to phpMyAdmin and delete everything from wp_posts [you can use the SQL editor and type 'delete from wp_posts']
12. import the saved file

I can't guarantee you it will work but it should.




That's not necessary - I help because I like it not to earn money

That is working beautifully the only thing that is messing up is when doing the regex replacement it also needs to remove a trailing space, eg there is a double space between the HREF elements rather than a single one.

I tried adding the space onto the end of the query you gave but that didn't work/replace anything.

And while you like helping people, I bet you like beer as well - so I can at least buy you a couple, I'm English after all its how we work

 

[MatthewWoodward]

Aha figured it out-

onClick.*]\);"\s

Thank you for your help - now hopefully PHPMyAdmin doesn't fall over on the import haha =D

 

[mazak85]

I forgot you won't be able to see the space at the end. You can either use 'onClick.*]\);" ' or ' onClick.*]\);"' [without the ' of course] - both ways work.

 

[ChanzGrande]

When performing SQL queries it is often advisable to make a complete backup of any databases you are editing in case of errors. I have lost more than one database for just simply failing to make a backup in case something goes wrong. This even applies if exporting data as text, and editing it externally. It's always just a good practice to have backups. Other than that, the proposed solutions seem sufficient. Great work folks!

 

[MatthewWoodward]

I forgot you won't be able to see the space at the end. You can either use 'onClick.*]\);" ' or ' onClick.*]\);"' [without the ' of course] - both ways work.

Yes adding the \s on the end actually deleted a bunch of stuff.

The problem is there are 2 instances that need to be tackled-

<a href="" onclick="" target="_blank"></a>
<a href="" target="_blank" onclick=""></a>

With the top one need to remove onclick="" with the trailing space

But with the bottom one it needs to be removed without a trailing space

Any ideas on how to achieve that? I didn't think about that before hand

 

[MatthewWoodward]

I forgot you won't be able to see the space at the end. You can either use 'onClick.*]\);" ' or ' onClick.*]\);"' [without the ' of course] - both ways work.

I don't think the original regular expression is selecting properly, take a look at this http://i.imgur.com/Isgu8bK.png

 

[HelloInsomnia]

I don't think the original regular expression is selecting properly, take a look at this http://i.imgur.com/Isgu8bK.png

It's being a bit greedy, try this:

onClick=\".*?]\);\"

 

[MatthewWoodward]

It's being a bit greedy, try this:

onClick=\".*?]\);\"

That is the nail on the head thank you.

I have modified it into 2 separate find/replace statements to match the 2 types of URL below but also remove any extra space-

<a href="http://www.matthewwoodward.co.uk/get/tweetattackspro/" rel="nofollow" target="_blank" onClick="_gaq.push([''_trackEvent'', ''Download'', ''Tutorial Resources'', ''ContentUpgrade-TweetAttacksPro'',, false]);">
<a onClick="_gaq.push([''_trackEvent'', ''Download'', ''Tutorial Resources'', ''ContentUpgrade-FreeSeoTools'',, false]);" href="http://www.matthewwoodward.co.uk/wp-content/uploads/2014/upgrades/Tools-Of-Trade.zip" target="_blank">

First find this-

<a onClick=\".*?]\);\"

and replace with

<a

Then find this

 onClick=\".*?]\);\"

and replace with nothing

Going to do a bigger test run now - thank you for all your help!