Hongmu Notes
Home › Program Notes › A single database record for WordPress operations
Program Notes wordpress

A single database record for WordPress operations

updated on 2023-07-01 19:44:22 Browse 173 📖 approx. 30 minutes
A single database record for WordPress operations

I'm documenting my code – its primary function is handling WordPress database connections and operations. Since I think I might need this again in the future, I've decided to jot it down once more; this way, when I need to use it next time, I can simply copy and paste it. As it turns out, taking notes can really be a handy shortcut!

First code snippet

<?php
// 引入WordPress的核心文件
require_once(dirname(__FILE__) . '/wp-load.php');

// 连接数据库
$host = 'localhost';
$user = '';
$password = 'G2b8pG83Ypnk8rMR';
$database = '';

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die('连接失败: ' . $mysqli->connect_error);
}

// 执行查询操作
global $wpdb;
$table_name = $wpdb->prefix . 'posts'; // 获取表名
$sql = "SELECT id, post_title as title, post_content as content, guid as link FROM {$wpdb->prefix}posts WHERE `post_type` = 'post'";
// `post_content` LIKE '%class=\"panel-body\"%' and 
$result = $mysqli->query($sql);

// 循环判断并处理数据
if ($result !== false) {
    foreach ($result as $row) {
        $content = $row['content'];

        /* 使用正则表达式匹配所有的a标签,正则：/<a\s+[^>]*?href=[\'"]([^\'"]*?)[\'"][^>]*?>/i */
        
        // 使用正则表达式匹配所有子页面的具体链接
        $matches = [];
        preg_match_all('/【--(.*?)--】/i', $content, $matches);
        if(!empty($matches[1])){
            $herf = "/{$row['id']}/";
            $href_array[$herf] = $matches[1][0];
            echo "成功匹配子页面信息，本站子页面链接【<a href='{$herf}' style='color:green;' target='_blank'><b>{$herf}</b></a>】,采集站页面链接【{$matches[1][0]}】，标题为【<a href='{$herf}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
        }
    }
    $num = count($href_array);
    echo "<p>共计{$num}个子页面</p>";
}

// 循环判断并处理数据2
if ($result !== false) {
    foreach ($result as $row) {
        
        $content = $row['content'];
        
        foreach ($href_array as $k=>$v){
            $original_content = $content;
            $content = str_replace("href=\"{$v}\"","href=\"{$k}\"",$content);
            if ($content !== $original_content) {
                echo "----查询到一个子页面链接，已将子页面链接修正至本站，链接【<a href='{$k}' style='color:green;' target='_blank'><b>{$k}</b></a>】<br>";
            }
        }
        
        // 更新文章内容
        $sql = "UPDATE {$wpdb->prefix}posts SET post_content = '{$content}' WHERE ID = {$row['id']}";
        $mysqli->query($sql);

        if ($mysqli->errno) {
            die('更新失败: ' . $mysqli->error);
        }else{
            echo "已处理文章【<a href='{$row['link']}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
        }

        // 更新文章发布时间和修改时间
        $wpdb->update(
            "{$wpdb->prefix}posts",
            array(
                // 'post_modified' => current_time('mysql'),
                'post_modified_gmt' => current_time('mysql', 1)
            ),
            array('ID' => $row['id'])
        );
        
        // 处理文章
        // if (isset($matches[1])) {
        //     $targetDiv = $matches[0];
        //     print_r($targetDiv);
        //     // 在目标 <div> 元素中使用正则表达式匹配文章标题，并修改为带链接的形式
        //     preg_match_all('/<h\d>(.*?)<\/h\d>/is', $targetDiv, $matches);

        //     foreach ($matches[1] as $match) {
        //         if (strpos($content, $match) !== false && $match !== $row['title']) {
        //             $link = '<a href="' . $row['link'] . '">' . $row['title'] . '</a>';
        //             $targetDiv = str_replace($match, $match . $link, $targetDiv);
        //         }
        //     }

        //     // 替换原文中的目标 <div> 元素为修改后的内容
        //     $content = str_replace($matches[0], $targetDiv, $content);

        //     // 更新文章内容
        //     $sql = "UPDATE {$wpdb->prefix}posts SET post_content = '{$content}' WHERE ID = {$row['id']}";
        //     $mysqli->query($sql);

        //     if ($mysqli->errno) {
        //         die('更新失败: ' . $mysqli->error);
        //     }

        //     // 更新文章发布时间和修改时间
        //     $wpdb->update(
        //         "{$wpdb->prefix}posts",
        //         array(
        //             'post_modified' => current_time('mysql'),
        //             'post_modified_gmt' => current_time('mysql', 1)
        //         ),
        //         array('ID' => $row['id'])
        //     );
        // }
    }
}

// 关闭数据库连接
$mysqli->close();

First code optimization

<?php
// 引入WordPress的核心文件
require_once(dirname(__FILE__) . '/wp-load.php');

// 连接数据库
$host = 'localhost';
$user = '';
$password = 'G2b8pG83Ypnk8rMR';
$database = '';

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die('连接失败: ' . $mysqli->connect_error);
}

// 执行查询操作
global $wpdb;
$table_name = $wpdb->prefix . 'posts'; // 获取表名
$sql = "SELECT id, post_title as title, post_content as content, guid as link FROM {$wpdb->prefix}posts WHERE `post_type` = 'post'";
$result = $mysqli->query($sql);

// 判断查询结果
if ($result !== false) {
    $href_array = array(); // 存储子页面链接
    while ($row = $result->fetch_assoc()) {
        $content = $row['content'];

        /* 使用正则表达式匹配所有的a标签,正则：/<a\s+[^>]*?href=[\'"]([^\'"]*?)[\'"][^>]*?>/i */

        // 使用正则表达式匹配所有子页面的具体链接
        $matches = [];
        preg_match_all('/【--(.*?)--】/i', $content, $matches);
        if (!empty($matches[1])){
            $herf = "/{$row['id']}/";
            $href_array[$herf] = $matches[1][0];
            echo "成功匹配子页面信息，本站子页面链接【<a href='{$herf}' style='color:green;' target='_blank'><b>{$herf}</b></a>】,采集站页面链接【{$matches[1][0]}】，标题为【<a href='{$herf}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
        }
    }
    $num = count($href_array);
    echo "<p>共计{$num}个子页面</p>";

    // 循环更新文章内容
    foreach ($href_array as $k => $v) {
        $sql = "UPDATE {$wpdb->prefix}posts SET post_content = REPLACE(post_content, 'href=\"{$v}\"', 'href=\"{$k}\"') WHERE post_content LIKE '%href=\"{$v}\"%' AND `post_type` = 'post'";
        $mysqli->query($sql);

        if ($mysqli->errno) {
            die('更新失败: ' . $mysqli->error);
        } else {
            echo "已处理文章【<a href='{$row['link']}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
        }
    }

    // 更新文章发布时间和修改时间
    $wpdb->query("UPDATE {$wpdb->prefix}posts SET post_modified = NOW(), post_modified_gmt = UTC_TIMESTAMP() WHERE `post_type` = 'post'");
}

// 关闭数据库连接
$mysqli->close();

Second update – new caching feature added... – primarily addresses Server 503 errors.

<?php
// 引入WordPress的核心文件
require_once(dirname(__FILE__) . '/wp-load.php');

// 连接数据库
$host = 'localhost';
$user = 'www_coserhub_net';
$password = 'G2b8pG83Ypnk8rMR';
$database = 'www_coserhub_net';

// 采集站的网站地址
$site_caiji = "https://www.cosjidi01.com";

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die('连接失败: ' . $mysqli->connect_error);
}

// 执行查询操作
global $wpdb;
$table_name = $wpdb->prefix . 'posts'; // 获取表名

$cache_file = "子页面链接.json";
if(file_exists($cache_file)){
    if (time() - filectime($cache_file) > 180) {
        unlink($cache_file);
        echo("会话过期，已删除之前缓存文件！");
        $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'];
        echo '<meta http-equiv="refresh" content="2; url='.$url.'">';
        die();
    }
}
// 默认查询全部数据
$sql = "SELECT id, post_title as title, post_content as content, guid as link 
        FROM {$wpdb->prefix}posts 
        WHERE `post_type` = 'post' ";
// 判断是否传入参数控制偏移量
if (isset($_GET['next'])) {
    $offset = intval($_GET['next']);
    $sql .= " AND post_content  NOT LIKE '%【--/pic/%' LIMIT 100 OFFSET $offset";
}
$result = $mysqli->query($sql);

