What?
This is an article on how to replace a string with another in all your joomla articles without modifying other data such as modified date and author.

Why?
I used to have a third-party plugin that would replace my HTML tags <PRE> with <PRE CLASS="brush:php">. Not I want to switch these back.

How?
For demo purposes, I'm searching a column called introtext in a database table called MYTABLE_content. First let me find all the articles that need this change:

  1.  SET @stringtoFind='<pre class="brush:php">'; 
  2.  SET @stringtoReplaceWith='<pre>'; 
  3.   
  4.   SELECT    
  5.        `id` ,  
  6.        `title` ,  
  7.     
  8.        IF(LOCATE( @stringtoFind, REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew,  
  9.        IF(LOCATE( @stringtoReplaceWith, `content_column`)>0,'yes','no') as NewFoundInOld,  
  10.        IF(LOCATE( @stringtoReplaceWith, REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew,  
  11.   
  12.        SUBSTRING(`content_column`, LOCATE( @stringtoFind, `content_column` )) as OldFind,  
  13.   
  14.        REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) AS newText,  
  15.        `content_column` as oldText  
  16.     
  17.   FROM  `MYTABLE_content`   
  18.     
  19.   WHERE  `content_column` LIKE CONCAT('%',@stringtoFind,'%') 
  20.   
  21.   
  22.  -- yields 
  23.  id    title        OldFoundInOld    NewFoundInOld    OldFoundInNew    newText                            oldText 
  24.  1    Article 1    yes        no        no        <pre class="brush:php">Hello World</pre>    <pre>Hello World</pre> 
  25.  2    Article 2    yes        no        no        <pre class="brush:php">Hello World</pre>    <pre>Hello World</pre> 
  26.  3    Article 3    yes        no        no        <pre class="brush:php">Hello World</pre>    <pre>Hello World</pre> 

Now update them
  1.  SET @stringtoFind='<pre class="brush:php">'; 
  2.  SET @stringtoReplaceWith='<pre>'; 
  3.   
  4.  UPDATE 
  5.  `MYTABLE_content` 
  6.  SET 
  7.  `introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith) 
  8.  WHERE   
  9.  `introtext` LIKE CONCAT('%',@stringtoFind,'%') 


Run the first query again and no rows should be returned.



Quick Joomla One
I use this reluctantly but often necessarily.
  1.  SET @stringtoFind='<pre class="brush:php">'; 
  2.  SET @stringtoReplaceWith='<pre>'; 
  3.   
  4.  UPDATE 
  5.  `MYTABLE_content` 
  6.  SET 
  7.  `introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith), 
  8.  `fulltext` = REPLACE(`fulltext`, @stringtoFind, @stringtoReplaceWith) 
  9.  WHERE   
  10.  `introtext` LIKE CONCAT('%',@stringtoFind,'%') 
  11.  OR 
  12.  `fulltext` LIKE CONCAT('%',@stringtoFind,'%') 

You might get some errors talking about some empty rows but don't be deceived as the update will have happened anyway.

Other Searches
  1. Search and Replace a string in a mySQL database.

Add comment


Send