Sai Sai - 5 months ago 11
MySQL Question

Can you use php with an SQL case?

I am trying to optimize some of my code and i believe i need an if/else or case to do this, however I think i would need php in the query to get it to work

here is the code I am trying to optimize

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


and this is what I have tried to give you a rough idea of what I am trying to achieve

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

Answer

You can build up an array of filters depending on which values (year, month, etc) are set, and then combine them all into the WHERE clause. You don't need to worry about all the separate cases where both are set, or one are set, and so on.

I would also strongly echo the advice above that recommended looking into prepared statements, but this will hopefully get you on your way.

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

The implode line combines each filter that's been set into a single WHERE clause.