最新消息:觉得本站不错的话 记得收藏哦 博客内某些功能仅供测试 讨论群:135931704 快养不起小站了 各位有闲钱就打赏下把 My Email weicots#gmail.com Please replace # with @

wordpress 大批量删除无用评论 以及垃圾评论统计 和文章 php 代码

PHP ajiang-tuzi 1323浏览

加载配置文件

<?php
// WEICOT 数据库引入文件
include_once("Mysql.php");
use  Weicot\Mysql;
$DB=new MySql;
$DB->setDb("wp_20012"); // 选择数据库

删除同一ip 超过某个数值的评论

//同一IP 评论统计代码

$sql='select comment_author_IP,
count(comment_author_IP) as num
from (
select comment_author_IP
from wp_comments
where comment_author_IP is not null
)  as total
group by comment_author_IP
order by num desc';  //评论统计

$data=$DB->data($sql);   //获得数据
$total=20;    //评论大于多少(同一Ip 的评论数)
echo "大于".$total." 评论的IP \r\n";
foreach($data["data"] as $value){
	if($value["num"]>$total){
		 echo $value["comment_author_IP"]."\t".$value["num"]."\t";
	  	 echo $del='delete from wp_comments WHERE comment_author_IP="'.$value["comment_author_IP"].'"';
		 echo "\t";
		 if($DB->exe($del)){    //执行
		 echo "删除完成 \r\n";
		 }

	}else{
		continue;
	}

}

 

C:\Users\Administrator>php C:\Users\Administrator\Desktop\GET\upstatus.php
大于20 评论的IP
46.161.9.23     3921    delete from wp_comments WHERE comment_author_IP="46.161.9.23"   删除完成
46.161.9.22     3462    delete from wp_comments WHERE comment_author_IP="46.161.9.22"   删除完成
146.185.223.55  84      delete from wp_comments WHERE comment_author_IP="146.185.223.55"        删除完成
146.185.223.67  78      delete from wp_comments WHERE comment_author_IP="146.185.223.67"        删除完成
46.118.153.31   74      delete from wp_comments WHERE comment_author_IP="46.118.153.31" 删除完成
146.185.223.57  71      delete from wp_comments WHERE comment_author_IP="146.185.223.57"        删除完成
146.185.223.140 68      delete from wp_comments WHERE comment_author_IP="146.185.223.140"       删除完成
146.185.223.180 67      delete from wp_comments WHERE comment_author_IP="146.185.223.180"       删除完成
146.185.223.77  66      delete from wp_comments WHERE comment_author_IP="146.185.223.77"        删除完成
146.185.223.150 66      delete from wp_comments WHERE comment_author_IP="146.185.223.150"       删除完成
146.185.223.120 66      delete from wp_comments WHERE comment_author_IP="146.185.223.120"       删除完成
146.185.223.110 65      delete from wp_comments WHERE comment_author_IP="146.185.223.110"       删除完成
146.185.223.80  65      delete from wp_comments WHERE comment_author_IP="146.185.223.80"        删除完成
146.185.223.170 65      delete from wp_comments WHERE comment_author_IP="146.185.223.170"       删除完成
146.185.223.73  63      delete from wp_comments WHERE comment_author_IP="146.185.223.73"        删除完成
146.185.223.167 63      delete from wp_comments WHERE comment_author_IP="146.185.223.167"       删除完成
146.185.223.130 61      delete from wp_comments WHERE comment_author_IP="146.185.223.130"       删除完成
146.185.223.97  61      delete from wp_comments WHERE comment_author_IP="146.185.223.97"        删除完成
146.185.223.160 60      delete from wp_comments WHERE comment_author_IP="146.185.223.160"       删除完成
134.249.141.24  59      delete from wp_comments WHERE comment_author_IP="134.249.141.24"        删除完成
134.249.51.228  40      delete from wp_comments WHERE comment_author_IP="134.249.51.228"        删除完成
46.118.157.125  33      delete from wp_comments WHERE comment_author_IP="46.118.157.125"        删除完成

删除评论中含有 http或htts(链接) 的品评论

// 找出  ping论中 带https:的评论并删除   如果只找出 http  那么 %http:% 这里为http
$sql="select * from wp_comments where comment_content like '%https:%' and  'cooment_approved' NOT LIKE '1'  ";
$data=$DB->data($sql);   //获得数据
$total=4;    //评论大于多少
echo "大于".$total." 评论的IP \r\n";
$i=0;
foreach($data["data"] as $value){
		 echo $value["comment_author_IP"]."\t".$value["comment_author_email"]."\t".
		 $value["comment_date"]."\t".$value["comment_agent"]."\r\n";
	  	 echo $del='delete from wp_comments WHERE comment_author_IP="'.$value["comment_author_IP"].'"';
		 echo "\t";
		 if($DB->exe($del)){    //执行
		 echo "删除完成 \r\n";
		 $i++;
		 }

	}
	echo  "total:". $i."\r\n";

