Xtykutl

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

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);

    }

?>

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.