Last Updated on Sunday, 03 February 2013
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:
- SET @stringtoFind='<pre class="brush:php">';
- SET @stringtoReplaceWith='<pre>';
- SELECT
- `id` ,
- `title` ,
- IF(LOCATE( @stringtoFind, REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew,
- IF(LOCATE( @stringtoReplaceWith, `content_column`)>0,'yes','no') as NewFoundInOld,
- IF(LOCATE( @stringtoReplaceWith, REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) )>0,'yes','no') as OldFoundInNew,
- SUBSTRING(`content_column`, LOCATE( @stringtoFind, `content_column` )) as OldFind,
- REPLACE(`content_column`, @stringtoFind, @stringtoReplaceWith) AS newText,
- `content_column` as oldText
- FROM `MYTABLE_content`
- WHERE `content_column` LIKE CONCAT('%',@stringtoFind,'%')
- -- yields
- id title OldFoundInOld NewFoundInOld OldFoundInNew newText oldText
- 1 Article 1 yes no no <pre class="brush:php">Hello World</pre> <pre>Hello World</pre>
- 2 Article 2 yes no no <pre class="brush:php">Hello World</pre> <pre>Hello World</pre>
- 3 Article 3 yes no no <pre class="brush:php">Hello World</pre> <pre>Hello World</pre>
Now update them
- SET @stringtoFind='<pre class="brush:php">';
- SET @stringtoReplaceWith='<pre>';
- UPDATE
- `MYTABLE_content`
- SET
- `introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith)
- WHERE
- `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.
- SET @stringtoFind='<pre class="brush:php">';
- SET @stringtoReplaceWith='<pre>';
- UPDATE
- `MYTABLE_content`
- SET
- `introtext` = REPLACE(`introtext`, @stringtoFind, @stringtoReplaceWith),
- `fulltext` = REPLACE(`fulltext`, @stringtoFind, @stringtoReplaceWith)
- WHERE
- `introtext` LIKE CONCAT('%',@stringtoFind,'%')
- OR
- `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
- Search and Replace a string in a mySQL database.
Latest Posts
-
Joes Revolver Map (JRM)
-
Fri 17-May-13
Hmmm... Sounds like a problem with the identifier. Was it working before and has there been a change ...
-
Fri 17-May-13
Hello Joel: Yes, I do have it published on all pages of the site. I just went back to Revolver maps to ...
-
Fri 17-May-13
Hi Bill, From the developers of RevolverMaps, "the module would need to be published on every page ...
-
Fri 17-May-13
Hi Bill, I'll investigate further as you're not the first to say this happens. In the meantime, simply ...
-
Fri 17-May-13
Its a great extension. But when I set it up I only show my presence on the 3D map and no other visitors ...
-
Fri 17-May-13