Announcement

Announcement Module
Collapse
No announcement yet.

Removing links from posts in wordpress

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Removing links from posts in wordpress

    Hi to all,
    I am not very good with mysql so i need some help.
    I am trying to remove some specific links from my wordpress blog. I have a lot of outgoing links to several domains and i want to remove just links to one of them. For example all links that lead to dontneedlink.com or start with dontneedlink.com/(some child page)

    I already try

    UPDATE wp_posts SET post_content = REPLACE (
    post_content,
    'Item to replace here',
    Replacement text here');

    But this is not god for me because I have a lot of key words and there is a lot of combinations.

    So I need some kind of query to recognize my links that include dontneedlink.com and to remove href text and leave keyword untouched.

    <a href=”http:// dontneedlink.com”>Test</a> -> Test
    <a href=”http:// dontneedlink.com”>Test Again</a> -> Test Again
    <a href=”http:// dontneedlink.com/childpage”>Test Again 2</a> -> Test Again 2

    Is this possible at all?
    This would save me a lot of time instead to go from one to another post and remove manually.

    Thanks in advance!

  • #2
    Below is a query I put together. It is super complicated, and someone likely will come along and replace it with a one liner, but it I believe does the trick based on the test case I put together based off your sample data.

    WARNING: BACK UP YOUR DATA BEFORE YOU ATTEMPT TO DO ANYTHING WITH THIS.

    Assumptions:

    1. Based on my understanding, post_content will be an entire post, not just a link. So we need to find the links within a chunk of text, and just replace the link portion leaving the rest of the text untouched.
    2. Links are in the form of <a href="">text</a>.
    3. The link text does not contain the greater-than or less-than characters (< or >). I.e. something like "This is < link text!" would break this.
    4. If there is more than one link in a single post_content that you want to replace, you'll have to run the update more than once to catch the 2nd+ link.

    Below is a SELECT of the sample data I used, the UPDATE I ran, and then another SELECT showing the resulting data. Note the "goodlink.com" link that is unaffected since it does not match the domain we are replacing.

    Update query:
    Code:
    update wp_posts set post_content = replace(post_content, left(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('</a>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 3), left(mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)))), locate('<', mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))))) - 1)) where mid(post_content, locate('http://', post_content), length(post_content)) LIKE 'http://dontneedlink.com%';
    Code:
    mysql> select * from link_test;
    +---------------------------------------------------------------------------+
    | post_content                                                              |
    +---------------------------------------------------------------------------+
    | Text1 <a href="http://dontneedlink.com">Test</a> Text2                    |
    | Text 3 <a href="http://dontneedlink.com">Test Again</a> Text4             |
    | Text 5 <a href="http://goodlink.com">Keep Me</a> Text 6                   |
    | Text 7 <a href="http://dontneedlink.com/childpage">Test Again 2</a>Text 8 |
    +---------------------------------------------------------------------------+
    4 rows in set (0.00 sec)
    
    mysql> update link_test set post_content = replace(post_content, left(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('</a>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 3), left(mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)))), locate('<', mid(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content)), locate('>', mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))) + 1, length(mid(post_content, locate('<a href="http://dontneedlink.com', post_content), length(post_content))))) - 1)) where mid(post_content, locate('http://', post_content), length(post_content)) LIKE 'http://dontneedlink.com%';
    Query OK, 3 rows affected (0.01 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
    mysql> select * from link_test;
    +---------------------------------------------------------+
    | post_content                                            |
    +---------------------------------------------------------+
    | Text1 Test Text2                                        |
    | Text 3 Test Again Text4                                 |
    | Text 5 <a href="http://goodlink.com">Keep Me</a> Text 6 |
    | Text 7 Test Again 2Text 8                               |
    +---------------------------------------------------------+
    4 rows in set (0.00 sec)
    
    mysql>
    To do the replace for a different domain, just do a find/replace for "dontneedlink.com" and replace it with another domain in the same format.

    I tested a few edge cases, but I expect you will run into exceptions. So again, make sure you back up your data.

    Once you have a chance to test it let me know how it goes and I'll try to help more if necessary. =)

    Comment

    Working...
    X