时间戳

首页 > 解决方案 > 在使用 php 的 sql 查询中使用 case 函数时出错

php - 在使用 php 的 sql 查询中使用 case 函数时出错

问题描述

我正在尝试显示用户 ID、用户名和他们每年购买的订阅数量。我的数据库包含两个表，即订阅和用户，如下所示：

用户

用户身份 姓名 电话号码
1 xyz 4567890
2 美国广播公司 4578003

订阅

subc_id 用户身份 类型 开始时间
1 2 星期 2019-07-30 17::00:19
2 4 年 2020-05-12 22:38:24

我在我的 php 代码中使用的 SQL 查询是

SELECT subscriptions.user_id, users.name, COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2018" AND subscriptions.type = "year" THEN 1 END) AS "2018",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2019" AND subscriptions.type = "year" THEN 1 END) AS "2019",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2020" AND subscriptions.type = "year" THEN 1 END) AS "2020",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2021" AND subscriptions.type = "year" THEN 1 END) AS "2021" FROM subscriptions, users WHERE subscriptions.user_id = users.user_id

问题是它在我的 phpmyadmin SQL 查询界面中运行良好，但是当我将它与 php mysqli 一起使用时，它给了我以下错误 Parse error: syntax error, unexpected '2018' (T_LNUMBER)

请告诉我我的代码有什么问题。我的php代码是

<?php
        $sql = "SELECT subscriptions.user_id, users.name, COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2018" AND subscriptions.type = "year" THEN 1 END) AS "2018",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2019" AND subscriptions.type = "year" THEN 1 END) AS "2019",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2020" AND subscriptions.type = "year" THEN 1 END) AS "2020",COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2021" AND subscriptions.type = "year" THEN 1 END) AS "2021" FROM subscriptions, users WHERE subscriptions.user_id = users.user_id";
        if ($res = mysqli_query($conn, $sql)) {
            if (mysqli_num_rows($res) > 0) {
                echo "<table>";
                echo "<tr>";
                echo "<th>user_id</th>";
                echo "<th>Name</th>";
                echo "<th>2018</th>";
                echo "<th>2019</th>";
                echo "<th>2020</th>";
                echo "<th>2021</th>";
                echo "<tr>";
                while($row = mysqli_fetch_array($res))
                {
                    echo "<tr>";
                    echo "<td>" . $row['user_id'] . "</td>";
                    echo "<td>" . $row['name'] . "</td>";
                    echo "<td>" . $row['2018'] . "</td>";
                    echo "<td>" . $row['2019'] . "</td>";
                    echo "<td>" . $row['2020'] . "</td>";
                    echo "<td>" . $row['2021'] . "</td>";
                    echo "</tr>";
                }
                echo "<table>";
            }
            else {
                echo "No Records";
            }
        }
        ?>

标签： phpmysqlsql

解决方案

我不相信您的查询在 phpmyadmin 中也不起作用，尽管它在语法上是正确的，因为它缺少一个GROUP BY子句。

另外，您没有正确使用".
在一行中，您是否使用双引号将字符串值分配给如下变量：

$sql = "SELECT subscriptions.user_id, users.name, COUNT(CASE WHEN YEAR(subscriptions.start_time) = "2018" ...........";

在内部再次使用双引号将字符串值和列名等标识符括起来是错误的。
对于字符串值使用单引号，对于标识符使用反引号。

此外，使用适当JOIN的 s 和ON子句，而不是过时的逗号语法。

最后添加GROUP BY子句：

SELECT u.user_id, users.name, 
       COUNT(CASE WHEN YEAR(s.start_time) = '2018' AND s.type = 'year' THEN 1 END) AS `2018`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2019' AND s.type = 'year' THEN 1 END) AS `2019`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2020' AND s.type = 'year' THEN 1 END) AS `2020`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2021' AND s.type = 'year' THEN 1 END) AS `2021` 
FROM subscriptions s INNER JOIN users u 
ON s.user_id = u.user_id
GROUP BY u.user_id, users.name

您还可以使用 WHERE 子句，这会更好地提高性能并缩短代码，因为您只计算具有以下内容的行type = 'year'：

SELECT u.user_id, users.name, 
       COUNT(CASE WHEN YEAR(s.start_time) = '2018' THEN 1 END) AS `2018`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2019' THEN 1 END) AS `2019`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2020' THEN 1 END) AS `2020`,
       COUNT(CASE WHEN YEAR(s.start_time) = '2021' THEN 1 END) AS `2021` 
FROM subscriptions s INNER JOIN users u 
ON s.user_id = u.user_id
WHERE s.type = 'year'
GROUP BY u.user_id, users.name

如果您利用 MySql 对布尔表达式的评估为 1 fortrue和 0 for false，还有另一种编写此查询的方法，SUM()而不是COUNT()：

SELECT u.user_id, users.name, 
       SUM(YEAR(s.start_time) = '2018') AS `2018`,
       SUM(YEAR(s.start_time) = '2019') AS `2019`,
       SUM(YEAR(s.start_time) = '2020') AS `2020`,
       SUM(YEAR(s.start_time) = '2021') AS `2021` 
FROM subscriptions s INNER JOIN users u 
ON s.user_id = u.user_id
WHERE s.type = 'year'
GROUP BY u.user_id, users.name

推荐阅读