Generating Highcharts from MySQL data with PHP does not work

Question

your help is very appreciated.

I am trying to generate a highcharts line graph from data I have stored in a mysql database called "log", said database has different tables each with 5 fields: fecha_id (type date) hora_id (type time) ping_id (type float) timechar_id (type varchar) and pingchar_id (type varchar). I try to select the table, the date and time rows from which I wish to use the data using the POST method, then generate valid data for highcharts to use, I am trying to use the time as X axis and the ping value as y axis. As Deep3015 suggested, I edited my code to followthe guide posted on highchart's website, the (updated) code is:

<?php
        require('conexionBD.php');
        $depar  = $_POST['dto'];
    $date   = $_POST['fecha'];
    $ini    = $_POST['hini'];
    $fin    = $_POST['hfin'];
?>

<!DOCTYPE HTML>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Highcharts test</title>

        <style type="text/css">

        </style>
    </head>
    <body>
<script src="code/highcharts.js"></script>
<script src="code/modules/exporting.js"></script>

<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>

        <script type="text/javascript">
<?php
$sql = "select * from ".$depar" where fecha_id = '".$date"' and hora_id >= '".$ini"' and hora_id <= '".$fin"'";
$result = $conn->query($sql);
while ($row = mysql_fetch_array($result)) {
   $data[] = $row['timechar_id'];
   $data1[] = $row['pingchar_id'];
}
?>

Highcharts.chart('container', {
    chart: {
        type: 'line'
    },
    title: {
        text: 'Ping stats'
    },
    subtitle: {
        text: 'Logged on MySQL'
    },
    xAxis: {
        categories: [<?php echo join($data, ',') ?>]
    },
    yAxis: {
        title: {
            text: 'Time in [ms]'
        }
    },
    plotOptions: {
        line: {
            dataLabels: {
                enabled: true
            },
            enableMouseTracking: false
        }
    },
    series: [{
        name: 'LPZ',
        data: [<?php echo join($data1, ',') ?>]
    }]
});
        </script>
    </body>
</html>

I still get a blank page. So I'd like to know what I'm doing wrong. Thanks for helping me out.

No console error. check highcharts.com/docs/working-with-data/data-from-a-database — Deep 3015
– Deep 3015, Commented Oct 8, 2017 at 16:15
Ok, that seems simple, updated my code following the guidelines given by the link, still nothing. — HumbCa
– HumbCa, Commented Oct 8, 2017 at 16:43
then start debugging php variables with var_dump() or print_r(). check this variable has some values or not. Remove error_reporting(0) if you are using in php — Deep 3015
– Deep 3015, Commented Oct 8, 2017 at 17:36

Akshay Hegde · Accepted Answer · 2017-10-09 19:14:03Z

You got syntax error

$sql = "select * from ".$depar" where fecha_id = '".$date"' and hora_id >= '".$ini"' and hora_id <= '".$fin"'";
                             ^                           ^                         ^                          ^                                                                      
                            Here                       Here

Also mysql_* functions are officially deprecated as of PHP 5.5 (released June 2013). Has been removed entirely as of PHP 7.0 (released December 2015), use mysqli_* or pdo

For better Readability, you can also use HEREDOC like below

$sql = <<<EOF
select * 
from `$depar` 
where `fecha_id` = '$date' and 
      `hora_id` >= '$ini'  and 
      `hora_id` <= '$fin'
EOF;

/* Above you can also use between
  `hora_id` between '$ini' and '$fin'
*/

$mysqli = new mysqli("localhost", "my_user", "my_password", "database");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$output = array();

if ($result = $mysqli->query($sql)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        $output[] = $row;
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();

/*some error handling if array is empty*/
if(empty($output)){
       /*
            Couldn't create plot,
      */
}

And in your highchart options

xAxis: {
       // your will get [charid, charid2, charid2, ... ]
      categories: <?php echo json_encode(array_column($output,'timechar_id'),JSON_NUMERIC_CHECK); ?>
  },

and in series data

series: [{
        name: 'LPZ',

        // your will get [id1, id2, id2, ... ]
        data: <?php echo json_encode(array_column($output,'pingchar_id'),JSON_NUMERIC_CHECK); ?>
}]

Collectives™ on Stack Overflow

Generating Highcharts from MySQL data with PHP does not work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related