MyBB Community Forums
SQL function for converting phpBB3 URLs over MyBB syntax. - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Community Archive (https://community.mybb.com/forum-106.html)
+--- Forum: Archived Forums (https://community.mybb.com/forum-143.html)
+---- Forum: Archived Development and Support (https://community.mybb.com/forum-155.html)
+----- Forum: MyBB 1.6 (https://community.mybb.com/forum-138.html)
+------ Forum: 1.6 Merge System Support (https://community.mybb.com/forum-141.html)
+------ Thread: SQL function for converting phpBB3 URLs over MyBB syntax. (/thread-104182.html)

Pages: 1 2


SQL function for converting phpBB3 URLs over MyBB syntax. - cvwan8ur - 2011-09-19

I'm not sure how common of a problem this is, but I migrated a forum a few weeks ago from phpBB3 to MyBB, and I noticed that none of the URLs were properly migrated. I attempted the migration again only to find the same problem. This was a vanilla installation of the latest version of phpBB3, so I'm not sure why the Merge system didn't convert the links, but at any rate, I wrote this function (well, it's two functions) to fix the issue.

It's far from being the most efficient thing in the world, but it only needs to be run once, so optimization wasn't a big deal to me.


Replaces the following:
<!-- m --><a class="postlink" href="http://www.google.com">Google!</a><!-- m -->
with:
[url=http://www.google.com]Google![/url]


The Function(s):
DELIMITER ||
DROP FUNCTION IF EXISTS GET_DATA||
CREATE FUNCTION GET_DATA( _data LONGTEXT, _begin LONGTEXT, _end LONGTEXT) RETURNS LONGTEXT
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
	DECLARE _startPos INT UNSIGNED;
	DECLARE _endPos INT UNSIGNED;
	SET _startPos = LOCATE(_begin, _data, 1);
	IF _startPos < 1 THEN RETURN NULL; END IF;
	SET _startPos = _startPos + LENGTH(_begin);
	SET _endPos = LOCATE(_end, _data, _startPos);
	RETURN SUBSTRING(_data,_startPos,_endPos - _startPos);
END;
||
DELIMITER ;

# SELECT GET_DATA( `message` , '<!-- m --><a class="postlink" href="', '">' ) 
# FROM `mybb_posts` 
# WHERE GET_DATA( `message` , '<!-- m --><a class="postlink" href="', '">' ) IS NOT NULL 
# LIMIT 0,5077;

DELIMITER ||
DROP FUNCTION IF EXISTS PHPBB3_TO_MYBB_URLS||
CREATE FUNCTION PHPBB3_TO_MYBB_URLS( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
	DECLARE _linkURL VARCHAR(500);
	DECLARE _linkText VARCHAR(500);
	DECLARE _begin VARCHAR(500);
	DECLARE _middle VARCHAR(500);
	DECLARE _end VARCHAR(500);
	DECLARE _startPos INT UNSIGNED;
	DECLARE _endPos INT UNSIGNED;
	SET _begin = '<!-- m --><a class="postlink" href="';
	SET _middle = '">';
	SET _end = '</a><!-- m -->';
	LOOP
		SET _linkURL = GET_DATA(x,_begin,_middle);
		IF (_linkURL IS NULL) THEN RETURN x; END IF;
		SET _linkText = GET_DATA(x,CONCAT(_linkURL,_middle),_end);
		SET x = REPLACE(x,CONCAT(_begin,_linkURL,_middle,_linkText,_end),CONCAT('[url=',_linkURL,']',_linkText,'[/url]'));
		SET _linkURL = GET_DATA(x,_begin,_middle);
	END LOOP;
	RETURN x;
END;
||
DELIMITER ;

# UPDATE `mybb_posts` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);

If you take the time to look through the code, you'll see how relatively easy it is to adopt it to suit your needs (if you need to convert some other special HTML tags or whatever).

Anyway...

Example usage:
UPDATE `mybb_posts` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);

UPDATE `mybb_privatemessages` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);

UPDATE `mybb_users` SET `signature` = PHPBB3_TO_MYBB_URLS(`signature`);

Optionally run the following to drop the functions after you're done with them:
DROP FUNCTION PHPBB3_TO_MYBB_URLS;
DROP FUNCTION GET_DATA;



Notice that I didn't put any limits. I had no need for them. I ran it all locally and then migrated to the production site. The forum that I ran it on only had 5000 posts and 500 topics. So YMMV. I suggest doing a backup first.


On a related note, I also ran the following to fix unordered lists:
UPDATE `mybb_posts` SET `message` = REPLACE(`message`,'[/list:u]','[/list]');
UPDATE `mybb_privatemessages` SET `message` = REPLACE(`message`,'[/list:u]','[/list]');
UPDATE `mybb_users` SET `signature` = REPLACE(`signature`,'[/list:u]','[/list]');

Hmm, I just saw the "solved"/"not solved" stuff.

Did I post this in the wrong section? Undecided


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Dylan M. - 2011-09-19

What version of the merge system are you using? The links issue has been fixed already... or should have been anyways. As for the unordered lists issue... please make a bug report on the development tracker:
http://dev.mybb.com/projects/mybb-import/issues

If you're using version 1.6.1 of the merge system, then please report the links issue as well. Undecided


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - cvwan8ur - 2011-09-19

I'm pretty sure I used 1.6.1. It was one month ago as of today that I did the migration.

I still have the merge package that I used:

mybb_merge_1601.zip
md5: df7774c612074f872865d9f610f64a06


index.php contains the following:
$merge_version = "1.6.1";
$version_code = 1601;

So I'm guessing that's the one you're talking about, right?


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Dylan M. - 2011-09-19

That it is. Please post your bugs (they can be a single bug report with something like "phpBB3 BBCode conversion issues")


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Skyrider - 2012-01-13

I can confirm this. While this may be a few months old threads, I'm having the same issue. All links never properly converted from PHPBB to MyBB. I downloaded both latest MyBB & MyBB Merger from the website today.


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Dylan M. - 2012-01-14

You can confirm it sure, but neither you nor the original post have put examples here inside of code tags as I requested. I can't fix what I can't see.


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Skyrider - 2012-01-14

What examples do you need exactly? This is what I found in my index.php:

$merge_version = "1.6.2";
$version_code = 1602;

If you need more examples, do let me know.


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Dylan M. - 2012-01-16

(2012-01-14, 08:30 AM)FF|Skyrider Wrote: What examples do you need exactly? This is what I found in my index.php:

$merge_version = "1.6.2";
$version_code = 1602;

If you need more examples, do let me know.

I need examples of the bad code that gets imported into MyBB. A copy of the post that has such bad codes in it.


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Skyrider - 2012-01-16

(2012-01-16, 08:01 AM)Dylan M. Wrote:
(2012-01-14, 08:30 AM)FF|Skyrider Wrote: What examples do you need exactly? This is what I found in my index.php:

$merge_version = "1.6.2";
$version_code = 1602;

If you need more examples, do let me know.

I need examples of the bad code that gets imported into MyBB. A copy of the post that has such bad codes in it.
I no longer have those seeing I converted the bad code to proper ones with some help from the forums.

However, it's the exact same issue as the main poster mentioned:

<!-- m --><a class="postlink" href="http://www.google.com">Google!</a><!-- m -->

I could provide the file which helped me converting the bad code to proper ones.


RE: SQL function for converting phpBB3 URLs over MyBB syntax. - Dylan M. - 2012-01-16

(2012-01-16, 11:02 AM)FF|Skyrider Wrote: However, it's the exact same issue as the main poster mentioned:

<!-- m --><a class="postlink" href="http://www.google.com">Google!</a><!-- m -->

I could provide the file which helped me converting the bad code to proper ones.

Hmm, and those should all be taken care of already. I'll have to check the parser again I guess.