fix_html5gamedevs_codeblocks.sql

<?php
    // CONFIG: setting this to a post id will TEST ONLY and print results for that post only
    // set it to zero to process all posts
    $id = 0; 
                
    // CONFIG: set to your database config         
    $mysqli = new mysqli("YOUR_ADDRESS_OR_IP", "YOUR_USER", "YOUR_PASSWORD", "html5forum");
    
    // grab all posts that have <pre> blocks
    $sql = "select old.topic_id, old.pid, old.post as old, new.post as new, (LENGTH(old.post) - LENGTH(REPLACE(old.post, '<pre', ''))) / LENGTH('<pre') AS cnt from forums_posts new join orig_posts old ON old.pid = new.pid WHERE (LENGTH(old.post) - LENGTH(REPLACE(old.post, '<pre', ''))) / LENGTH('<pre')  > 0";
    if ($id > 0)
        $sql .= " AND old.pid = " . $id;
    $sql .= " ORDER BY old.pid";
    $result = $mysqli->query($sql);
    $total = mysqli_num_rows($result);
    $processed = 0;
    while ($row = $result->fetch_assoc()) {
        
        // find every <pre> block in the post
        $pattern = '/<pre.+\/pre>/s';
        preg_match($pattern, $row["old"], $oldMatches, PREG_OFFSET_CAPTURE);
        preg_match($pattern, $row["new"], $newMatches, PREG_OFFSET_CAPTURE);

        if (count($oldMatches) != count($newMatches)) {
            // number of <pre> blocks is not the same in the old + new versions of the post, should be investigaged so kill process
            printf("\nTopic %d, Comment: %d mistmatch.  Expected: %d, actual: %d", $row['topic_id'], $row['pid'], count($oldMatches), count($newMatches));
            printf("\nOLD: %s\nNEW: %s\n\nProcessed:%d of %d\n", $row["old"], $row["new"], $processed, $total);
            exit();
        }
        else {
            
            // replace new <pre> blocks with old <pre> blocks  
            $post = "";
            $last = 0;
            for ($i = 0; $i < count($oldMatches); $i++) {
                $post .= substr($row['new'], $last, $newMatches[$i][1]);
                $post .= $oldMatches[$i][0];
                $last = $newMatches[$i][1] + strlen($newMatches[$i][0]);  
            }
            $post .= substr($row['new'], $last);
            if ($id > 0)
                printf("Old: %s\n\nNew: %s\n\nFixed: %s\n", $row['old'], $row['new'], $post);
            else {
                // sql update post
                $statement = $mysqli->prepare("UPDATE forums_posts SET post=? WHERE pid=?");
                $statement->bind_param('si', $post, $row['pid']);
                $results =  $statement->execute();
                if(!$results){
                    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
                    exit();
                }
            }
            $processed++;
        }
    }
?>