Display apex piecharts which has non- empty datasets

General PHP Help
#1

Hello, I am fetching data from Database using sql query to display Piecharts. I am using apex chart - piecharts. For now I only want to display piecharts which has non-empty datasets. Currently below code is working fine but displays blank card for locations which has empty dataset. I only want to display non-empty piecharts. How I can do that ? where I can apply some loop to check the fetched dataset is empty or not? Please advice.

Here is my php code:

<?php
    require('functions/chain.php');
    
    $sql = file_get_contents("sql/reportPiecharts.sql");
    
    if(isset($entity)) {
        $sql.= " WHERE E.entityID = $entity";
      }
    
    if($result = query($sql)) {
          
            
            while ($row = mysqli_fetch_array($result)){
               
           
    
          $allRepo = $row['allRepo'];
          $repoRev = $row['repoRev'];
          $repoRan = $row['repoRan'];
          $repoRej = $row['repoRej'];
          $repoRFD = $row['repoRFD'];
          $repoInRev = $row['repoInRev'];
     
          $allrepoMD = $row['allrepoMD'];
          $repoRevMD = $row['repoRevMD'];
          $repoRanMD = $row['repoRanMD'];
          $repoRejMD = $row['repoRejMD'];
          $repoRFDMD = $row['repoRFDMD'];
          $repoInRevMD = $row['repoInRevMD'];
    
          $allrepoKL = $row['allrepoKL'];
          $repoRevKL = $row['repoRevKL'];
          $repoRanKL = $row['repoRanKL'];
          $repoRejKL = $row['repoRejKL'];
          $repoRFDKL = $row['repoRFDKL'];
          $repoInRevKL = $row['repoInRevKL'];
    
          $allrepoCL = $row['allrepoCL'];
          $repoRevCL = $row['repoRevCL'];
          $repoRanCL = $row['repoRanCL'];
          $repoRejCL = $row['repoRejCL'];
          $repoRFDCL = $row['repoRFDCL'];
          $repoInRevCL = $row['repoInRevCL'];
    
          $allrepoFB = $row['allrepoFB'];
          $repoRevFB = $row['repoRevFB'];
          $repoRanFB = $row['repoRanFB'];
          $repoRejFB = $row['repoRejFB'];
          $repoRFDFB = $row['repoRFDFB'];
          $repoInRevFB = $row['repoInRevFB'];
    
          $allrepoOW = $row['allrepoOW'];
          $repoRevOW = $row['repoRevOW'];
          $repoRanOW = $row['repoRanOW'];
          $repoRejOW = $row['repoRejOW'];
          $repoRFDOW = $row['repoRFDOW'];
          $repoInRevOW = $row['repoInRevOW'];
    
    
        }
     
    }
    
        $repoRem = $allRepo - ($repoRev + $repoRan + $repoRej + $repoRFD + $repoInRev);
        $repoRemMD = $allrepoMD - ($repoRevMD + $repoRanMD + $repoRejMD + $repoRFDMD + $repoInRevMD);
        $repoRemKL = $allrepoKL - ($repoRevKL + $repoRanKL + $repoRejKL + $repoRFDKL + $repoInRevKL);
        $repoRemCL = $allrepoCL - ($repoRevCL + $repoRanCL + $repoRejCL + $repoRFDCL + $repoInRevCL);
        $repoRemFB = $allrepoFB - ($repoRevFB + $repoRanFB + $repoRejFB + $repoRFDFB + $repoInRevFB);
        $repoRemOW = $allrepoOW - ($repoRevOW + $repoRanOW + $repoRejOW + $repoRFDOW + $repoInRevOW);
        
    ?>

Here is my JS file code.