新建评论表

$create="CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_mail_notify` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`),
  KEY `comment_author_email` (`comment_author_email`(10))
) ENGINE=MyISAM AUTO_INCREMENT=17412 DEFAULT CHARSET=utf8";
var_dump($DB->exe($create));

Mysql 文件

<?php
// 简易连接器
namespace Weicot;
class MySql
{
    static $con;
    public static $sname ;
    public static $uname ;
    public static $pword ;
    public static $dbname;

    static public function setDb($db){

        self::$dbname=$db;
    }

    /**
     * @throws \Exception
     */
    static public function ini(){
        self::$sname="";  //host 地址
        self::$uname="";   //用户名
        self::$pword="";   //密码
        self::$con = @mysql_connect(self::$sname, self::$uname, self::$pword);
        if (!self::$con) {
            throw new \Exception("数据库连接错误" . mysql_error());
        }

    }

    /***
     * @param $sql
     * @return resource
     * @throws \Exception
     * 执行sql
     */
    static  public function exe($sql)
    {
        self::ini();
        mysql_select_db(self::$dbname,self::$con);
        mysql_query("SET NAMES UTF8");//设置 客户端查询编码
		mysql_query("set global max_allowed_packet = 2*1024*1024*10");//设置 Pake 大小
        $out = mysql_query($sql, self::$con);
        if (!$out) {
            throw new \Exception("查询错误" . mysql_error());
        }
        return $out;
    }

    /***
     * @param $sql
     * @return array
     * @throws \Exception
     * 获得mysql 查询数组
     */
    static  public function data($sql)
    {
        $sql =self::exe($sql);
        $rows=mysql_num_rows($sql); //获得行数
        $rwr = array();
        $rwr['rows'] = $rows;
        while ($row = mysql_fetch_array($sql)) {
            $rwr['data'][] = $row;
        }
        return $rwr;
    }

    public function __destruct()
    {
        mysql_close(self::$con);
    }
}

完整代码

<?php
/**
 * WeiCot Framework
 * User: jiang
 * Date: 2015/5/15
 * Time: 17:16
 * 数据库操作基类
 */

namespace Weicot\Core;

class Database
{
    static $con;
    public static $sname;
    public static $uname;
    public static $pword;
    public static $dbname;
    public static $showQuery;

    static public function ini()
    {
        $config = File::getConfig();
        self::$sname = $config["DbHost"];
        self::$uname = $config["DbUserName"];
        self::$pword = $config["DbPassword"];
        self::$dbname = $config["DbName"];

        self::$con = @mysql_connect(self::$sname, self::$uname, self::$pword);
        if (!self::$con) {
            throw new \Exception("数据库连接错误" . mysql_error());
        }

    }

    static public function log($value, $file = "sys.log")
    {
        $basePath = "./var/log/";
        if (is_array($value) && count($value)) {
            file_put_contents($basePath . $file, date('H-i-s') . "=>" . var_export($value, TRUE) . "\r\n", FILE_APPEND);
        } else {
            file_put_contents($basePath . $file, date('H-i-s') . "=>" . $value . "\r\n", FILE_APPEND);
        }
    }

    static public function exe($sql)
    {
        self:: log($sql, "query.log");
        self::ini();
        mysql_select_db(self::$dbname, self::$con);
        mysql_query("SET NAMES UTF8");//设置 客户端查询编码
        $out = mysql_query($sql, self::$con);
        if (!$out) {
            throw new \Exception("查询错误" . mysql_error());
        }
        return $out;
    }

    /**
     * @param $sql
     * @param bool|false $row
     * @return array|int
     * @throws \Exception
     * 获得数据
     */
    static public function data($sql, $row = false)
    {
        $sql = self::exe($sql);
        //返回行数
        if ($row) {
            return mysql_num_rows($sql); //获得行数
        }

        $rows = mysql_num_rows($sql); //获得行数
        $rwr = array();
        $rwr['rows'] = $rows;
        while ($row = mysql_fetch_array($sql)) {
            $rwr['data'][] = $row;
        }
        return $rwr;
    }

    static function getDbDate()
    {
        return date('Y-m-d H:i:s', time());
    }

    static function getIp()
    {
        $ip = false;
        if (!empty($_SERVER["HTTP_CLIENT_IP"])) {
            $ip = $_SERVER["HTTP_CLIENT_IP"];
        }
        if (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
            $ips = explode(", ", $_SERVER['HTTP_X_FORWARDED_FOR']);
            if ($ip) {
                array_unshift($ips, $ip);
                $ip = FALSE;
            }
            for ($i = 0; $i < count($ips); $i++) {
                if (!eregi("^(10|172\.16|192\.168)\.", $ips[$i])) {
                    $ip = $ips[$i];
                    break;
                }
            }
        }
        return ($ip ? $ip : $_SERVER['REMOTE_ADDR']);
    }

