MyBB Community Forums

Hi everyone.

Just merged a very old PHPBB3 (beta) forum with MyBB 1.6.12 and so far it's looking pretty good, but I've come across an error with the URLs.

URLs posted within the site now look like this:

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

Instead of:

[url=http://www.google.com]whatever[/url]

I've tried using the following code in PHPMyAdmin (from this post: http://community.mybb.com/thread-104182.html )but it gets thrown out:

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`);

No errors in the merge report. Can't download the whole debug log as it's too big - the server hangs.

Any help gratefully received!

---

After a lot of fruitless searching, I found DylanM's message here:

http://community.mybb.com/thread-65098-page-2.html

And after a little code tweaking it worked perfectly. Good news seeing as there were tens of thousands of links...

Dylan - you're a miracle-worker, thank you.