php链接Mysql排查问题代码

wdcp后台被窝改了一个参数,安全模式开启了,开启后mysql奇怪的打不开了,而在wdcp中的phpmyadmin和wdcp都可以链接上mysql,3306端口开着的,mysql开启了,最终想了下修改了一个参数,然后改回来了就好了。
php链接mysql排错代码如下:

<?php
$conn = mysql_connect("localhost","username","password") or die("connect err: ".mysql_error());
echo "MySQL数据库成功!";
?>

Mysql 数据统计学习积累

一、关键词展现统计
1.1 本周每日展现与上周每日展现 折线图
1.2 本周车系 展现量TOP10 柱状图
1.3 关键词覆盖-展现分析,新增加的关键词展现数据与之前展现数据对比 柱状图
 
二、关键词点击统计?
2.1 本周每日点击与上周每日点击 折线图
2.2 本周车系 点击量TOP10 柱状图
2.3 本周每日点击率与上周每日点击率 折线图
 
三、关键词转化统计
3.1 本周每日申请试驾报名数量 柱状图
3.2 本周车系申请试驾排名 柱状图
 
四、数据总结
4.1 百度指数TOP50总占比,TOP50关键词各占比
4.2 百度指数本周与上周变化较大的几个车系关键词 (百度指数差-排序) ?百度指数差,上升和下降的前10名
4.3 同行竞品分析-分析与同行厂商,车系的变化细节
 
五、小结
上周与本周的平均展现,点击,点击率,转化率对比上升或者下降幅度,每月,季度等数据变化。
 
以下是涉及数据库相关操作语句:
关键词规则
1.带空格,去掉空格
2.带括号(),去掉括号内所有内容,比如(进口)、(概念车)、(海外)
3.带? 去掉 ? 符号
4. 大小写转换;
5.去掉“浙江”
6. +*厢
7.特殊符号 “ α”
整理常用SQL语句

A、正则批量替换

UPDATE `think_key` SET `otherkey` = replace(keyname,’α’,”) WHERE `keyname` LIKE ‘%浙江%’
UPDATE `think_key` SET `otherkey` = replace(keyname,’两厢’,”) WHERE `keyname` LIKE ‘%两厢%’
UPDATE `think_key` SET `otherkey` = replace(keyname,’三厢’,”) WHERE `keyname` LIKE ‘%三厢%’
UPDATE `think_key` SET `otherkey` = replace(keyname,’α’,”) WHERE `keyname` LIKE ‘%α%’
B、根据条件修改记录

//关键词与副关键词相等的记录(keyname=otherkey)则设置keyzhishu,oldkeyzhishu,solu,otherkey为0,即清空
操作。
create table tmp as select keyname from think_key where 1;
update `think_key` set keyzhishu = ‘0’ , oldkeyzhishu = ‘0’ , solu = ‘0’ , otherkey = ” WHERE otherkey
in (select keyname from tmp where 1);
drop table tmp;
C、查看展现量与点击量SQL

//==查看每日展现与点击量sql
SELECT sum(click) FROM `cms_360_api_data` WHERE `days` = ‘2014-06-30’ ORDER BY `searchvol` DESC

//查看一周展现与点击
SELECT sum(searchvol) FROM `cms_360_api_data` WHERE `days` >= ‘2014-07-04’ and `days` <= ‘2014-07-10’

//车系收录量最高的,一共65个。
SELECT * FROM `think_key` where keyname in (SELECT keychexi FROM `think_access`) and solu = ‘100000000’
ORDER BY `keyzhishu` DESC
//每周车系前10名按展现和点击分组排序。。。。
SELECT keyword,sum(searchvol),sum(click) FROM `cms_360_api_data` WHERE keyword in (SELECT keychexi FROM
`think_access`) and days >=’2014-6-30′ and days<=’2014-7-6′ group by keyword ORDER BY sum(searchvol)
DESC limit 10

1.每周车系展现排行榜
SELECT keyword,sum(searchvol) FROM `cms_360_api_data` WHERE keyword in (SELECT keychexi FROM
`think_access`) and days >=’2014-6-30′
and days<=’2014-7-6′ group by keyword ORDER BY sum(searchvol) DESC limit 10
2.每周车系点击排行榜
SELECT keyword,sum(click) FROM `cms_360_api_data` WHERE keyword in (SELECT keychexi FROM `think_access`)
and days >=’2014-6-30′ and days<=’2014-7-6′ group by keyword ORDER BY sum(click) DESC limit 10
D、异常数据sql

