Removing links from articles in Joomla using MySQL query

Question

Trying to use ChristoKiwi's https://stackoverflow.com/questions/19356897/removing-links-from-posts-in-wordpress-using-query/61652078#61652078 approach for deleting links in Joomla articles, but having issues with this example:

    <p>
   <img
      class="responsive caption"
      style="margin-right: 10px; float: left;"
      src="images/stories/ds/Specials/Mikrosystemtechnik/2019/rohm0119.jpg"
      alt="rohm0119"
   /><a
      dir="ltr"
      title="Rohm"
      href="https://www.rohm.de/"
      target="_blank"
      rel="noopener"
      >Rohm </a
   >kündigt mit dem BM14270MUV-LB einen der kleinsten berührungslosen
   Stromsensoren der Industrie an. Der neue Baustein verbraucht nur minimal
   Strom und ist mit 3,5 x 3,5 x 1,0 mm sehr kompakt.&nbsp;Damit eignet er sich
   für Industrieanlagen und Verbrauchergeräte, die Betriebsbedingungen über den
   Stromfluss erfassen wie batteriebetriebene Drohnen, Solaranlagen und Server
   in Rechenzentren.
</p>

The query

UPDATE jos_content SET jos_content.introtext = REPLACE(jos_content.introtext, substring_index( substring_index(jos_content.introtext, 'title="', -1), '"', 1),'')

deletes not only the text within the "title" but also the word "Rohm" after the link.

How to delete the entire link with all the attributes completely?

Answer 1

It appears on review of Christo Kiwi's solution that it only works with a link which has an identical part of which you wish to remove.

Based on your example, I suspect that your link is a variable and so you may need to take a slightly different approach.

1. You first need to identify the start position and end position of the string component in the intro text that you wish to remove.
2. Once you have found the start and end positions (which you can check using a select statement) then you can build your update/replace based on that. For example, you may need to find the position of <a dir and then the position of the next > after that and use that in your replace string.

I would definitely start by creating a SELECT query to make sure that you are correctly getting the article ID and the string part that you wish to replace and then build that into your update query.

In my particular case, I was looking for multiple links of the same type in intro text so I had to work out the position of the first start and end, then the second start and end, then the third start and end and replace all three.

Here is a snippet of code which may help you - I've used LOCATE() and SUBSTRING() to first check that the data I was going to replace was what I was expecting, then I built that into my replace function.

SELECT
    id,
    introtext,
    SUBSTRING(introtext, LOCATE('<li><a href="index.php?option=com_docman', introtext), 4 + LOCATE('/li>', introtext, LOCATE('<li><a href="index.php?option=com_docman', introtext)) - LOCATE('<li><a href="index.php?option=com_docman', introtext)) AS pos1text
FROM `d8bk2_content` 
WHERE id >395
  AND introtext LIKE '%<li><a href="index.php?option=com_docman%'

In your case, you would LOCATE('<a dir') and then the next closing tag etc.

Answer 2

As an alternative, you could have opened up your template and used php's native strip_tags function which allows a list of exceptions, so...

echo strip_tags($this->item->introtext, '<img><p><listofallowedelements>');