Howdy, Stranger!

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

Advertise on LowEndTalk.com
mysql experts ? how to do this
New on LowEndTalk? Please read our 'Community Rules' by clicking on it in the right menu!

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 !

Comments

  • tetechtetech Member

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

  • kkrajkkkrajk Member
    edited July 23

    /\<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 Provider

    @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

    BuyVM - Free DirectAdmin, Softaculous, & Blesta! / Anycast Support! / Windows 2008, 2012, & 2016! / Unmetered Bandwidth!
    BuyShared - Shared & Reseller Hosting / cPanel + Softaculous + CloudLinux / Pure SSD! / Free Dedicated IP Address
  • tetechtetech Member
    edited July 23

    @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 Provider

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

    Francisco

    BuyVM - Free DirectAdmin, Softaculous, & Blesta! / Anycast Support! / Windows 2008, 2012, & 2016! / Unmetered Bandwidth!
    BuyShared - Shared & Reseller Hosting / cPanel + Softaculous + CloudLinux / Pure SSD! / Free Dedicated IP Address
  • 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 Provider

    @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
    BuyVM - Free DirectAdmin, Softaculous, & Blesta! / Anycast Support! / Windows 2008, 2012, & 2016! / Unmetered Bandwidth!
    BuyShared - Shared & Reseller Hosting / cPanel + Softaculous + CloudLinux / Pure SSD! / Free Dedicated IP Address
  • 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, Provider

    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.

    2x4GHz cores 200GB NVMe VPS for $9.99/mo, 8-core VPS for $11.99/mo, 32GB RAM VPS for $15.99/mo - wishosting.com

    Affiliate program

  • (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
  • youandriyouandri Member

    @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.

    VPS with perfect, complete, unique control panel and feature + has a mobile application, please check Cloudcone (Aff)

  • 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.

    UltraVPS.eu KVM in US/UK/NL/DE: 15% off first 6 month | Netcup VPS/rootDS - 5€ off: 36nc15279180197 (ref)

  • 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%'

  • FalzoFalzo Member

    @bolle said: delete from wp_posts

    no.

    Thanked by 1doghouch

    UltraVPS.eu KVM in US/UK/NL/DE: 15% off first 6 month | Netcup VPS/rootDS - 5€ off: 36nc15279180197 (ref)

  • BlaZeBlaZe Member, Provider

    Hire someone from Fiverr to do it manually for you.

    ExoticVM.com - Find VPS in exotic locations! - Discussion Thread

  • 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?

    UltraVPS.eu KVM in US/UK/NL/DE: 15% off first 6 month | Netcup VPS/rootDS - 5€ off: 36nc15279180197 (ref)

  • 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 Moderator

    @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.

    For LET support, please visit the support desk.

  • 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/

    UltraVPS.eu KVM in US/UK/NL/DE: 15% off first 6 month | Netcup VPS/rootDS - 5€ off: 36nc15279180197 (ref)

  • nemnem Member, Provider

    @raindog308 said:
    To summarize long-standing wisdom: regex is the wrong way to handle HTML.

    Or in more detail...

    You'd want to pull the data from database, parse DOM, remove all matching A nodes, then reinject back into the database. Applying a regex to it... 50/50 shot of breaking your HTML structure especially if malformed.

  • jsgjsg Member

    @jbiloh, @FAT32

    I'm extremely p_ssed off because

    • I took the time and made the effort to write a helpful post for OP that was based on concrete experience and offered a solution to OPs problem - then Clouf%&°! (or Vanilla?) popped up and asked me to do a captcha without even giving me the chance to do it.
    • Then I made the additional effort to edit both my post and OPs post quote - same sh_tty result
    • Then I crippled the post to a ridiculous extent like replacing 'href tag + url' by '[HREF] URL' (verbatim!) - yet I again couldn't post it, highly likely because I hadn't crippled the SQL stuff which OPs question was about!.

    So, the situation is that a user asks for help, I can provide that help - based on concrete practical experience - and even a full "just copy and insert your variable" solution BUT I'M F_CKING BLOCKED from posting it.

    Gentlemen, if CloudF%&#! nukes the very reason of existence of this forum it's about time to throw out the "protection" rather than asked for solutions and angry users.

    @momkin

    I'm sorry, I had a full - and known to work well - solution for you but I can't post it thanks to CloudF_ck and/or Vanilla being aggressively imbecile. I'd be willing to send it by PM but I'm virtually sure that won't work either. Sorry.

    Thanks no.

  • MrEdMrEd Member

    @jsg you could use some paste bin site for your solution and place a link to it ;)

    Thanked by 2Falzo Chronic
  • jsgjsg Member

    @MrEd said:
    @jsg you could use some paste bin site for your solution and place a link to it ;)

    Thanks, what good is a forum for if I need to put my solution on another site? Making it hard for experienced user to share their experience and knowledge and to provide solutions and help is about the worst thing a forum can do.
    And btw I've already jumped through quite a few loops to (try to) provide a solution.

    Thanks no.

  • momkinmomkin Member
    edited July 23

    @jsg there is a lot of ways to share your solution like https://pastebin.com/ or you could also answer my question in https://stackoverflow.com/questions/63019017/mysql-delete-text-starting-and-ending-with-specific-words

    This way it will be helpful for others !

    Thank you for your time and effort !

  • jsgjsg Member
    edited July 23

    @momkin said:

    As you ask so constructively and nicely ...

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

    [HREF]
    and ends with
    [End of HREF]

    Any help will be appreciated !

    No. What you want is highly likely a way to remove text that starts with '[HREF]="URL1"' or with '[HREF]="URL2"' and ends with '[End of HREF]' - or do you really want to delete all links?

    I occasionally had to do something similar when moving and cleaning a WP blog. The way I chose was to

    https://paste.gg/p/anonymous/66074d0273514d449fcdd170b752973b

    (Note: link will expire after 24 hours)

    Thanked by 1vimalware

    Thanks no.

  • jbilohjbiloh Administrator

    @jsg regret the inconvenience. Cloud flare is not perfect and I know there are some frustrating oddities that occur.

    Jon Biloh
  • jsgjsg Member

    Funny. I shared a known to work well solution incl. concrete how-to, changed my post multiple times to make it pass, and finally put it on some paste bin as suggested.

    And not even a simple "thank you". Lesson learned, thank you.

    Thanked by 1vimalware

    Thanks no.

  • FalzoFalzo Member

    @jsg said:
    Funny. I shared a known to work well solution incl. concrete how-to, changed my post multiple times to make it pass, and finally put it on some paste bin as suggested.

    And not even a simple "thank you". Lesson learned, thank you.

    you lost him at parameterized but latest at python or lua

    do you think, if he would feel comfortable with that he would have asked his question in the first place? ;-)

    UltraVPS.eu KVM in US/UK/NL/DE: 15% off first 6 month | Netcup VPS/rootDS - 5€ off: 36nc15279180197 (ref)

  • momkinmomkin Member

    @jsg said:
    Funny. I shared a known to work well solution incl. concrete how-to, changed my post multiple times to make it pass, and finally put it on some paste bin as suggested.

    And not even a simple "thank you". Lesson learned, thank you.

    i don't see any ( ready to go tutorial )
    what i see is you asking me to do thing with other programming languages python or lua which far away what i'm seeking for .

    Anyway thank you very much for your time and effort i really appreciate it .

  • momkinmomkin Member

    Anyone got an easy way to do this ??

  • KatamazeKatamaze Member
    edited July 24

    You have to use PHP & DOM. I wrote a function on the fly but this damned Cloudflare is driving me crazy! It keeps asking to complete a CAPTCHA I can't see! I've been forced to post the code on Github :/ here:

    https://github.com/Kian987/Random-Stuff/blob/master/Stuff/WordPress.php

    I will remove it later. Anyway here's the before/after in database:

    The only problem is that DOM keeps adding <p></p> tags even if I used LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD. Well it's too late now. I have no time to explain. I wasted too much time because of Cloudflare :|

  • momkinmomkin Member

    @Katamaze said:
    You have to use PHP & DOM. I wrote a function on the fly but this damned Cloudflare is driving me crazy! It keeps asking to complete a CAPTCHA I can't see! I've been forced to post the code on Github :/ here:

    https://github.com/Kian987/Random-Stuff/blob/master/Stuff/WordPress.php

    I will remove it later. Anyway here's the before/after in database:

    The only problem is that DOM keeps adding <p></p> tags even if I used LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD. Well it's too late now. I have no time to explain. I wasted too much time because of Cloudflare :|

    Thanks mate how can i use this script exactly ?
    I mean how can i link to my database to make the changes ?

  • KatamazeKatamaze Member
    edited July 25

    I'm not using WordPress hence I created a wp_post table on the fly following this structure. What you need are:

    • id (to update the post once you removed unwanted URL blocks)
    • post_content that contains the actual comment

    That said, I've added comments to the original script so that you can get an idea of how it works. Of course you'll need to adapt it to your needs sinc as I said earlier I'm not using WordPress.

  • momkinmomkin Member

    @Katamaze said:
    I'm not using WordPress hence I created a wp_post table on the fly following this structure. What you need are:

    • id (to update the post once you removed unwanted URL blocks)
    • post_content that contains the actual comment

    That said, I've added comments to the original script so that you can get an idea of how it works. Of course you'll need to adapt it to your needs sinc as I said earlier I'm not using WordPress.

    Hi ,
    I'll try to make it work with WordPress , Thank you very much for your help !

  • What I would try is using WP-CLI as that is a reasonably safe / easy way to do DB replacements. I would:

    1 Back up the database!

    2 Instal WP-CLI and do the replace command

    You can get WP-CLI from here https://wp-cli.org/

    And an example on how to use the replace command - although not exactly what it is you want here, but is close:

    https://webdock.io/en/docs/wordpress-guides/fixing-wordpress-redirect-issues

  • momkinmomkin Member

    @Webdock_io said:
    What I would try is using WP-CLI as that is a reasonably safe / easy way to do DB replacements. I would:

    1 Back up the database!

    2 Instal WP-CLI and do the replace command

    You can get WP-CLI from here https://wp-cli.org/

    And an example on how to use the replace command - although not exactly what it is you want here, but is close:

    https://webdock.io/en/docs/wordpress-guides/fixing-wordpress-redirect-issues

    This can easy be done without wp cli i can do it without it , wp cli is useless , what i requested is more complicated !

  • @momkin said:

    is more complicated !

    This is usually the hint that you need a scripting language with batteries included. (for parsing markup.)

    250GB USA RAID6 StorageKVM from €13/yr (no-torrenting, TopProvider)
    (affiliate for 🥰 ) https://clients.inceptionhosting.com/aff.php?aff=401&gid=30

Sign In or Register to comment.