if(file_exists($cache_file)){
    // 循环判断并处理数据
    $href_array = json_decode(file_get_contents($cache_file),true);
    $next_id = $offset + 100;
    $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?next=$next_id";
    // 循环判断并处理数据2
    if ($result !== false) {
        echo "<p>点击<a href='{$url}'>下一步</a>开始处理下一个一百条</p>{$html}";
        foreach ($result as $row) {
            echo "处理文章【<a href='{$row['link']}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
            $content = $row['content'];
            foreach ($href_array as $k=>$v){
                $original_content = $content;
                $content = str_replace("href=\"{$v}\"","href=\"{$k}\"",$content);
                if ($content !== $original_content) {
                    echo "----查询到一个子页面链接，已将子页面链接修正至本站，链接【<a href='{$k}' style='color:green;' target='_blank'><b>{$k}</b></a>】<br>";
                }
            }
            foreach ($href_array as $k=>$v){
                $original_content = $content;
                $v = $site_caiji.$v;
                $content = str_replace("href=\"{$v}\"","href=\"{$k}\"",$content);
                if ($content !== $original_content) {
                    echo "----查询到一个子页面链接，已将子页面链接修正至本站，链接【<a href='{$k}' style='color:green;' target='_blank'><b>{$k}</b></a>】<br>";
                }
            }
            // 更新文章内容
            $sql = "UPDATE {$wpdb->prefix}posts SET post_content = '{$content}' WHERE ID = {$row['id']}";
            $mysqli->query($sql);
    
            if ($mysqli->errno) {
                die('更新失败: ' . $mysqli->error);
            }else{
                echo "成功更新文章【<a href='{$row['link']}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
            }
    
            // 更新文章发布时间和修改时间
            $wpdb->update(
                "{$wpdb->prefix}posts",
                array(
                    // 'post_modified' => current_time('mysql'),
                    'post_modified_gmt' => current_time('mysql', 1)
                ),
                array('ID' => $row['id'])
            );
        }
    }
}else{
    
    if ($result !== false) {
        
        foreach ($result as $row) {
            $content = $row['content'];
            /* 使用正则表达式匹配所有的a标签,正则：/<a\s+[^>]*?href=[\'"]([^\'"]*?)[\'"][^>]*?>/i */
            // 使用正则表达式匹配所有子页面的具体链接
            $matches = [];
            preg_match_all('/【--(.*?)--】/i', $content, $matches);
            if(!empty($matches[1])){
                $herf = "/{$row['id']}/";
                $href_array[$herf] = $matches[1][0];
                $html .= "成功匹配子页面信息，本站子页面链接【<a href='{$herf}' style='color:green;' target='_blank'><b>{$herf}</b></a>】,采集站页面链接【{$matches[1][0]}】，标题为【<a href='{$herf}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
            }
        }
        $num = count($href_array);
        file_put_contents($cache_file,json_encode($href_array));
        $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?next=1";
        echo "<p>已匹配到{$num}个子页面，点击<a href='{$url}'>下一步</a>开始处理前一百条</p>{$html}";
    }
}

// 关闭数据库连接
$mysqli->close();

Second version (optimized):

<?php
// 引入WordPress的核心文件
require_once(dirname(__FILE__) . '/wp-load.php');

// 连接数据库
$host = 'localhost';
$user = '';
$password = '';
$database = '';

// 采集站的网站地址
$site_caiji = "";

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_errno) {
    die('连接失败: ' . $mysqli->connect_error);
}

// 执行查询操作
global $wpdb;
$table_name = $wpdb->prefix . 'posts'; // 获取表名

$cache_file = "子页面链接.json";
if(file_exists($cache_file)){
    if (time() - filectime($cache_file) > 180) {
        unlink($cache_file);
        echo("会话过期，已删除之前缓存文件！");
        $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'];
        echo '<meta http-equiv="refresh" content="2; url='.$url.'">';
        die();
    }
}

// 默认查询全部数据
$sql = "SELECT id, post_title as title, post_content as content, guid as link 
        FROM {$wpdb->prefix}posts 
        WHERE `post_type` = 'post' ";

// 判断是否传入参数控制偏移量
if (isset($_GET['next'])) {
    $offset = intval($_GET['next']);
    $sql .= " AND post_content  NOT LIKE '%【--/pic/%' LIMIT 100 OFFSET $offset";
}

$result = $mysqli->query($sql);

