Cleaning database from Rich Editor left over

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Post Reply
Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Cleaning database from Rich Editor left over

Post by Nick225 » Fri Nov 30, 2018 7:53 pm

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.

User avatar
AbaddonOrmuz
Registered User
Posts: 438
Joined: Wed Dec 25, 2013 9:06 pm
Location: /dev/null
Name: Alfredo Ramos
Contact:

Re: Cleaning database from Rich Editor left over

Post by AbaddonOrmuz » Fri Nov 30, 2018 9:14 pm

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.
A proudly user of Arch Linux :: /r/sddm_themes

Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sat Dec 01, 2018 2:40 am

AbaddonOrmuz wrote:
Fri Nov 30, 2018 9:14 pm
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
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sat Dec 01, 2018 3:47 am

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
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sat Dec 01, 2018 8:44 am

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

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 49749
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Cleaning database from Rich Editor left over

Post by stevemaury » Sat Dec 01, 2018 5:45 pm

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.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. PM or email me.

All unsolicited PMs will be ignored.

Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sun Dec 02, 2018 8:15 pm

That's a good idea.. Thanks.

User avatar
AbaddonOrmuz
Registered User
Posts: 438
Joined: Wed Dec 25, 2013 9:06 pm
Location: /dev/null
Name: Alfredo Ramos
Contact:

Re: Cleaning database from Rich Editor left over

Post by AbaddonOrmuz » Wed Dec 05, 2018 6:13 am

You could also write a script and run it with a cron job.
A proudly user of Arch Linux :: /r/sddm_themes

Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Wed Dec 05, 2018 9:54 pm

AbaddonOrmuz wrote:
Wed Dec 05, 2018 6:13 am
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?
Last edited by Nick225 on Sun Dec 09, 2018 1:03 am, edited 1 time in total.

User avatar
AmigoJack
Registered User
Posts: 5390
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Cleaning database from Rich Editor left over

Post by AmigoJack » Fri Dec 07, 2018 5:38 pm

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.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sun Dec 09, 2018 1:09 am

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.

User avatar
3Di
Registered User
Posts: 13131
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: Cleaning database from Rich Editor left over

Post by 3Di » Sun Dec 09, 2018 2:35 am

You need to create a script which mimics those files, not put your code inside of them.
We mean take inspiration from them.
:game_die: The new Dice Roller extension for phpBB 3.2 is out! :game_die:

Please PM me only to request paid works. Thx.
Want to compensate me for my interest? Donate
Extensions, Scripts, MOD porting, Update/Upgrades
My development's activity º PhpStorm's proud user

Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sun Dec 09, 2018 2:45 am

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.

User avatar
AmigoJack
Registered User
Posts: 5390
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Cleaning database from Rich Editor left over

Post by AmigoJack » Sun Dec 09, 2018 9:02 am

Nick225 wrote:
Sun Dec 09, 2018 2:45 am
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).
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

Nick225
Registered User
Posts: 75
Joined: Sat Nov 24, 2018 7:48 pm

Re: Cleaning database from Rich Editor left over

Post by Nick225 » Sun Dec 09, 2018 3:47 pm

AmigoJack wrote:
Sun Dec 09, 2018 9:02 am
Nick225 wrote:
Sun Dec 09, 2018 2:45 am
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.

Post Reply

Return to “phpBB Custom Coding”

Who is online

Users browsing this forum: No registered users and 4 guests