php - 可以在SQL情况下使用php吗？

我正在尝试优化一些代码，我相信我需要if / else或case来执行此操作，但是我认为我需要在查询中使用php才能使其正常工作

这是我正在尝试优化的代码

    $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; ";

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 . "
    AND year = " . $_SESSION['filteryear'] . " AND month = " . $_SESSION['filtermonth'] . "
    GROUP BY age_id; ";
} else 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 . "
    AND (year = " . $_SESSION['filteryear'] . " OR month = " . $_SESSION['filtermonth'] . ")
    GROUP BY age_id; ";
}

这就是我试图让您大致了解我想要实现的目标

$filter = "";

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

$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 . "
CASE
    WHEN ".isset($filter)." THEN ". $filter ."
END
GROUP BY age_id; ";

最佳答案

您可以根据设置的值（年，月等）构建一个过滤器数组，然后将它们全部合并到WHERE子句中。您无需担心设置了两个或设置了一个等等的所有单独情况。

我也强烈建议您参考上述建议，仔细研究准备好的陈述，但这希望能助您一臂之力。

<?php
$office_id = 10;
$_SESSION['filteryear'] = 2016;
$_SESSION['filtermonth'] = 12;

$filters = [
    "value IS NOT NULL",
    "office_id = {$office_id}",
];

if ($_SESSION['filteryear']) {
    $filters[] = "year = {$_SESSION['filteryear']}";
}

if ($_SESSION['filtermonth']) {
    $filters[] = "month = {$_SESSION['filtermonth']}";
}


$sql = "
    SELECT value, COUNT(*) AS count
    FROM sodsurvey
    LEFT JOIN age ON sodsurvey.age_id = age.id
    WHERE " . implode(' AND ', $filters) . "
    GROUP BY age_id;
";

implode行将已设置的每个过滤器组合到单个WHERE子句中。

关于php - 可以在SQL情况下使用php吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37856191/