Highchart chart importing table values from the database

Question

I have a system where students are enrolled in training classes, every enrolled student is "scheduled" to attend and when he attends, in addition to scheduled he "held".

What I want is to make a chart using Highcharts that compares the amount of students from a certain area who were scheduled to do that training that month and the amount that actually did.

php:

 <?php

    require('conn.php');

    $prevJan= "SELECT COUNT(*) FROM participantes WHERE PREVISTO = 'Previsto' and FORMACAO = 'Revues Techniques' and MES = 'jan' and AREA = 'R&D'";
    $realJan= "SELECT COUNT(*) FROM participantes WHERE REALIZADO = 'Realizado' and FORMACAO = 'Revues Techniques' and MES = 'jan' and AREA = 'R&D'";

    $rsp = mysqli_fetch_all($mysqli->query($prevJan));
    $rsr = mysqli_fetch_all($mysqli->query($realJan));

    $totalp = mysql_num_rows($rsp);
    $totalr = mysql_num_rows($rsr);

    $listap = array($prevJan);

    ?>

The Highchart chart:

<script type="text/javascript">

Highcharts.chart('container', {
    chart: {
        zoomType: 'xy'
    },
    title: {
        text: 'Reveus Techniques'LOP
    },
    subtitle: {
        text: 'R&D'
    },
    xAxis: [{
        categories: ['Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun',
            'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dec'],
        crosshair: true
    }],
    yAxis: [{ // Primary yAxis
        labels: {
            format: '{value}',
            style: {
                color: Highcharts.getOptions().colors[1]
            }
        },
        title: {
            text: '',
            style: {
                color: Highcharts.getOptions().colors[1]
            }
        }
    }, { // Secondary yAxis
        title: {
            text: '',
            style: {
                color: Highcharts.getOptions().colors[0]
            }
        },
        labels: {
            format: '{value} ',
            style: {
                color: Highcharts.getOptions().colors[0]
            }
        },
        opposite: true
    }],
    tooltip: {
        shared: true
    },
    legend: {
        layout: 'vertical',
        align: 'left',
        x: 120,
        verticalAlign: 'top',
        y: 100,
        floating: true,
        backgroundColor: (Highcharts.theme && Highcharts.theme.legendBackgroundColor) || '#FFFFFF'
    },
    series: [{
        name: 'Realizado',
        type: 'column',
        yAxis: 1,
        data: [<?php echo join($datar, ',') ?>],
        tooltip: {
            valueSuffix: ''
        }

    }, {
        name: 'Previsto',
        type: 'spline',
        data: [<?php echo join($datap, ',') ?>],
        tooltip: {
            valueSuffix: ''
        }
    }]
});
        </script>

I imagine for that I’ll need to turn this select in a value, insert it into a list and deliver that list to the highchart, but I don’t know that language and I have a lot of idea how to do that. If anyone can help me, it would be great. :)

Obs: Also, the chart just disappeared from my page

Table image:

Answer 1

First, this structure of your table, with two fields to identify the presence can be converted into a single boolean field.

Table structure:

ID | PARTICIPANTE | PRESENCA | DATA

PRESENCA -> Must be 1 for executed and 0 (default) for predicted.

Enter the dependencies:

<script src="https://code.highcharts.com/highcharts.src.js"></script>
<script
    src="https://code.jquery.com/jquery-3.2.1.min.js"
    integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4="
crossorigin="anonymous"></script>

Connect to the database, and make a single query:

$sql = 'SELECT * FROM participantes';
$rs = mysqli_fetch_all($mysqli->query($sql));

After that, open the query stored in $rs and build an array:

$data = [];
//$loop pela resposta no banco para construir uma array
foreach ($rs as $row) {
    $status = $row['2'];
    $date = $row['3'];
    if ($status == 0) {
        if (!array_key_exists($date, $data)) {
            $data[$date] = [];
            $data[$date]['previsto'] = 1;
        } else {
            $data[$date]['previsto'] += 1;
        }
    } else {
        if (!array_key_exists($date, $data)) {
            $data[$date] = [];
            $data[$date]['realizado'] = 1;
        } else {
            $data[$date]['realizado'] += 1;
        }
    }
}

This construction will return you something like this (This is the data I put my table to test):

Array ( [2017-01-01] => Array ( [previsto] => 1 [realizado] => 3 ) [2017-02-01] => Array ( [realizado] => 3 [previsto] => 1 ) [2017-03-01] => Array ( [previsto] => 3 [realizado] => 1 ) [2017-04-01] => Array ( [realizado] => 4 ) ) 

Note: Examine the structure of this array that will understand what was done.

Now, let’s go to the graph part, to simplify, I did in the mode called 'GAMBIARRA'.

Check the structure that the plugin asks for the result you want, in case I took a chart in columns.

//Contando registros para saber onde colocar as virgulas
$count = count($data);
$arg = '';
$arg .= 'series: [';
$a = 1;
$arg .= '{';
$arg .= 'name: "Previsto",';
$arg .= 'data: [';
foreach ($data as $key => $serie) {
    $previsto = $serie['previsto'];
    if ($previsto == '') {
        $previsto = 0;
    }
    if ($a < $count) {
        $arg .= '' . $previsto . ',';
    } else {
        $arg .= '' . $previsto . '';
    }
    $a += 1;
}
$arg .= ']},{';
$arg .= 'name: "Realizado",';
$arg .= 'data: [';
$c = 1;
foreach ($data as $key => $serie) {
    $realizado = $serie['realizado'];
    if ($realizado == '') {
        $realizado = 0;
    }
    if ($c < $count) {
        $arg .= '' . $realizado . ',';
    } else {
        $arg .= '' . $realizado . '';
    }
    $c += 1;
}
$arg .= ']}]';
?>

<script>
    $(function () {
        var myChart = Highcharts.chart('container', {
            chart: {
                type: 'column'
            },
            title: {
                text: 'Comparativo de presença'
            },
            xAxis: {
                categories: [
<?php
$b = 1;
foreach ($data as $key => $serie) {
    if ($b < $count) {
        echo '"' . $key . '",';
    } else {
        echo '"' . $key . '"';
    }
    $b += 1;
}
?>
                ]
            },
            yAxis: {
                title: {
                    text: 'Valores'
                }
            },
<?php
echo $arg;
?>
        });
    });
</script>
<div id="container" style="width:100%; height:400px;"></div>

The chart has no secret, even the most complicated and bring the data and put in the correct form, if you copy these codes in order and put somewhere, you will get a chart like this:

I do not advise you to generate the graphics this way, it is better to use the constructions that the plugin provides and indicates HERE, your error is in table logic, not in graph generation itself, take only as basis.

And finally, if you are going to use many graphics that will interact with PHP I recommend switching to the Fusioncharts library, I think q integration of them better and simpler to use, and I like their documentation better too.