very messy BBcode cleanup, need cleaner help

[falxzzr]

Is there a way to run a * query in the cleaner?

Basically I used a [video] bbcode on my phpbb3 forum that I imported.

The problem is, on import it looked like it stored each video as a different random code, one for each post.

For example

post 1 with embeded video looks like this [video:1sdfqtqw]http://www.youtube.com/watch?v=fbGkxcY7YFU[/video:1sdfqtqw]

post 2 it looks like this [video:368mrk79]http://www.youtube.com/watch?v=ODz2rUXpKWw[/video:368mrk79]



etc etc, each post has its own random string after the [video: part of the code.

Any way I can fix this in cleaner, or will i really have to go through 14k posts deleting them?

I tried using something like this;

$replacer = array(
"[video:*]" => "",
"[/video:*]" => ""
);


but, it didnt work :(

[falxzzr]

Nobody knows how to SQL query all the randomness in my old bbcode [video:randomlettersequences] away?

[Lats]

Try these...

Remove the opening bit...

UPDATE
post
SET
pagetext = replace( pagetext, substring( pagetext, locate( '[video', pagetext ) , 16 ) , '' )
WHERE
LOCATE( '[video', pagetext ) >0

Remove the closing bit...


UPDATE
post
SET
pagetext = replace( pagetext, substring( pagetext, locate( '[/video', pagetext ) , 17 ) , '' )
WHERE
LOCATE( '[/video', pagetext ) >0

[falxzzr]

that worked!

thanks :D

[falxzzr]

Now the new problem, how to get the forum to accept all the previously 'posted as bbcode' media URLs as actual URLs

Now they are just text, when I hit edit post and save they become URLs again.

[Lats]

Did you run through the update threads/forum info/other bits etc?

[falxzzr]

yes I updated forums threads post cache.

What it looks like is, now tha the code has been removed around the youtube URLs they are now just in a text form rather than a URL.

For example

http://midmoriders.com/forum/showthread.php?t=2704

you can see here that the top 3 or 4 osts I did a edit and then save and it made the videos appear, now go down a few posts and you will see lots of http://youtube.com/blabhlablah in text only forum, not in URL form. I need it to be in URL form so that my AME will convert it to embedded video~

[Lats]

Dang, should have replaced the random bits with url tags.

Try this...

UPDATE
post
SET
pagetext = replace(pagetext, substring(pagetext, locate('http://', pagetext), 42), concat('', substring(pagetext, locate('http://', pagetext) , 42) , ''))
WHERE
locate('http://', pagetext) > 0
AND
locate('[url]', pagetext) = 0

[Jerry]

Is there a way to run a * query in the cleaner?

Basically I used a [video] bbcode on my phpbb3 forum that I imported.

The problem is, on import it looked like it stored each video as a different random code, one for each post.

For example

post 1 with embeded video looks like this [video:1sdfqtqw]http://www.youtube.com/watch?v=fbGkxcY7YFU[/video:1sdfqtqw]

post 2 it looks like this [video:368mrk79]http://www.youtube.com/watch?v=ODz2rUXpKWw[/video:368mrk79]



etc etc, each post has its own random string after the [video: part of the code.

Any way I can fix this in cleaner, or will i really have to go through 14k posts deleting them?

I tried using something like this;

$replacer = array(
"[video:*]" => "",
"[/video:*]" => ""
);


but, it didnt work :(


Line 98 of cleaner is :



#$text = preg_replace('##siU', '', $text);


Replace that with



$text = preg_replace('#\\[([a-z]+):([a-z0-9]+)\](.*)\[/\\1:\\2\]#siU', '[$1]$3[/$1]', $text);

[falxzzr]

While I dont doubt that may work,

Lats SQL query worked like a charm.

[designeru]

Try these...

Remove the opening bit...

UPDATE
post
SET
pagetext = replace( pagetext, substring( pagetext, locate( '[video', pagetext ) , 16 ) , '' )
WHERE
LOCATE( '[video', pagetext ) >0
Remove the closing bit...


UPDATE
post
SET
pagetext = replace( pagetext, substring( pagetext, locate( '[/video', pagetext ) , 17 ) , '' )
WHERE
LOCATE( '[/video', pagetext ) >0

i had it like [youtube:123456]link[/youtube:123456]... modifying a little bit the above commands i fixed it with:


UPDATE
post
SET
pagetext = replace( pagetext, substring( pagetext, locate( '[youtube', pagetext ) , 15 ) , '[youtube' )
WHERE
LOCATE( '[youtube', pagetext ) >0
and for the closing tags:


UPDATE
post
SET
pagetext = replace( pagetext, substring( pagetext, locate( '[/youtube', pagetext ) , 16 ) , '[/youtube' )
WHERE
LOCATE( '[/youtube', pagetext ) >0