    /***
     * @param $value 输入值 xss
     * @return mixed
     */
    static function xss($value)
    {
        return htmlspecialchars($value);

    }

    public function __destruct()
    {
        @mysql_close(self::$con);
    }

    /***
     * @param $sql
     * @param bool|false $row
     * @return array|bool|int|mixed
     * @throws \Exception
     */
    static public function cacheData($sql,$prefix="cache",$row = false)
    {
        $cache = self::isCache($sql,$prefix);
        if ($cache) {
            return $cache;
        } else {
            $resource = self::exe($sql);
            //返回行数
            if ($row) {
                return mysql_num_rows( $resource); //获得行数
            }
            $rows = mysql_num_rows( $resource); //获得行数
            $rwr = array();
            $rwr['rows'] = $rows;
            while ($row = mysql_fetch_array( $resource)) {
                $rwr['data'][] = $row;
            }
            self::cacheFile($sql, $rwr,$prefix);
            return $rwr;
        }

    }

    /***
     * @param $sql
     * @return bool|mixed
     * 判断是否缓存
     */
    static function isCache($sql,$prefix="cache")
    {
        $fileName =$prefix."_". md5($sql);
        $file =  "./var/cache/sqldata/" . $fileName;
        if (file_exists($file)) {
            $data = file_get_contents($file);
            return unserialize($data);
        } else {
            return false;
        }

    }

    /**
     * 查询缓存
     * @param $sql
     * @param $data
     */
    static function cacheFile($sql,$data,$prefix="cache")
    {

        $fileName =$prefix."_". md5($sql);
        $file = "./var/cache/sqldata/" . $fileName;
        $msg = serialize($data);
        $fp = fopen($file, "w");
        fputs($fp, $msg);
        fclose($fp);
    }

    /***
     * @param $ostr
     * @return string
     */
    public function filter_utf8_char($ostr)
    {
        preg_match_all('/[\x{FF00}-\x{FFEF}|\x{0000}-\x{00ff}|\x{4e00}-\x{9fff}]+/u', $ostr, $matches);
        $str = join('', $matches[0]);
        if ($str == '') {   //含有特殊字符需要逐個處理
            $returnstr = '';
            $i = 0;
            $str_length = strlen($ostr);
            while ($i <= $str_length) {
                $temp_str = substr($ostr, $i, 1);
                $ascnum = Ord($temp_str);
                if ($ascnum >= 224) {
                    $returnstr = $returnstr . substr($ostr, $i, 3);
                    $i = $i + 3;
                } elseif ($ascnum >= 192) {
                    $returnstr = $returnstr . substr($ostr, $i, 2);
                    $i = $i + 2;
                } elseif ($ascnum >= 65 && $ascnum <= 90) {
                    $returnstr = $returnstr . substr($ostr, $i, 1);
                    $i = $i + 1;
                } elseif ($ascnum >= 128 && $ascnum <= 191) { // 特殊字符
                    $i = $i + 1;
                } else {
                    $returnstr = $returnstr . substr($ostr, $i, 1);
                    $i = $i + 1;
                }
            }
            $str = $returnstr;
            preg_match_all('/[\x{FF00}-\x{FFEF}|\x{0000}-\x{00ff}|\x{4e00}-\x{9fff}]+/u', $str, $matches);
            $str = join('', $matches[0]);
        }
        return $str;
    }

    /***
     * @param $string
     * @return string
     * 转意
     */
    public function string($string)
    {
        return addslashes($string);
    }

}

?>

删除垃圾文章

#delete from ... using ... where ....。
delete
from
  wp_posts
using
    wp_posts,
    wp_term_relationships,
    wp_term_taxonomy
where
  wp_posts.id=wp_term_relationships.object_id
  and
  wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  and
  wp_term_relationships.term_taxonomy_id = 11

wp_term_relationships 需要删除对应的文章分类关系,不然记录条数和原来的wp_posts 一样,
查询:
SELECT * FROM `wp_term_relationships` where term_taxonomy_id=11
删除:
delete FROM `wp_term_relationships` where term_taxonomy_id=11
记得优化下表

把wp_term_taxonomy里的分类文章数量计数的几W count 给归零
UPDATE `wp_term_taxonomy` SET `count` = '0' WHERE `wp_term_taxonomy`.`term_taxonomy_id` =11 LIMIT 1 ;

转载请注明:(●--●) Hello.My Weicot » wordpress 大批量删除无用评论 以及垃圾评论统计 和文章 php 代码