Fair warning: There’s a pretty lengthy lead-in here.
After migrating a forum from one site to another, I’ve set out to try to replace old links with their equivalents on the new site.
The trouble is that none of the IDs can be expected to match. So, I have to replace every ID in every in-site link. I have a large table of the old links and the current IDs that corresponds to them, so that’s not an issue. What is an issue is working out how to do the conversion.
There’s over 15,000 topics and 460k+ posts, so searching through every post for one topic ID, and then doing that again 14,999+ times, isn’t a time-efficient or reasonable solution.
What I thought would be a decent solution would be to look for posts where there’s content that fits the pattern of the link. After finding the first such post, get the link (although there might be more than one!), get its ID, find the matching ID in the link table, get the current replacement from that row, and then replace the link in the original post with the new one.
As usual in programming, the trouble’s in the details. I decided regular expressions might be the best way to make this happen, but I can’t get them to produce anything even remotely useful.
Here’s what the current code looks like, with genericized names. Right now it’s only single-use since I’m trying to get it to work at all, and verify that it does what I want it to do before I scale it up.
[php] <?php
session_start();
$servername = “localhost”;
$username = “username”;
$password = “password”;
$link = new PDO(“mysql:host=$servername;dbname=dbname”, $username, $password);
$link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$total_targets = $link->query(“SELECT id_msg
, body
FROM dbname
.smf_messages
WHERE (CONVERT(body
USING utf8) LIKE ‘%7.genericizedlink.com/sitename/index.php?showtopic=%’);”);
$row = $total_targets->fetch(PDO::FETCH_ASSOC);//) {
$ebert = $row[‘body’];
$reggie = $link->query(“SELECT REGEXP_SUBSTR(’$ebert’,’(7.genericizedlink.com/sitename/index.php?showtopic=\d+)’)”);
$fetch = $reggie->fetch(PDO::FETCH_OBJ);
var_dump($fetch);
?>[/php]
And the results:
object(stdClass)#4 (1) { ["REGEXP_SUBSTR('[url=http://b7.genericizedlink.com/sitename/index.php?showtopic=239]http://b7.genericizedlink.com/sitename/i...p?showtopic=239[/url]Insert text from the relevant post here. -_- string(0) “” }
I don’t think Regexp_substr is doing anything at all, and I’m not sure why not. From what I could gather from the syntax in the examples in the manual, this is the correct format, but obviously that isn’t actually the case.
Any insight that anyone can offer here would be greatly appreciated!