加载配置文件
<?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 代码