Doubt about highcharts and php

Question

For styling a web page, I usually use highchart charts with information from a mysql database via php. For those who are not familiar with highcharts, here is a simple code example.

Highcharts.chart('minhadiv', {
chart: {
    type: 'bar'
},

xAxis: {
    categories: ['Categoria 1', 'Categoria 2', 'Categoria 3'],
},
series: [{
    name: 'Informação',
    data: [1, 2, 3]

}]
});

This code returns me a graph like this:

I always make the php connection to mysql within the field data, succeeding. Below follows the example of an sql connection I’ve used before:

data: [<?php
      $mysqli = new mysqli('servidor', 'usuario', 'senha', 'banco');
      $sql = $mysqli->query("SELECT minhacoluna from minhatabela");
                     ?>
                    <?php while ($result = mysqli_fetch_array($sql)) {?>
                      <?php echo $result["minhacoluna"]?>,

                    <?php } ?>],

The problem is that now I needed to make this sql connection both in the field data how much on the field categories, as the categories may change according to my database. When trying to put a php code like the above that I normally use, I got no result. How to proceed in this case?

Answer 1

You can do so to show a chart with the categories as it is in the database.

Records in the Database:

php code to feed the chart:

$mysqli = new mysqli('servidor', 'usuario', 'senha', 'banco');


// Aqui você pode fazer um select alimentando normal uma váriavel com as categorias a ser exibida
$categories = array();


$sql = $mysqli->query("SELECT nome from categorias");
while ($result = mysqli_fetch_array($sql)) {
    $categories[] = $result['nome'];
}

// Select para pegar os dados da tabela
$sql = $mysqli->query("SELECT coluna, valor from grafico");

$valores = array();

// while dos registros alimentando um array
while ($result = mysqli_fetch_array($sql)) {
    $valores[$result['coluna']][] = floatval($result['valor']);
}

// aqui irá ser colocado a váriavel que foi alimentada por array no while acima
// para deixar no padrão que o highcharts reconhece
foreach($valores as $key => $value)
{
    $series[] = array('name' => utf8_encode($key), 'data' => array_values($value));
}   

Code in js of highcharts:

Highcharts.chart('container', {
    chart: {
        type: 'bar'
    },
    title: {
        text: 'Chart title'
    },
    xAxis: {
        categories: <?=json_encode($categories); ?> // Transforma array em json para mostrar as categorias
    },
    yAxis: {
        min: 0,
        title: {
            text: 'Total fruit consumption'
        }
    },
    legend: {
        reversed: true
    },
    plotOptions: {
        series: {
            stacking: 'normal'
        }
    },
    series: <?=json_encode($series); ?> // Transforma array em json para mostrar as informações do gráfico
});

Upshot: