1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How Can I Do This Simple MYSQL Search/Replace?

Discussion in 'PHP & Perl' started by MatthewWoodward, Mar 10, 2015.

  1. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    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-


    Code:
    <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
     
  2. lancis

    lancis Elite Member

    Joined:
    Jul 31, 2010
    Messages:
    1,683
    Likes Received:
    2,426
    Occupation:
    Entrepreneur
    Location:
    Milky Way
    Home Page:
    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
     
  3. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    Hi,

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

    lancis Elite Member

    Joined:
    Jul 31, 2010
    Messages:
    1,683
    Likes Received:
    2,426
    Occupation:
    Entrepreneur
    Location:
    Milky Way
    Home Page:
  5. mazak85

    mazak85 Regular Member

    Joined:
    Oct 10, 2014
    Messages:
    347
    Likes Received:
    120
    You don't need regex - a simple query is enough

    Code:
    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


    Code:
    
    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


    Code:
    <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>
    
    
     
  6. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    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
     
  7. spectrejoe

    spectrejoe Jr. VIP Jr. VIP

    Joined:
    Sep 25, 2013
    Messages:
    2,105
    Likes Received:
    440
    Home Page:
    Pretty straight forward aren't you?


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

    mazak85 Regular Member

    Joined:
    Oct 10, 2014
    Messages:
    347
    Likes Received:
    120
    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

    Code:
    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
     
    • Thanks Thanks x 1
    Last edited: Mar 10, 2015
  9. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    Haha yeah I am!

    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 :p
     
  10. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    Aha figured it out-

    Code:
    onClick.*]\);"\s
    Thank you for your help - now hopefully PHPMyAdmin doesn't fall over on the import haha =D
     
  11. mazak85

    mazak85 Regular Member

    Joined:
    Oct 10, 2014
    Messages:
    347
    Likes Received:
    120
    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.
     
  12. ChanzGrande

    ChanzGrande Elite Member

    Joined:
    Feb 16, 2008
    Messages:
    2,487
    Likes Received:
    1,177
    Occupation:
    Accountant
    Location:
    Northern Woods Counting Money
    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!
     
  13. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    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
     
  14. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    I don't think the original regular expression is selecting properly, take a look at this http://i.imgur.com/Isgu8bK.png
     
  15. HelloInsomnia

    HelloInsomnia Jr. Executive VIP Jr. VIP

    Joined:
    Mar 1, 2009
    Messages:
    1,826
    Likes Received:
    2,939
    It's being a bit greedy, try this:

    Code:
    onClick=\".*?]\);\"
    
     
  16. MatthewWoodward

    MatthewWoodward Senior Member

    Joined:
    Aug 31, 2012
    Messages:
    992
    Likes Received:
    1,694
    Occupation:
    SEO
    Location:
    UK
    Home Page:
    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-

    Code:
    <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-

    Code:
    <a onClick=\".*?]\);\"
    and replace with

    Code:
    <a
    Then find this

    Code:
     onClick=\".*?]\);\"
    and replace with nothing

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