php - 如何使用 session 通过 SQL 查询过滤数据

Question

我有一个数据库，用于使用 D3.js 创建饼图。我制作了一个过滤器，使用表单按年和月对数据进行排序，当您提交时，会根据下拉选项创建一个 session 。我正在查询中使用 session 。

为了更好地理解，我附上了一张图片，目前我的查询正在对整体数据进行排序，但不是独立排序。例如，我想按 2016 年或一月份的数据进行排序。

我一直在玩它，但我似乎无法做到正确，它们应该两者彼此独立和一起工作，我只得到了至于或其他。这是我的代码

// this is used to reset the graphs back to default 
            if ($_SESSION['filteryear'] == 0 && $_SESSION['filtermonth'] == 0) {
                $sql = "SELECT value, COUNT(*) AS count
                FROM sodsurvey LEFT OUTER JOIN age
                ON sodsurvey.age_id = age.id 
                WHERE value IS NOT NULL AND office_id = " . $office_id . " 
                GROUP BY age_id; ";
            } else {
                $sql = "SELECT value, COUNT(*) AS count
                FROM sodsurvey LEFT OUTER JOIN age
                ON sodsurvey.age_id = age.id 
                WHERE value IS NOT NULL AND office_id = " . $office_id . " 
                AND month = " . $_SESSION['filtermonth'] . " 
                AND year = " . $_SESSION['filteryear'] . " 
                GROUP BY age_id; ";
            }

            $result = $dbh->prepare($sql);//prepares query  
            $result->execute();

@Alox 这是代码

$EX_SQL="";

if ($_SESSION['filteryear'] != 0 && $_SESSION['filtermonth'] != 0) {
    $EX_SQL=" AND month = " . $_SESSION['filtermonth'] . " AND year = " . $_SESSION['filteryear'] . "";
} else if($_SESSION['filteryear'] != 0) {
    $EX_SQL=" AND month = " . $_SESSION['filtermonth'] . "";
} else {
   $EX_SQL=" AND year = " . $_SESSION['filteryear'] . "";
}

        $sql = "SELECT value, COUNT(*) AS count
               FROM sodsurvey LEFT OUTER JOIN age
               ON sodsurvey.age_id = age.id 
               WHERE value IS NOT NULL AND office_id = " . $office_id . "
               ".$EX_SQL."
                GROUP BY age_id; ";

            $result = $dbh->prepare($sql);//prepares query  
            $result->execute();

Answer 1

会有 3 种情况。

1. 您有年份和月份。
2. 您可以选择月份或年份。
3. 您没有这些。

您当前的PHP代码仅支持2种情况。 (第一和第三)您应该添加另一个 else if block 。

为了更好的优化，你可以做的就是根据参数替换 SQL 字符串。像这样，

$EX_SQL="";

if ($_SESSION['filteryear'] != 0 && $_SESSION['filtermonth'] != 0) {
    $EX_SQL=" AND month = " . $_SESSION['filtermonth'] . " AND year = " . $_SESSION['filteryear'] . "";
} else if($_SESSION['filteryear'] != 0) {
    $EX_SQL=" AND month = " . $_SESSION['filtermonth'] . "";
} else {
   $EX_SQL=" AND year = " . $_SESSION['filteryear'] . "";
}

查询中的用户EX_SQL字符串如下所示:

$sql = "SELECT value, COUNT(*) AS count FROM sodsurvey LEFT OUTER JOIN age ON sodsurvey.age_id = age.id WHERE value IS NOT NULL AND office_id=".$office_id." ".$EX_SQL." GROUP BY age_id; ";

以上代码应涵盖所有 3 种情况。