SQL to update URL's

[thecoalman]

I only need to run this once so efficiency is irrelevant, how do I get the placeholder into the select statement?

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE
(
    post_text, 

    'http://example.com/about([0-9]{1,5}).html', 
    

    CONCAT('https://example.com/forum/viewtopic.php?f=' , (SELECT `forum_id` FROM phpbb_topics WHERE `topic_id` = '\\1')  , '&t=\\1')
)

This returns 0 results presumably because it's looking for \\1 instead of the placeholder value. Testing this changing the placeholder \\1 to an actual topic ID works but obviously it's wrong value. Just so it's clear there isno issue with the placeholder in '&t=\\1'

[thecoalman]

Any help with this? If I can get this to work as outlined it would be very helpful now and in the future.

[RMcGirr83]

Maybe I don't understand but what populates this?

the placeholder value

Are you running this directly in phpmyadmin or within a PHP file?

[thecoalman]

Maybe I don't understand but what populates this?

It's using regex.

Code: Select all

 'http://example.com/about([0-9]{1,5}).html'

That gets me the topic ID and it's held in the placholder \\1

If I just wanted to do this it will work fine.

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE
(
    post_text, 

    'http://example.com/about([0-9]{1,5}).html', 
    

    'https://example.com/forum/viewtopic.php?t=\\1')
)

I have been using this right in phpMyadmin for many one off queries for a pattern match. This for example will get the correct forum ID for the topic 12345 from the topics table assuming there is topic ID in the topics table 12345

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE
(
    post_text, 

    'http://example.com/about([0-9]{1,5}).html', 
    

    CONCAT('https://example.com/forum/viewtopic.php?f=' , (SELECT `forum_id` FROM phpbb_topics WHERE `topic_id` = 12345)  , '&t=\\1')
)

The issue is getting topic ID held in placeholder into the select statement.