XF 2.3 PHPBB import

[andybond]

I have imported a PHPBB 3,2 site to my Xenforo 2.3.6 dev site.

I am seeing the following instead of images.



Any suggestions how I can get the image instead of what appears to be an album shortcode? I believe its from a phpbb gallery plugin

 

[MentaL]

Resource icon

Post Content Find / Replace

Oct 19, 2017

Allows an administrator to do a regular expression find and replace in the content of all posts.

• XenForo
• Add-ons [2.x]

You'll need this.

 

[andybond]

Resource icon

Post Content Find / Replace

Oct 19, 2017

Allows an administrator to do a regular expression find and replace in the content of all posts.

• XenForo
• Add-ons [2.x]

You'll need this.

Ok, that makes sense.

What regex will I use?

 

[andybond]

This is what I have worked out so far.

The shortcode in the phpBB gallery is linked to a table phpbb_gallery_images
The image ID is a sequentially incremental number and reveals the image name in the imagefilename.

Now all I have to do is rewrite the posts that have [album]numbers[/album] to the correct URL.

This is going to be fun.

 

[andybond]

With some judicious use of AI I did a SQL Query to rewrite the [album] number [/album] with the correct URL.

What you need to do is extract two columns from a table called gallery_id_map in phpBB Gallery.
Add these two a newly created table in your Xenforo table.

Then the following script needs to be run in mySQL

-- Step 1: Drop the existing procedure if it exists
DROP PROCEDURE IF EXISTS update_album_tags;

-- Step 2: Drop the temporary table if it already exists
DROP TEMPORARY TABLE IF EXISTS posts_to_update;

-- Step 3: Create the Temporary Table to Store Posts That Need Updating
CREATE TEMPORARY TABLE posts_to_update AS
SELECT p.post_id, p.message
FROM xf_post AS p
JOIN xf_thread AS t ON p.thread_id = t.thread_id
JOIN xf_forum AS f ON t.node_id = f.node_id
WHERE f.node_id = 665 -- the node ID you want to replace
AND p.message LIKE '%[album]%[/album]%';

-- Step 4: Set the delimiter to allow the creation of the procedure
DELIMITER //

-- Step 5: Create the Procedure to Update Posts with the New Format
CREATE PROCEDURE update_album_tags()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE current_post_id INT;
DECLARE current_message TEXT;
DECLARE updated_message TEXT;

-- Adjust album_id size to ensure it can hold larger IDs (e.g., VARCHAR(100) or more)
DECLARE album_id VARCHAR(255); -- Increase the size to accommodate larger album IDs
DECLARE img_url VARCHAR(255); -- This will now hold the filename
DECLARE album_tag VARCHAR(255); -- Increased size of album_tag to accommodate longer concatenated strings

-- Cursor to iterate through posts that need updating
DECLARE post_cursor CURSOR FOR
SELECT post_id, message FROM posts_to_update;

-- Continue handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Create a log table if it doesn't exist
CREATE TABLE IF NOT EXISTS tag_conversion_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT,
conversion_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
conversion_type VARCHAR(50) DEFAULT '-- Step 1: Drop the existing procedure if it exists
DROP PROCEDURE IF EXISTS update_album_tags;

-- Step 2: Drop the temporary table if it already exists
DROP TEMPORARY TABLE IF EXISTS posts_to_update;

-- Step 3: Create the Temporary Table to Store Posts That Need Updating
CREATE TEMPORARY TABLE posts_to_update AS
SELECT p.post_id, p.message
FROM xf_post AS p
JOIN xf_thread AS t ON p.thread_id = t.thread_id
JOIN xf_forum AS f ON t.node_id = f.node_id
WHERE f.node_id = 736 -- Assuming this is the node_id for v6_your_v6_for_sale_forum
AND p.message LIKE '%[album]%[/album]%';

-- Step 4: Set the delimiter to allow the creation of the procedure
DELIMITER //

-- Step 5: Create the Procedure to Update Posts with the New Format
CREATE PROCEDURE update_album_tags()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE current_post_id INT;
DECLARE current_message TEXT;
DECLARE updated_message TEXT;

-- Adjust album_id size to ensure it can hold larger IDs (e.g., VARCHAR(100) or more)
DECLARE album_id VARCHAR(512); -- Increase the size to accommodate larger album IDs
DECLARE img_url VARCHAR(512); -- This will now hold the filename
DECLARE album_tag VARCHAR(512); -- Increased size of album_tag to accommodate longer concatenated strings

-- Cursor to iterate through posts that need updating
DECLARE post_cursor CURSOR FOR
SELECT post_id, message FROM posts_to_update;

-- Continue handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Create a log table if it doesn't exist
CREATE TABLE IF NOT EXISTS tag_conversion_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT,
conversion_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
conversion_type VARCHAR(50) DEFAULT 'album_to_img'
);

-- Open cursor
OPEN post_cursor;