// == 点击大于检索 非正常关键词
SELECT * FROM `cms_360_api_data` WHERE `click` > `searchvol` and days = ‘2014-07-06’ ORDER BY
`searchvol` DESC
E、百度指数sql

//查询车系关键词 百度指数
SELECT * FROM `think_key` WHERE keyname in (SELECT keychexi FROM `think_access` WHERE 1) ORDER BY
`keyzhishu` DESC

//查询车系中otherkey不为空的记录
SELECT * FROM `think_key` where otherkey != “” ORDER BY `keyzhishu` DESC
SELECT * FROM `think_key` WHERE keyname in (SELECT keychexi FROM `think_access`) and otherkey != “”
ORDER BY `keyzhishu` DESC

//Top50
select sum(keyzhishu) from (SELECT keyzhishu FROM `think_key` WHERE keyname in (SELECT keychexi FROM
`think_access`) ORDER BY `think_key`.`keyzhishu` DESC LIMIT 0,50) as t1

//Top50关键词各占百分比
SELECT keyname,otherkey,keyzhishu/606936 FROM `think_key` WHERE keyname in (SELECT keychexi FROM
`think_access`) ORDER BY `think_key`.`keyzhishu` DESC LIMIT 0,50
F、试驾报名

//统计每日试驾报名

//统计车系试驾报名排行榜 — 返回指定日期 预约试驾报名 前 15个车系。
select chexi , count(*) AS count from think_baoming where data between ‘2014-07-04’ and ‘2014-07-11’
group by chexi order by count DESC limit 15

 

G、数据总结

//小结 统计周每日 检索 或展现 周平均值。
SELECT sum(searchvol)/7 FROM `cms_360_api_data` WHERE `days` >= ‘2014-06-26’ and `days` <= ‘2014-07-03’

 

I、其他.
//==查询主关键词和次关键词总和 排序。。。
SELECT k.p_id , k.keywords , c.keyword , sum(c.searchvol) , sum(c.click) , c.days FROM
`cms_360_api_data` as c LEFT JOIN keywords as k ON c.keyword = k.keywords where c.days=’2014-07-06′
group by k.p_id ORDER BY sum(c.searchvol) DESC
//一周数据
SELECT k.p_id , k.keywords , c.keyword , sum(c.searchvol) , sum(c.click) , c.days FROM
`cms_360_api_data` as c LEFT JOIN keywords as k ON c.keyword = k.keywords where c.days>=’2014-6-30′ and
c.days<=’2014-7-6′ group by k.p_id ORDER BY sum(c.searchvol) DESC
//点击比动较大的上周与本周 点击差值。
select c1.keyword , c2.click – c1.click from (SELECT keyword , click FROM `cms_360_api_data` WHERE days
= ‘2014-07-07’) as c1 left join (SELECT keyword , click FROM `cms_360_api_data` WHERE days = ‘2014-07-
14’) as c2 on c1.keyword = c2.keyword ORDER BY `c2.click – c1.click` DESC
—-
select c1.keyword , c1.click as click1 , c2.click as click2 ,c2.click – c1.click as click3 from (SELECT
keyword , click FROM `cms_360_api_data` WHERE days = ‘2014-07-07’) as c1 left join (SELECT keyword ,
click FROM `cms_360_api_data` WHERE days = ‘2014-07-14′) as c2 on c1.keyword = c2.keyword ORDER BY
`click3` DESC

//百度指数添加历史记录
INSERT INTO think_tongji (id,data,keyname,otherkey,keyzhishu,solu) SELECT null,’2014-08-
08′,keyname,otherkey,keyzhishu,solu FROM think_key;

//查询360关键词指数排名TOP50
select sum(k.week_index) as data from (SELECT week_index FROM think_360key WHERE keyname in (SELECT
keychexi FROM think_access) and date='{$benzhou}’ ORDER BY week_index DESC LIMIT 50) as k
//计算360关键词指数TOP50各占百分比
SELECT keyname,round(week_index/’197354’*100,0) as bfb FROM think_360key WHERE keyname in (SELECT
keychexi FROM think_access) and date=’2014-08-07′ ORDER BY week_index DESC LIMIT 50
//对于value-sql
SELECT keyname,round(week_index/'{$count}’*100,0) as bfb FROM `think_360key` WHERE keyname in (SELECT
keychexi FROM `think_access`) and date='{$benzhou}’ ORDER BY week_index DESC LIMIT 50
 
===========================搜索引擎品牌专区栏目数据汇总==========================