if(file_exists($cache_file)){
    // 循环判断并处理数据
    $href_array = json_decode(file_get_contents($cache_file), true);
    $next_id = $offset + 100;
    $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?next=$next_id";

    // 循环判断并处理数据2
    if ($result !== false) {
        $html = '';
        foreach ($result as $row) {
            $html .= processArticle($row, $href_array, $site_caiji);
        }
        echo "<p>点击<a href='{$url}'>下一步</a>开始处理下一个一百条</p>{$html}";
    }
}else{
    if ($result !== false) {
        $html = '';
        $href_array = [];
        foreach ($result as $row) {
            $html .= processContent($row, $href_array);
        }
        $num = count($href_array);
        file_put_contents($cache_file, json_encode($href_array));
        $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?next=1";
        echo "<p>已匹配到{$num}个子页面，点击<a href='{$url}'>下一步</a>开始处理前一百条</p>{$html}";
    }
}

// 关闭数据库连接
$mysqli->close();

function processArticle($row, &$href_array, $site_caiji) {
    $html = '';
    $content = $row['content'];
    foreach ($href_array as $k => $v) {
        $original_content = $content;
        $content = str_replace("href=\"{$v}\"","href=\"{$k}\"",$content);
        if ($content !== $original_content) {
            $html .= "----查询到一个子页面链接，已将子页面链接修正至本站，链接【<a href='{$k}' style='color:green;' target='_blank'><b>{$k}</b></a>】<br>";
        }
    }
    foreach ($href_array as $k => $v) {
        $original_content = $content;
        $v = $site_caiji.$v;
        $content = str_replace("href=\"{$v}\"","href=\"{$k}\"",$content);
        if ($content !== $original_content) {
            $html .= "----查询到一个子页面链接，已将子页面链接修正至本站，链接【<a href='{$k}' style='color:green;' target='_blank'><b>{$k}</b></a>】<br>";
        }
    }
    // 更新文章内容
    $sql = "UPDATE {$wpdb->prefix}posts SET post_content = '{$content}' WHERE ID = {$row['id']}";
    $mysqli->query($sql);

    if ($mysqli->errno) {
        die('更新失败: ' . $mysqli->error);
    }else{
        $html .= "成功更新文章【<a href='{$row['link']}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
    }

    // 更新文章发布时间和修改时间
    $wpdb->update(
        "{$wpdb->prefix}posts",
        array(
            // 'post_modified' => current_time('mysql'),
            'post_modified_gmt' => current_time('mysql', 1)
        ),
        array('ID' => $row['id'])
    );

    return "处理文章【<a href='{$row['link']}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>" . $html;
}

function processContent($row, &$href_array) {
    $content = $row['content'];
    /* 使用正则表达式匹配所有的a标签,正则：/<a\s+[^>]*?href=[\'"]([^\'"]*?)[\'"][^>]*?>/i */
    // 使用正则表达式匹配所有子页面的具体链接
    $matches = [];
    preg_match_all('/【--(.*?)--】/i', $content, $matches);
    if (!empty($matches[1])) {
        $herf = "/{$row['id']}/";
        $href_array[$herf] = $matches[1][0];
        return "成功匹配子页面信息，本站子页面链接【<a href='{$herf}' style='color:green;' target='_blank'><b>{$herf}</b></a>】,采集站页面链接【{$matches[1][0]}】，标题为【<a href='{$herf}' style='color:green;' target='_blank'><b>{$row['title']}</b></a>】<br>";
    }
    
    return '';
}
微信赞赏

WeChat

支付宝赞赏

Alipay

Copyright Statement · No reproduction allowed

Author of this article: Hong Mu

Link to this article: https://www.m120.vip/en/archives/1439.html

Copyright statement:This article is an original article. Any form of reproduction, collection, or mirroring without authorization is prohibited. Violators will be prosecuted. If you need to reprint, please indicate the source and keep the link to the original text.

✍️ Author: Hong Mu

webmaster · Thanks for reading, stay tuned for more exciting content

Author homepage View home page →

Related articles

What should I do if the Redis Object Cache plug-in cannot be linked after wordpress redis changes the password?

What should I do if the Redis Object Cache plug-in cannot be linked after wordpress redis changes the password? Program Notes wordpress

Problem description: My wordpress site server uses pagoda panel, installed php8.0, and installed redis on the extension panel, and installed Redis Object Cache plug-in in wordpress. In fact, in general, you don't need to do anything, just start it directly. However, it is unsafe for radis not to set a password, which is a risky loophole, so I still think it is better to set a password, …
👁 255
How do I disable revisions and automatic draft saving in WordPress?

How do I disable revisions and automatic draft saving in WordPress? Program Notes wordpress

