您好，我在我的 SQL 查询中遇到了一个错误，无法弄清楚是什么问题。这是迄今为止在 Barmar 的帮助下的查询。
| 1 2 3 4 5 6 7 8 9 10 11 12 |
$query = "SELECT c.*, count(s.curso_id) as count, SUM(IF(s.status = 'aprobado', 1, 0)) AS count_approved , SUM(IF(s.status = 'cupolleno', 1, 0)) AS count_cupolleno , SUM(IF(s.status = 'cancelado', 1, 0)) AS count_cancelado, SUM(IF(s.status = 'noacion', 1, 0)) AS count_noacion, SUM(IF(s.status = 'ama_de_casa', 1, 0)) AS count_ama_de_casa , SUM(IF(s.status = 'cliente_externo', 1, 0)) AS count_cliente_externo FROM cursos_modulos AS c LEFT JOIN subscriptions AS s ON s.curso_id = c.id LEFT JOIN users AS u ON u.userID = s.user_id GROUP BY c.id WHERE 1" ; if ( ! empty ( $id ) ) { $query .= " AND c.id = '$id'" ; } if ( ! empty ( $ciudad ) ) { $query .= " AND c.ciudad = '$ciudad'" ; } if ( ! empty ( $tipo ) ) { $query .= " AND c.tipo = '$tipo'" ; } if ( ! empty ( $titulo ) ) { $query .= " AND c.titulo = '$titulo'" ; } if ( ! empty ( $status ) ) { $query .= " AND c.status = '$status'" ; } $paginate = new pagination ( $page , $query , $options ) ; |
我得到的错误信息如下：
Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'WHERE 1 AND
c.id = '1' LIMIT 0, 30' at line 6' in
E:\\xampp\\htdocs\\admin\\class\\pagination.php:376 Stack trace: #0
E:\\xampp\\htdocs\\admin\\class\\pagination.php(376):
PDOStatement->execute() #1
E:\\xampp\\htdocs\\admin\\class\\pagination.php(202):
pagination->excecute_query() #2
E:\\xampp\\htdocs\\admin\\class\\pagination.php(162):
pagination->run(1, 'SELECT c., cou...', Array) #3
E:\\xampp\\htdocs\\admin\\search.php(146):
pagination->__construct(1, 'SELECT c., cou...', Array) #4 {main}
thrown in E:\\xampp\\htdocs\\admin\\class\\pagination.php on line
376
group by 子句应该在 where 子句之后。即：
| 1 2 3 4 5 6 7 8 9 10 11 12 |
$query = "SELECT c.*, count(s.curso_id) as count, SUM(IF(s.status = 'aprobado', 1, 0)) AS count_approved , SUM(IF(s.status = 'cupolleno', 1, 0)) AS count_cupolleno , SUM(IF(s.status = 'cancelado', 1, 0)) AS count_cancelado, SUM(IF(s.status = 'noacion', 1, 0)) AS count_noacion, SUM(IF(s.status = 'ama_de_casa', 1, 0)) AS count_ama_de_casa , SUM(IF(s.status = 'cliente_externo', 1, 0)) AS count_cliente_externo FROM cursos_modulos AS c LEFT JOIN subscriptions AS s ON s.curso_id = c.id LEFT JOIN users AS u ON u.userID = s.user_id WHERE 1" ; if ( ! empty ( $id ) ) { $query .= " AND c.id = '$id'" ; } if ( ! empty ( $ciudad ) ) { $query .= " AND c.ciudad = '$ciudad'" ; } if ( ! empty ( $tipo ) ) { $query .= " AND c.tipo = '$tipo'" ; } if ( ! empty ( $titulo ) ) { $query .= " AND c.titulo = '$titulo'" ; } if ( ! empty ( $status ) ) { $query .= " AND c.status = '$status'" ; } $query .= " GROUP BY c.id" ; |
- 这看起来是正确的解决方案，唯一的问题是它给了我一个错误；解析错误：语法错误，第 147 行 E:\\\\xampp\\\\htdocs\\\\admin\\\\search.php 中的意外 '$paginate' (T_VARIABLE)
- 我在最后一个语句的末尾缺少一个 ; 。添加它应该可以解决问题。
where 1 能为你做什么？尝试杀死它。
以下不会引发 1064 错误：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
create table cursos_modulos ( id int not null ) ; create table subscriptions create table users SELECT c .id , |
- 它使 if 条件在最后的查询中被考虑。
- 1 是 mysql 中的 true 。 where 1 是完全合法的(尽管有些无用)mysql 语法。
- 我主要根据您的 group by 将其更改为 c.id 。如果您与该 group by 有 c.* ，祝您好运，它可能会提供错误的答案，或者通常会。应该不惜一切代价避免使用 Table.*，除非它非常接近被丢弃的代码。
本文由 华域联盟 原创撰写：华域联盟 » 关于 php:SQLSTATE[42000] 聚合函数错误
转载请保留出处和原文链接：https://www.cnhackhy.com/158224.htm