<script>
/*************************************** PIE CHARTS ***************************************************/
    
    //Settings
    let widthPC = 295;
    let heightPC = 250;
    let redrawPC = 'true';
    let offsetPC = 0;


    var bgRev = "#00e396";
    var bgRAN = "#f2c73a";
    var bgRej = "#f50d05";
    var bgRFD = "#ffb5f1";
    var bgInRev = "#0ccaf0";
    var bgNYS = "#acadad";


    // Pie Chart Start - ALL
    const PieChart1 = {
        series: [<?php echo $repoRev ?>, <?php echo $repoRan ?>, <?php echo $repoRej ?>, <?php echo $repoRFD ?>, <?php echo $repoInRev ?>, <?php echo $repoRem ?>],
        colors: [bgRev,bgRAN,bgRej,bgRFD,bgInRev,bgNYS],
        title: {show: false},
        legend: {show: false},
        chart: {
          width: widthPC,
          height: heightPC,
          type: 'pie',
          redrawOnParentResize: redrawPC,
          offsetX: offsetPC,
        },
        responsive: [{
          breakpoint: undefined,
          options: {},
        }]
        
    };

    // Init chart
    const pieChart1 = new ApexCharts(document.querySelector("#repoSubStatus"), PieChart1);

    // Render Chart
    pieChart1.render();

    
    
    // Pie Chart Start - Mt. Dennis
    const PieChart2 = {
        series: [<?php echo $repoRevMD ?>, <?php echo $repoRanMD ?>, <?php echo $repoRejMD ?>, <?php echo $repoRFDMD ?>, <?php echo $repoInRevMD ?>, <?php echo $repoRemMD ?>],
        colors: [bgRev,bgRAN,bgRej,bgRFD,bgInRev,bgNYS],
        title: {show: false},
        legend: {show: false},
        chart: {
          width: widthPC,
          height: heightPC,
          type: 'pie',
          redrawOnParentResize: redrawPC,
          offsetX: offsetPC,
        },

        
    };

    // Init chart
    const pieChart2 = new ApexCharts(document.querySelector("#repoSubStatusMD"), PieChart2);

    // Render Chart
    pieChart2.render();


    // Pie Chart Start - Keelsdale
    const PieChart3 = {
        series: [<?php echo $repoRevKL ?>, <?php echo $repoRanKL ?>, <?php echo $repoRejKL ?>, <?php echo $repoRFDKL ?>, <?php echo $repoInRevKL ?>, <?php echo $repoRemKL ?>],
        colors: [bgRev,bgRAN,bgRej,bgRFD,bgInRev,bgNYS],
        title: {show: false},
        legend: {show: false},
        chart: {
          width: widthPC,
          height: heightPC,
          type: 'pie',
          redrawOnParentResize: redrawPC,
          offsetX: offsetPC,
        },
    };

    // Init chart
    const pieChart3 = new ApexCharts(document.querySelector("#repoSubStatusKL"), PieChart3);

    // Render Chart
    pieChart3.render();


    // Pie Chart Start - Caledonia
    const PieChart4 = {
        series: [<?php echo $repoRevCL ?>, <?php echo $repoRanCL ?>, <?php echo $repoRejCL ?>, <?php echo $repoRFDCL ?>, <?php echo $repoInRevCL ?>, <?php echo $repoRemCL ?>],
        colors: [bgRev,bgRAN,bgRej,bgRFD,bgInRev,bgNYS],
        title: {show: false},
        legend: {show: false},
        chart: {
          width: widthPC,
          height: heightPC,
          type: 'pie',
          redrawOnParentResize: redrawPC,
          offsetX: offsetPC,
        },
    };

    // Init chart
    const pieChart4 = new ApexCharts(document.querySelector("#repoSubStatusCL"), PieChart4);

    // Render Chart
    pieChart4.render();


    // Pie Chart Start - Fairbank
    const PieChart5 = {
        series: [<?php echo $repoRevFB ?>, <?php echo $repoRanFB ?>, <?php echo $repoRejFB ?>, <?php echo $repoRFDFB ?>, <?php echo $repoInRevFB ?>, <?php echo $repoRemFB ?>],
        colors: [bgRev,bgRAN,bgRej,bgRFD,bgInRev,bgNYS],
        title: {show: false},
        legend: {show: false},
        chart: {
          width: widthPC,
          height: heightPC,
          type: 'pie',
          redrawOnParentResize: redrawPC,
          offsetX: offsetPC,
        },
    };

    // Init chart
    const pieChart5 = new ApexCharts(document.querySelector("#repoSubStatusFB"), PieChart5);

    // Render Chart
    pieChart5.render();


    // Pie Chart Start - Oakwood
    const PieChart6 = {
        series: [<?php echo $repoRevOW ?>, <?php echo $repoRanOW ?>, <?php echo $repoRejOW ?>, <?php echo $repoRFDOW ?>, <?php echo $repoInRevOW ?>, <?php echo $repoRemOW ?>],
        colors: [bgRev,bgRAN,bgRej,bgRFD,bgInRev,bgNYS],
        title: {show: false},
        legend: {show: false},
        chart: {
          width: widthPC,
          height: heightPC,
          type: 'pie',
          redrawOnParentResize: redrawPC,
          offsetX: offsetPC,
        },
    };

    // Init chart
    const pieChart6 = new ApexCharts(document.querySelector("#repoSubStatusOW"), PieChart6);

    // Render Chart
    pieChart6.render();