WordPress's automatic article revision tracking feature logs every time you edit an article in the后台; each revision is recorded as a separate entry in the `wp_posts` table. Due to the interplay between article revisions and automatic saving, the article ID often grows larger over time. While this typically does not cause significant issues for your WordPress installation, an excessive number of article versions can place a considerable burden on your storage space and database...
👁 372
Detailed Tutorial on Migrating EmpireCMS Blog Data to WordPress

Detailed Tutorial on Migrating EmpireCMS Blog Data to WordPress Program Notes wordpress

PS1: Second revision – now supports data transfer between categories, posts, and tags. PS2: If the post IDs consistently don't match, you can try clearing the WordPress post category data table and relationship tables. I previously used Typecho for my blog, but later migrated to Empire; however, Empire has too many features – using it for a blog feels like putting a large hammer into a small hole! Additionally, I often record important code snippets in my blog – all of which I've painstakingly written...
👁 546
WordPress issue: pages freezing due to image transcoding

WordPress issue: pages freezing due to image transcoding Program Notes wordpress

Last night, while uploading an image, for some reason, the uploaded image was automatically transcoded. Transcoding type: BASE64 encoding. For an image on a website, simply upload it to your server and then access the link – the image will then be displayed. However, there is another approach: directly transcoding the image; then you can simply access the converted image using its encoded URL. Problem description: When an image is relatively small (e.g., only a few KB in size), transcoding it and then hosting it on a website can help avoid...
👁 152
WordPress database query operations

WordPress database query operations Program Notes wordpress

To connect to a database for a WordPress installation, you need to include the `wp-config.php` file in your PHP script. This file contains the database configuration details for WordPress installation and other constants. Here is a simple example demonstrating how to include the `wp-config.php` file and establish a database connection: // Include the wp-config.php file: require...
👁 377
WordPress MySQL Database Table and Subtable Structure Functionality Guide

WordPress MySQL Database Table and Subtable Structure Functionality Guide Program Notes wordpress

WordPress uses a MySQL database. Its data table structure differs slightly from that of conventional databases; data can be stored either in the form of standard data tables or as data elements – the latter approach is less intuitive when viewed directly. To become a developer, you must understand the basic structure of the WordPress database and be able to use it within your own plugins or themes to write to or read from the database. I. Data Tables – As of WordPress...
👁 374

Recommended reading

(Adaptive mobile version) Responsive cultural media company website – PBootCMS template; Download source code for entertainment主播 live-streaming training websites – 0287

(Adaptive mobile version) Responsive cultural media company website – PBootCMS template; Download source code for entertainment主播 live-streaming training websites – 0287 Practical Collection pbootcms Template

This is a responsive mobile-friendly website template developed by PbootCMS for cultural and media companies. Its playful and trendy design makes it ideal for entertainment streamers and live-streaming training companies to showcase their services and artists. It helps cultural and media enterprises attract streamers and secure partnership opportunities online. Template Demo | Installation Instructions | Website Backend: /admin.php | Username: admin | Password: admin | Extraction Password: www.4s5.cn | Related Articles: Pbo...
👁 58
(Adaptive mobile version) Hotel bedding English-language international e-commerce website template – includes download functionality and three-level navigation menu – 0982

(Adaptive mobile version) Hotel bedding English-language international e-commerce website template – includes download functionality and three-level navigation menu – 0982 Practical Collection pbootcms Template

A professional English-language PbootCMS website template designed for hotel bedding products targeting international trade, compatible with both PC and WAP devices, featuring a download function and a three-level navigation structure. Its sophisticated, international design makes it ideal for hotel bedding manufacturers and exporters to showcase their products and brand identity, supporting effective brand promotion in global markets. Template Preview | Installation Instructions | Website Backend: /admin.php | Username: admin | Password: admin | Extraction Password: www.4s5....
👁 41
Home Interior Design Studio Website Template 0518

Home Interior Design Studio Website Template 0518 Practical Collection Yiyou template

An eYou CMS website template designed specifically for home design studios. Its creative, modern design style is ideal for showcasing home design projects, spatial planning proposals, design concepts, and brand stories. This template helps home design studios effectively demonstrate their expertise online and attract homeowners and developers. Template Demo | Installation Instructions | Website Backend: /login.php | Username: admin | Password: admin | Related Articles: Summary of Common Installation Issues for eYou CMS | eYou CMS...
👁 40