Cleaning database from Rich Editor left over

[Nick225]

I was using HTML (Rich Text Editor) in my old forum that I just converted to phpbb3.
The Rich Editor was replacing the characters

Code: Select all

 '<' with #ed_op#  and '>' with #ed_cl#

.
So now my new bb3 posts are filled with all those #ed_op#.
From my phpadmin, I can do an update on a row like this:

Code: Select all

UPDATE phpbb_posts 
		SET 
			post_text=REPLACE(post_text, '#od-op#', '<'), 
			post_text=REPLACE(post_text, '#od-oc#', '>')
		WHERE post_id=xxxx;

The phpbb_posts table has more than 500,000 rows. I had so many database connection losses during the Conversion process but finally got through.

I would like a query to loop through the table, 2000 records at a time and make all the changes without connection issues. (I will later convert those to bbcode too. )

Can someone suggest the most efficient way to do this? Need help.
Thanks a bunch.

[AlfredoRamos]

You can use a WHILE, but I would use a LIMIT to your current SQL sentence and run it multiple times until none is left.

[Nick225]

You can use a WHILE, but I would use a LIMIT to your current SQL sentence and run it multiple times until none is left.

Thanks for the reply. To test that query, I created a small table with just a topic_id and post_text field on myphpadmin, using the same field definition in phpbb_posts table.
I ran the query on the small example table and that worked.
So still from phpadmin, I ran the query on a real post in phpbb3. Surprise. Nothing changes. I tried it again thinking maybe I did something wrong. But no... Can't edit the field.

[Nick225]

That was my mistake.. I didn't change the table name when I ran the query on phpbb_post.
Writing the while loop example. Works great on my example table.

Code: Select all

DELIMITER $$
DROP PROCEDURE IF EXISTS testme$$
CREATE PROCEDURE testme()
BEGIN
 DECLARE count INT DEFAULT 1;
 WHILE count < 5 DO
 	UPDATE phpbb_example SET
	post_text=REPLACE(post_text, '#ed_op#br#ed_cl#', '<br/>'),
	post_text=REPLACE(post_text, '#ed_op#br #ed_cl#', '<br/>'),
	post_text=REPLACE(post_text, '#ed_op#/p#ed_cl#', '<p/> '),	
	post_text=REPLACE(post_text, '#ed_op#p style="text-align: justify;"#ed_cl#','\r'),
	post_text=REPLACE(post_text, '#ed_op#', '<'),
	post_text=REPLACE(post_text, '#ed_cl#', '>')	
   WHERE post_id=count;
   SET count = count + 1;
 END WHILE;
END$$
DELIMITER ;

call testme();

Now I need to care of the

Code: Select all

<span> and youtube htmls

[Nick225]

Loop works only for small set.
Trying bigger numbers like 1000 fails.
Any suggestion? thanks.

[stevemaury]

Export the table (in pieces if necessary, like SELECT * FROM phpbb_posts WHERE post_id < X), load it into an industrial strength text editor and do a search and replace.

[Nick225]

That's a good idea.. Thanks.

[AlfredoRamos]

You could also write a script and run it with a cron job.

[Nick225]

You could also write a script and run it with a cron job.

I am on a shared host.So I have access to SSH. What would your cron script look like?

[AmigoJack]

The script would look like a PHP one, of course, and be executed either internally (executing PHP) or externally (requesting it via HTTP, i.e. thru WGET).

Look at a few of the phpBB PHP files (i.e. viewtopic and memberlist) and you get the idea how its skeleton should look like and how to execute SQL queries. This would even give you much more freedom in acting upon the results instead of defining and using database procedures.

[Nick225]

I first tried to add my code to the viewforum page. But realize that page doesn''t read the posts.
So I moved on to viewtopic without much success. The strings are not replaced. That's when I went to do it in phpadmin.
I'll to go back to viewtopic again to update all the posts in that single topic are they are read. Still new to the phpbb3 codes.

[3Di]

You need to create a script which mimics those files, not put your code inside of them.
We mean take inspiration from them.

[Nick225]

Yes I got that. already wrote one external page for newest posts, based on the phpbb tutorial. Just was looking for a quick show of the modification. Most extension I have seen place them on the index page. I wanted that list on all pages.

[AmigoJack]

place them on the index page. I wanted that list on all pages

Why are you writing this? Your script to be created needs no output at all (and no input either).

[Nick225]

place them on the index page. I wanted that list on all pages

Why are you writing this? Your script to be created needs no output at all (and no input either).

Sorry.. that "latest topic" is unrelated to this issue. I should just stick with the 'Rich Editor clearing' problem. Sorry.