-- Start processing posts
read_loop: LOOP
FETCH post_cursor INTO current_post_id, current_message;

IF done THEN
LEAVE read_loop;
END IF;

-- Initialize updated message
SET updated_message = current_message;

-- Find and replace all occurrences of [album]ID[/album]
album_loop: WHILE updated_message LIKE '%[album]%' DO
-- Extract the album ID using SUBSTRING and locate the content between [album] and [/album]
SET album_id = SUBSTRING_INDEX(SUBSTRING_INDEX(updated_message, '[album]', -1), '[/album]', 1);
SET album_tag = CONCAT('[album]', album_id, '[/album]');

-- Remove commas from album_id (in case it's formatted with commas)
SET album_id = REPLACE(album_id, ',', '');

-- Ensure the album_id does not exceed 100 characters
IF CHAR_LENGTH(album_id) > 100 THEN
SET album_id = LEFT(album_id, 100); -- Truncate to 100 characters
END IF;

-- Look up the corresponding filename from the gallery_id_map table (remove commas from image_id as well)
SELECT image_filename INTO img_url
FROM gallery_id_map
WHERE REPLACE(image_id, ',', '') = album_id
LIMIT 1;

-- If filename exists, replace the album tag with the full IMG tag with the URL
IF img_url IS NOT NULL THEN
-- Update URL to http://localhost/phpbbgallery/<image_filename>
SET img_url = CONCAT('https://fullurl/Path/butnotimage.jpg', img_url);
SET updated_message = REPLACE(updated_message, album_tag, CONCAT('

'));
ELSE
-- If no filename found, just continue with the next occurrence
SET updated_message = REPLACE(updated_message, album_tag, '');
END IF;
END WHILE album_loop;

-- Update the post with the new message format
UPDATE xf_post
SET message = updated_message,
last_edit_date = UNIX_TIMESTAMP(NOW())
WHERE post_id = current_post_id;

-- Log the update
INSERT INTO tag_conversion_log (post_id)
VALUES (current_post_id);
END LOOP;

-- Close cursor
CLOSE post_cursor;
END //

-- Step 6: Reset the delimiter
DELIMITER ;

-- Step 7: Call the Procedure to Perform the Update
CALL update_album_tags();

-- Step 8: Clean Up - Drop the Procedure and Temporary Table
DROP PROCEDURE update_album_tags;
DROP TEMPORARY TABLE IF EXISTS posts_to_update;
album_to_img'
);

-- Open cursor
OPEN post_cursor;

-- Start processing posts
read_loop: LOOP
FETCH post_cursor INTO current_post_id, current_message;

IF done THEN
LEAVE read_loop;
END IF;

-- Initialize updated message
SET updated_message = current_message;

-- Find and replace all occurrences of [album]ID[/album]
album_loop: WHILE updated_message LIKE '%[album]%' DO
-- Extract the album ID using SUBSTRING and locate the content between [album] and [/album]
SET album_id = SUBSTRING_INDEX(SUBSTRING_INDEX(updated_message, '[album]', -1), '[/album]', 1);
SET album_tag = CONCAT('[album]', album_id, '[/album]');

-- Remove commas from album_id (in case it's formatted with commas)
SET album_id = REPLACE(album_id, ',', '');

-- Ensure the album_id does not exceed 100 characters
IF CHAR_LENGTH(album_id) > 100 THEN
SET album_id = LEFT(album_id, 100); -- Truncate to 100 characters
END IF;

-- Look up the corresponding filename from the gallery_id_map table (remove commas from image_id as well)
SELECT image_filename INTO img_url
FROM gallery_id_map
WHERE REPLACE(image_id, ',', '') = album_id
LIMIT 1;

-- If filename exists, replace the album tag with the full IMG tag with the URL
IF img_url IS NOT NULL THEN
-- Update URL to http://localhost/phpbbgallery/<image_filename>
SET img_url = CONCAT('https://turborenault.co.uk/import/v6clio/files/phpbbgallery/core/source/', img_url);
SET updated_message = REPLACE(updated_message, album_tag, CONCAT('

'));
ELSE
-- If no filename found, just continue with the next occurrence
SET updated_message = REPLACE(updated_message, album_tag, '');
END IF;
END WHILE album_loop;

-- Update the post with the new message format
UPDATE xf_post
SET message = updated_message,
last_edit_date = UNIX_TIMESTAMP(NOW())
WHERE post_id = current_post_id;

-- Log the update
INSERT INTO tag_conversion_log (post_id)
VALUES (current_post_id);
END LOOP;

-- Close cursor
CLOSE post_cursor;
END //

-- Step 6: Reset the delimiter
DELIMITER ;

-- Step 7: Call the Procedure to Perform the Update
CALL update_album_tags();

-- Step 8: Clean Up - Drop the Procedure and Temporary Table
DROP PROCEDURE update_album_tags;
DROP TEMPORARY TABLE IF EXISTS posts_to_update;