php查询mysql数量_PHP+MYSQL 查询每月的总注册人数

$dbms='mysql'; //数据库类型

$host='localhost'; //数据库主机名

$dbName='mydata'; //使用的数据库

$user='root'; //数据库连接用户名

$pass=''; //对应的密码

$dsn="$dbms:host=$host;dbname=$dbName";

try {

$dbh = new PDO($dsn, $user, $pass); //初始化一个PDO对象

$monthcount = $dbh -> query('SELECT

SUM(CASE WHEN MONTH(user_registertime) = 1 THEN 1 else 0 END) 一月,

SUM(CASE WHEN MONTH(user_registertime) = 2 THEN 1 else 0 END) 二月,

SUM(CASE WHEN MONTH(user_registertime) = 3 THEN 1 else 0 END) 三月,

SUM(CASE WHEN MONTH(user_registertime) = 4 THEN 1 else 0 END) 四月,

SUM(CASE WHEN MONTH(user_registertime) = 5 THEN 1 else 0 END) 五月,

SUM(CASE WHEN MONTH(user_registertime) = 6 THEN 1 else 0 END) 六月,

SUM(CASE WHEN MONTH(user_registertime) = 7 THEN 1 else 0 END) 七月,

SUM(CASE WHEN MONTH(user_registertime) = 8 THEN 1 else 0 END) 八月,

SUM(CASE WHEN MONTH(user_registertime) = 9 THEN 1 else 0 END) 九月,

SUM(CASE WHEN MONTH(user_registertime) = 10 THEN 1 else 0 END) 十月,

SUM(CASE WHEN MONTH(user_registertime) = 11 THEN 1 else 0 END) 十一月,

SUM(CASE WHEN MONTH(user_registertime) = 12 THEN 1 else 0 END) 十二月,

COUNT(user_id) 合计

FROM user_users WHERE YEAR(user_registertime)=YEAR(NOW())')->fetch(PDO::FETCH_ASSOC);

foreach($monthcount as $v=>$i){

echo('月份：'.$v.'人数：'.$i);

}

$dbh = null;

} catch (PDOException $e) {

die ("Error!: " . $e->getMessage() . "
");

}