Search code examples
phpmysqlamcharts

Select count union based on current session

I created a pie chart based on my table then group them by 4 parts Escalate, Undet, Supported, Not Supported using this query:

$query = "select 'Undet' as trendx , COUNT(*) as counter 
 from jeremy_table_trend  WHERE trendx LIKE '%Undet%' 
 union all 
 select 'Escalate', COUNT(*) as counter 
 from jeremy_table_trend WHERE trendx LIKE '%Escalate%' 
 union all 
 select 'Not Supported', COUNT(*) as counter 
 from jeremy_table_trend WHERE trendx LIKE '%Not Supported%' 
 union all 
 select 'Supported', COUNT(*) as counter 
 from jeremy_table_trend 
 WHERE trendx NOT LIKE '%Not Supported%' OR '%Undet%' OR '%Escalate%'";

This outputs

enter image description here

But I want my pie chart output my current session when I filter it, I tried this query but it doesn't show the right output according to my session:

  $query = "select 'Undet' as trendx , COUNT(*) as counter from jeremy_table_trend $_SESSION[current_query]  WHERE trendx LIKE '%Undet%' union all select 'Escalate', COUNT(*) as counter from jeremy_table_trend WHERE trendx LIKE '%Escalate%' union all select 'Not Supported', COUNT(*) as counter from jeremy_table_trend WHERE trendx LIKE '%Not Supported%' union all select 'Supported', COUNT(*) as counter from jeremy_table_trend WHERE trendx NOT LIKE '%Not Supported%' OR '%Undet%' OR '%Escalate%'";

Take a look at my code, this is my filter page, to output the filtered table, I want my pie chart to show the items according to my current_session: 

