这是用于从数据库
中提取数据的当前代码 $query = "SELECT username,time,UNIX_TIMESTAMP(time) as timestamp,UNIX_TIMESTAMP(active) as activestamp,IP FROM ".$table_name." WHERE success='1' ORDER BY active DESC";
下面是一组示例数据(删除了所有不重要的字段)
ID | username
_01 | admin
_02 | admin
_03 | bob
_04 | jill
我想要的是每个用户出现的总次数,例如:
admin (2)
bob (1)
jill (1)
我需要按照这个COUNT(IF(name = 'admin', 1, NULL)) AS login_totals
的思路向我向您展示的原始查询添加一些内容,但我无法找出正确的代码。我的示例中的名称管理员必须是当前用户名,所以我会在那里输入"用户名"吗?
试试这个:我添加了count(*) and the group by
$query = "SELECT username,time,
UNIX_TIMESTAMP(time) as timestamp,
UNIX_TIMESTAMP(active) as
activestamp,IP, count(*)
FROM ".$table_name." WHERE success='1'
group by username
ORDER BY active DESC";
看看这个博客:http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/
SELECT
子句中添加COUNT(username)
,并在查询末尾添加GROUP BY username
。
SELECT
username,count(username)
FROM ".$table_name." WHERE success='1'
GROUP BY username
ORDER BY active DESC";