php - 在没有 GROUP BY 的聚合查询中，SELECT 列表的表达式 #2 包含非聚合列 'a.b.id' ；

Question

请帮我解决这个问题。我的代码:

$dn1 = mysqli_fetch_array(mysqli_query($con, '
select count(id) as recip
     , id as recipid
     , (select count(*) from pm) as npm 
  from users 
 where username="'.$recip.'"'
 ));

它给出以下错误

Error: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'omapm.users.id'; this is incompatible with sql_mode=only_full_group_by

请帮我改正。谢谢!

Answer 1

$dn1 = mysqli_fetch_array(mysqli_query($con, 
    'select count(id) as recip, id as recipid, 
        (select count(*) from pm) as npm 
    from users 
    where username="'.$recip.'"
    group by recipid, npm'));

无论如何你不应该在 sql 命令中使用 php var .. 这样你就有 sqlinjection 的风险.. 你应该检查你的 mysql 驱动程序以准备语句和绑定(bind)参数..