</script>



Here is my SQL File code:




SELECT

-- Total Reports Status

COUNT(CASE WHEN R.receivedStatus IN(4,5,6,7,12,13) AND E.entityID BETWEEN 0 AND 6 THEN 1 END ) AS allRepo,
COUNT(CASE WHEN R.receivedStatus = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRev,
COUNT(CASE WHEN R.receivedStatus = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRan,
COUNT(CASE WHEN R.receivedStatus = 7 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRej,
COUNT(CASE WHEN R.receivedStatus = 12 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRFD,
COUNT(CASE WHEN R.receivedStatus = 4 OR R.receivedStatus = 13 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoInRev,

-- Location 1 - Mt.Dennis
COUNT(CASE WHEN R.receivedStatus IN(4,5,6,7,12,13) AND R.loc = 1 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS allrepoMD,
COUNT(CASE WHEN R.receivedStatus = 6 AND R.loc = 1 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRevMD,
COUNT(CASE WHEN R.receivedStatus = 5 AND R.loc = 1 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRanMD,
COUNT(CASE WHEN R.receivedStatus = 7 AND R.loc = 1 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRejMD,
COUNT(CASE WHEN R.receivedStatus = 12 AND R.loc = 1 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRFDMD,
COUNT(CASE WHEN (R.receivedStatus = 4 OR R.receivedStatus = 13) AND R.loc = 1 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoInRevMD,

-- Location 3 - Keelsdale
COUNT(CASE WHEN R.receivedStatus IN(4,5,6,7,12,13) AND R.loc = 3 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS allrepoKL,
COUNT(CASE WHEN R.receivedStatus = 6 AND R.loc = 3 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRevKL,
COUNT(CASE WHEN R.receivedStatus = 5 AND R.loc = 3 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRanKL,
COUNT(CASE WHEN R.receivedStatus = 7 AND R.loc = 3 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRejKL,
COUNT(CASE WHEN R.receivedStatus = 12 AND R.loc = 3 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRFDKL,
COUNT(CASE WHEN (R.receivedStatus = 4 OR R.receivedStatus = 13) AND R.loc = 3 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoInRevKL,

-- Location 4 - Caledonia
COUNT(CASE WHEN R.receivedStatus IN(4,5,6,7,12,13) AND R.loc = 4 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS allrepoCL,
COUNT(CASE WHEN R.receivedStatus = 6 AND R.loc = 4 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRevCL,
COUNT(CASE WHEN R.receivedStatus = 5 AND R.loc = 4 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRanCL,
COUNT(CASE WHEN R.receivedStatus = 7 AND R.loc = 4 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRejCL,
COUNT(CASE WHEN R.receivedStatus = 12 AND R.loc = 4 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRFDCL,
COUNT(CASE WHEN (R.receivedStatus = 4 OR R.receivedStatus = 13) AND R.loc = 4 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoInRevCL,

-- Location 5 - Fairbank
COUNT(CASE WHEN R.receivedStatus IN(4,5,6,7,12,13) AND R.loc = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS allrepoFB,
COUNT(CASE WHEN R.receivedStatus = 6 AND R.loc = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRevFB,
COUNT(CASE WHEN R.receivedStatus = 5 AND R.loc = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRanFB,
COUNT(CASE WHEN R.receivedStatus = 7 AND R.loc = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRejFB,
COUNT(CASE WHEN R.receivedStatus = 12 AND R.loc = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRFDFB,
COUNT(CASE WHEN (R.receivedStatus = 4 OR R.receivedStatus = 13) AND R.loc = 5 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoInRevFB,

-- Location 6 - Oakwood
COUNT(CASE WHEN R.receivedStatus IN(4,5,6,7,12,13) AND R.loc = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS allrepoOW,
COUNT(CASE WHEN R.receivedStatus = 6 AND R.loc = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRevOW,
COUNT(CASE WHEN R.receivedStatus = 5 AND R.loc = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRanOW,
COUNT(CASE WHEN R.receivedStatus = 7 AND R.loc = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRejOW,
COUNT(CASE WHEN R.receivedStatus = 12 AND R.loc = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoRFDOW,
COUNT(CASE WHEN (R.receivedStatus = 4 OR R.receivedStatus = 13) AND R.loc = 6 AND E.entityID BETWEEN 0 AND 6 THEN 1 END) AS repoInRevOW,


FROM reportsub R
LEFT JOIN entity E on E.entityID = R.receivedStatus
Sponsor our Newsletter | Privacy Policy | Terms of Service