Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


mysql experts ? how to do this
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

mysql experts ? how to do this

momkinmomkin Member

Hello ,
I have a WordPress site and i want to remove text from wp_posts table precisely in post_content field. Example of the text i want to remove :
<a href="http://www.mediafire.com"><img class="aligncenter wp-image-41174 size-full" src="https://www.ritavpn.com/blog/wp-content/uploads/2019/12/Best-APK-Download-Sites-for-2020.png" alt="" width="214" height="57" data-wp-pid="41174" /></a>

Note : http://www.mediafire.com and https://www.ritavpn.com/blog/wp-content/uploads/2019/12/Best-APK-Download-Sites-for-2020.png

are dynamic they are not the some links in all the posts !

So what I'm looking for is a query to remove the text that starts with

<a href=
and ends with
</a>

Any help will be appreciated !

«1

Comments

  • tetechtetech Member

    delete from wp_posts where post_content like '%<a href=%</a>%'

  • kkrajkkkrajk Member
    edited July 2020

    /\<a href(.*?)\<\/a\>/g

    This is the regex to select the text starting with <a href and ending with </a> both inclusive . Sorry unable to help you on the SQL query

  • AlefAlef Member

    @tetech said:
    delete from wp_posts where post_content like '%<a href=%</a>%'

    While this would work, it doesn't delete the data relating to that post in other tables. So you would be left with orphaned data scattered throughout the database.

    Also this should go without saying, make a backup of your database before running any suggested query that changes the database.

  • FranciscoFrancisco Top Host, Host Rep, Veteran

    @Alef said: While this would work,

    No, that'll delete all links, not just the one they're after.

    You could have a link at the start and then one way at the end of the post and it'd match.

    If anything he should be doing:

    delete from wp_posts where post_content like '%Best-APK-Download-Sites-for-2020%';
    

    Francisco

  • tetechtetech Member
    edited July 2020

    @Francisco said:

    @Alef said: While this would work,

    No, that'll delete all links, not just the one they're after.

    You could have a link at the start and then one way at the end of the post and it'd match.

    If anything he should be doing:

    delete from wp_posts where post_content like '%Best-APK-Download-Sites-for-2020%';
    

    Francisco

    But OP said

    Note : http://www.mediafire.com and https://www.ritavpn.com/blog/wp-content/uploads/2019/12/Best-APK-Download-Sites-for-2020.png

    are dynamic they are not the some links in all the posts !

    So what I'm looking for is a query to remove the text that starts with

    <a href=
    and ends with
    </a>

    My interpretation was delete anything with an <a> tag, but I can see how other interpretations are possible.

  • FranciscoFrancisco Top Host, Host Rep, Veteran

    You could be very much correct, i guess he has the answer to both possibilities.

    Francisco

  • export the sql, search and delete using regex with editor, import the sql

  • tetechtetech Member

    @Francisco said:
    You could be very much correct, i guess he has the answer to both possibilities.

    Francisco

    Let's assume so unless OP clarifies otherwise :smile:

  • FranciscoFrancisco Top Host, Host Rep, Veteran

    @yokowasis said:
    export the sql, search and delete using regex with editor, import the sql

    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

    Francisco

    Thanked by 3Chronic doghouch Pwner
  • tetechtetech Member

    @Francisco said:

    @yokowasis said:
    export the sql, search and delete using regex with editor, import the sql

    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

    Francisco

    That exactly describes what happened when my girlfriend said I didn't communicate enough.

    Thanked by 1kkrajk
  • exception0x876exception0x876 Member, Host Rep, LIR

    He wants to delete the text, not the entire post. If you are running MySQL 8, you can use REGEXP_REPLACE function otherwise it is probably easier to write a small script in your preferred programming language to do the replacement rather than using pure SQL.

  • (if you don't know what you're doing, just don't)

  • cochoncochon Member

    @SCAM_DONT_BUY said:
    (if you don't know what you're doing, just don't)

    Eeek, I'd never be able to get up in the morning :D

    Thanked by 1tetech
  • @exception0x876 said:
    He wants to delete the text, not the entire post. If you are running MySQL 8, you can use REGEXP_REPLACE function otherwise it is probably easier to write a small script in your preferred programming language to do the replacement rather than using pure SQL.

    Yes the best is using regexp_replace or programming.

  • FalzoFalzo Member

    @Alef said: make a backup of your database before running any suggested query that changes the database.

    this!

    @exception0x876 said: He wants to delete the text, not the entire post.

    this!

    if OP blindly ran the command from the first answer without a proper backup he's probably pretty much f*cked already by now... because his posts be gone instead of just the text.

  • You should use WP CLI search-replace to find and replace data from DB... doing direct edits can break serialized entries in the database

    Thanked by 1Falzo
  • @mehargags said:
    You should use WP CLI search-replace to find and replace data from DB... doing direct edits can break serialized entries in the database

    What is it? I too need solution for this.

  • marianmarian Member

    https://interconnectit.com/products/search-and-replace-for-wordpress-databases/ - I have used this script many times when moving Wordpress sites from a domain to other.

    Also it can be a solution for your request - just be sure to read all the notes and take your time before making the final "Submit". Don't forget to make a backup before making any change!!!

  • bollebolle Member

    just keep it simple

    delete from wp_posts where post_content like '%ritavpn%'

    Thanked by 1yoursunny
  • FalzoFalzo Member

    @bolle said: delete from wp_posts

    no.

    Thanked by 1doghouch
  • BlaZeBlaZe Member, Host Rep

    Hire someone from Fiverr to do it manually for you.

  • momkinmomkin Member

    @tetech said:
    delete from wp_posts where post_content like '%<a href=%</a>%'

    This will remove all the post_content text not just the part i want to remove !!

  • momkinmomkin Member

    @exception0x876 said:
    He wants to delete the text, not the entire post.

    Exactly ! :smile:

  • FalzoFalzo Member

    @momkin said:

    @tetech said:
    delete from wp_posts where post_content like '%<a href=%</a>%'

    This will remove all the post_content text not just the part i want to remove !!

    did you notice before or learned the hard way?

  • momkinmomkin Member

    @Francisco said:

    @Alef said: While this would work,

    No, that'll delete all links, not just the one they're after.

    You could have a link at the start and then one way at the end of the post and it'd match.

    If anything he should be doing:

    delete from wp_posts where post_content like '%Best-APK-Download-Sites-for-2020%';
    

    Francisco

    @Francisco i don't want to remove the full text only a part of it and the links are dynamic , so i can't use your way :smile:

  • momkinmomkin Member

    @Falzo said:

    @momkin said:

    @tetech said:
    delete from wp_posts where post_content like '%<a href=%</a>%'

    This will remove all the post_content text not just the part i want to remove !!

    did you notice before or learned the hard way?

    Well first i know this query will remove all the text not just a part of it ,
    Second i always take a sql backup before doing any queries just in case !

    Thanked by 1Falzo
  • raindog308raindog308 Administrator, Veteran

    @exception0x876 said: REGEXP_REPLACE

    To summarize long-standing wisdom: regex is the wrong way to handle HTML. It is inherently brittle. I'm not saying it can't work in many cases but tokenizing is not what regex is designed to do. You'll forever run into cases your naive regex hadn't anticipated, to say nothing of multi-line HTML, etc.

    Scripting languages such as perl, python, etc. provide libraries to handle this. Bigger DBs (SQL Server, Oracle, etc.) include native support for parsing various markups (html, xml, json, etc.). I don't think MySQL has support for parsing HTML.

    Are you doing this live? i.e., do you have a dataset where you can run a transform and then you're done, or is this query part of the web page? Doing it once is one thing because you can always check and you can do it in stages...e.g., create a new column that has the results from a simple step, then another column that has results from another step, then repopulate the first column with the results of another step, etc. until you have the process down. But that won't work if you're constantly adding new data in this format and expecting your web site to parse it on the fly.

    Thanked by 1yoursunny
  • momkinmomkin Member

    So in other words it can't be done , or at least no one here knows how ?

  • FalzoFalzo Member

    of course it can be done. it's just no one will spoon-feed you ;-)

    as other pointed out, I wouldn't necessarily try to do this directly at the database level though.

    here are few more ideas how to approach that: https://www.cminds.com/5-best-search-replace-wordpress-tools-fix-content-website/

Sign In or Register to comment.