<?php
    $rpp = 10;
    $page = 1;
    if (isset($_GET['rpp'])) {
        $_SESSION['rpp'] = $_GET['rpp'];
    }
    if (isset($_SESSION['rpp'])) {
        $rpp = $_SESSION['rpp'];
    }
    if (isset($_GET['page'])) {
        $page = $_GET['page'];
    }
    $conn = mysqli_connect("localhost", "root", "", "jeremy_db");

    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $_SESSION['sourced_from'] = $_POST['sourced_from'];
        $_SESSION['sourced_to'] = $_POST['sourced_to'];
        $_SESSION['vsdt'] = $_POST['vsdt'];
        $_SESSION['sha1'] = $_POST['sha1'];
        $_SESSION['trendx'] = $_POST['trendx'];
        $_SESSION['notes'] = $_POST['notes'];
        $_SESSION['trendx_eq'] = $_POST['trendx_eq'];
        $_SESSION['vsdt_eq'] = $_POST['vsdt_eq'];
    }

    if ($_SESSION['sourced_from'] or $_SESSION['sourced_to'] or $_SESSION['vsdt'] or $_SESSION['sha1'] or $_SESSION['trendx'] or $_SESSION['notes']) {  
        $_SESSION['filter_query'] = "";
        $first = True;
        $and = "";
        $_SESSION['filter_query'] .= "WHERE ";
        if ($_SESSION['sourced_from'] or $_SESSION['sourced_to']) {
            if (!$first) $and = " AND ";
            else $first = False;
            $date1 = $_SESSION['sourced_from'];
            $date2 = $_SESSION['sourced_to'];
            if($_SESSION['sourced_from'] and $_SESSION['sourced_to']) $_SESSION['filter_query'] .= $and . " date_sourced BETWEEN '$date1' AND '$date2'";
            elseif($_SESSION['sourced_from']) $_SESSION['filter_query'] .= $and . " date_sourced >= '$date1'";
            elseif($_SESSION['sourced_to']) $_SESSION['filter_query'] .= $and . " date_sourced <= '$date2'";
        }
        if ($_SESSION['sha1']) {
            if (!$first) $and = " AND ";
            else $first = False;
            $_SESSION['filter_query'] .= $and . " sha1 = '" . $_SESSION['sha1'] . "'";
        }
        if ($_SESSION['vsdt']) {
            if (!$first) $and = " AND ";
            else $first = False;
            if($_SESSION['vsdt_eq'] == "eq") {
                $_SESSION['filter_query'] .= $and . " vsdt = ";
                if($_SESSION['vsdt'] == 'None')  $_SESSION['filter_query'] .= " '' ";
                else  $_SESSION['filter_query'] .= " '" . $_SESSION['vsdt'] . "' ";
            } elseif($_SESSION['vsdt_eq'] == "neq") {
                $_SESSION['filter_query'] .= $and . " vsdt != ";
                if($_SESSION['vsdt'] == 'None')  $_SESSION['filter_query'] .= " '' ";
                else  $_SESSION['filter_query'] .= " '" . $_SESSION['vsdt'] . "' ";
            } else $_SESSION['filter_query'] .= $and . " vsdt LIKE '%" . $_SESSION['vsdt'] . "%'";
        }   

        if ($_SESSION['trendx']) {
            if (!$first) $and = " AND ";
            else $first = False;
            if($_SESSION['trendx_eq'] == "eq") {
                $_SESSION['filter_query'] .= $and . " trendx = ";
                if($_SESSION['trendx'] == 'None')  $_SESSION['filter_query'] .= " '' ";
                else  $_SESSION['filter_query'] .= " '" . $_SESSION['trendx'] . "' ";
            } elseif($_SESSION['trendx_eq'] == "neq") {
                $_SESSION['filter_query'] .= $and . " trendx != ";
                if($_SESSION['trendx'] == 'None')  $_SESSION['filter_query'] .= " '' ";
                else  $_SESSION['filter_query'] .= " '" . $_SESSION['trendx'] . "' ";
            } else $_SESSION['filter_query'] .= $and . " trendx LIKE '%" . $_SESSION['trendx'] . "%'";
        }   

        if ($_SESSION['notes']) {
            if (!$first) $and = " AND ";
            else $first = False;
            $_SESSION['filter_query'] .= $and . " notes LIKE '%" . $_SESSION['notes'] . "%'";
        }
    } else {
        $_SESSION['filter_query'] = "";
        $first = True;
        $and = "";
    }
    $temp = ($page-1)*$rpp;
    $query = " SELECT * FROM jeremy_table_trend " . $_SESSION['filter_query'] . " ORDER by id desc LIMIT $temp, $rpp ";
    // echo $query;
    $page_result = mysqli_query($conn, $query);
    $total = " SELECT * FROM jeremy_table_trend " . $_SESSION['filter_query'];
    $_SESSION['current_query'] = $_SESSION['filter_query'];
    $total = mysqli_query($conn, $total);
    $total = mysqli_num_rows($total);
    if (isset($_GET['update_id'])) {
        $update_id = $_GET['update_id'];
        $update_id = " SELECT * FROM jeremy_table_trend WHERE id='$update_id' ";
        $update_id = mysqli_query($conn, $update_id);
        $update_id = mysqli_fetch_assoc($update_id);
    }
?>
Solution

  • Your current SQL query is probably incorrect, plus it can certainly be improved. I suggest this:

    SELECT
    CASE
        WHEN trendx LIKE '%Undet%' THEN 'Undet'
        WHEN trendx LIKE '%Escalate%' THEN 'Escalate'
        WHEN trendx LIKE '%Not Supported%' THEN 'Not Supported'
        ELSE 'Supported'
    END
    AS trendx
    , COUNT(*) AS counter
FROM jeremy_table_trend

WHERE ....

GROUP BY
    CASE
        WHEN trendx LIKE '%Undet%' THEN 'Undet'
        WHEN trendx LIKE '%Escalate%' THEN 'Escalate'
        WHEN trendx LIKE '%Not Supported%' THEN 'Not Supported'
        ELSE 'Supported'
    END

    As this is a single query it will be much easier to insert the required where clause.

    nb: The reason I think the current one is incorrect is that you do not negate all the conditions in the final 

    WHERE trendx NOT LIKE '%Not Supported%' OR '%Undet%' OR '%Escalate%'";

    should read:

    WHERE NOT (trendx LIKE '%Not Supported%' OR trendx LIKE '%Undet%' OR trendx LIKE '%Escalate%')

    but it will be more efficient to use a case expression as shown above.

    btw it isn't necessary to write sql queries as single lines in PHP, it will help readability if you start using formatted blocks of SQL.