PHP站点及mysql常用操作

1.mysql查询表里面重复数据

SELECT * FROM 表名xxxx a WHERE ((SELECT COUNT(*) FROM 表名xxxx WHERE title = a.title) > 1) ORDER BY title DESC //查询标题重复文章

SELECT * FROM 表名xxxx a WHERE ((SELECT COUNT(*) FROM 表名xxxx WHERE title = a.title) > 1) and channel_id=31 ORDER BY title DESC //可增加查询条件

 *以上表名XXX为同一个表

2.group和count统计发布内容数量

SELECT DATE_FORMAT(estime,'%Y') years,COUNT(id) COUNT FROM articles GROUP BY years
*estime=2022-2-12

SELECT FROM_UNIXTIME(estime,'%Y%m%d') years,COUNT(id) COUNT FROM articles  GROUP BY years
*estime=116545151

上述两个查询需区分表中时间格式

group by多个条件查询（每个栏目每年发的文章总数）
SELECT FROM_UNIXTIME(estime,'%Y') years,COUNT(id) COUNT,colId FROM articles GROUP BY years,colId

*estime=116545151

每个栏目每年发的文章总数（多表联查查出各个栏目名称及发布文章数量）
SELECT FROM_UNIXTIME(articles.estime,'%Y') years,COUNT(articles.id) COUNT,articles_column.name as name FROM articles,articles_column where articles.colId=articles_column.id GROUP BY years,articles.colId

*estime=116545151

其余查询可根据上述进行灵活变动

 3.原生php插入数据

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<?php
//连接数据库
$conn=mysqli_connect('localhost','forsafe','FT6BSSBHXBhJBxNd','forsafe','3306');
$cip=$_GET["cip"];
$curl=$_GET["curl"];
date_default_timezone_set('prc');
$ctime=date('y-m-d h:i:s',time());
$sql = "insert into safes(cip,curl,ctime) values('$cip','$curl','$ctime')";
// mysqli_select_db($conn,'forsafe');
mysqli_set_charset($conn,'utf8');
$insert=mysqli_query($conn,$sql);
if($insert){
echo '成功插入数据';
}
else{
echo '插入数据失败';
}
?>

4.PHP获取当前时间：

date_default_timezone_set('prc');
echo date(‘y-m-d h:i:s’,time());

5.php获取完整域名地址

#测试网址: http://www.test.cn/first/index.php?id=23
//获取域名或主机地址
echo $_SERVER['HTTP_HOST']."<br>"; #www.test.cn
//获取网页地址
echo $_SERVER['PHP_SELF']."<br>"; #/first/index.php
//获取网址参数
echo $_SERVER["QUERY_STRING"]."<br>"; #id=23
//获取用户代理
echo $_SERVER['HTTP_REFERER']."<br>";
//获取完整的url
echo 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
echo 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING'];
#http://www.test.cn/first/index.php?id=23
//包含端口号的完整url
echo 'http://'.$_SERVER['SERVER_NAME'].':'.$_SERVER["SERVER_PORT"].$_SERVER["REQUEST_URI"];
#http://www.test.cn:80/first/index.php?id=23
//只取路径
$url='http://'.$_SERVER['SERVER_NAME'].$_SERVER["REQUEST_URI"];
echo dirname($url);
#http://www.test.cn/first

6.PHP和JS判断是否为移动端/PC端

js判断
function isPc(){
if(window.navigator.userAgent.match(/(phone|pad|pod|iPhone|iPod|ios|iPad|Android|Mobile|BlackBerry|IEMobile|MQQBrowser|JUC|Fennec|wOSBrowser|BrowserNG|WebOS|Symbian|Windows Phone)/i)) {
return true; // 移动端
}else{
return false; // PC端
}
}
PHP判断
function is_mobile_request(){
$_SERVER['ALL_HTTP'] = isset($_SERVER['ALL_HTTP']) ? $_SERVER['ALL_HTTP'] : '';
$mobile_browser = '0';
if(preg_match('/(up.browser|up.link|mmp|symbian|smartphone|midp|wap|phone|iphone|ipad|ipod|android|xoom)/i', strtolower($_SERVER['HTTP_USER_AGENT'])))
$mobile_browser++;
if((isset($_SERVER['HTTP_ACCEPT'])) and (strpos(strtolower($_SERVER['HTTP_ACCEPT']),'application/vnd.wap.xhtml+xml') !== false))
$mobile_browser++;
if(isset($_SERVER['HTTP_X_WAP_PROFILE']))
$mobile_browser++;
if(isset($_SERVER['HTTP_PROFILE']))
$mobile_browser++;
$mobile_ua = strtolower(substr($_SERVER['HTTP_USER_AGENT'],0,4));
$mobile_agents = array(
'w3c ','acs-','alav','alca','amoi','audi','avan','benq','bird','blac',
'blaz','brew','cell','cldc','cmd-','dang','doco','eric','hipt','inno',
'ipaq','java','jigs','kddi','keji','leno','lg-c','lg-d','lg-g','lge-',
'maui','maxo','midp','mits','mmef','mobi','mot-','moto','mwbp','nec-',
'newt','noki','oper','palm','pana','pant','phil','play','port','prox',
'qwap','sage','sams','sany','sch-','sec-','send','seri','sgh-','shar',
'sie-','siem','smal','smar','sony','sph-','symb','t-mo','teli','tim-',
'tosh','tsm-','upg1','upsi','vk-v','voda','wap-','wapa','wapi','wapp',
'wapr','webc','winw','winw','xda','xda-'
);
if(in_array($mobile_ua, $mobile_agents))
$mobile_browser++;
if(strpos(strtolower($_SERVER['ALL_HTTP']), 'operamini') !== false)
$mobile_browser++;
// Pre-final check to reset everything if the user is on Windows
if(strpos(strtolower($_SERVER['HTTP_USER_AGENT']), 'windows') !== false)
$mobile_browser=0;
// But WP7 is also Windows, with a slightly different characteristic
if(strpos(strtolower($_SERVER['HTTP_USER_AGENT']), 'windows phone') !== false)
$mobile_browser++;
if($mobile_browser>0)
return true;
else
return false;
}

7.PHP原生执行sql语句

$m=M();

$sql="xxx...";

$m->query($sql);query用于select查询语句

$m->execute($sql);用于添加，删除，更新的sql语句的操作；

8.fastadmin 后台访问域名报你所浏览的页面暂时无法访问 解决

找到application/extra/site.php
中最后加一行代码
'version'=>'1.1.1',
版本号的原因。

百度ueditor编辑器无法上传截图图片和远程图片：
ueditor.config.js文件里修改如下属性：
pasteplain:false, 、、是否纯文本粘贴
catchRemoteImageEnable: false, 、、是否抓取远程图片

8.css设置背景渐变颜色

/* 从上到下，蓝色渐变到红色 */
linear-gradient(blue, red);

/* 渐变轴为45度，从蓝色渐变到红色 */
linear-gradient(45deg, blue, red);

/* 从右下到左上、从蓝色渐变到红色 */
linear-gradient(to left top, blue, red);

/* 从下到上，从蓝色开始渐变、到高度40%位置是绿色渐变开始、最后以红色结束 */
linear-gradient(0deg, blue, green 40%, red);

background: url(/static/admin/images/bg.png), linear-gradient(to left, #34a853, #4285f4);

9.MySQL替换指定字段字符串语句

update 表名 set 字段名=replace(字段名,'原来的内容','替换后的内容')
update article set title=replace(title,'你好','hello')