prinsobed prinsobed - 3 months ago 31
JSON Question

How do I create a C3 Chart Line Graph from JSON data?

I need help for a school project. I have been able to pull data from a mySQL database into an array and encoded into JSON, which displays fine. Now, I need help with passing the JSON data to C3 to produce a chart (if possible on the same page).

What I've done so far:

$strQuery = "SELECT production_date,oil FROM production WHERE well = '$h_well' AND production_date BETWEEN '$h_start' AND '$h_end' ORDER BY production_date ASC";
$result = mysqli_query($conn, $strQuery);

// Print out rows
$data = array();
while ( $row = $result->fetch_assoc() ) {
$data[] = $row;
}
echo json_encode( $data );

Answer

You need to create two separate array, one for your data and one for dates that you want to show on x-axis and then pass that array to java script.

here is full working example

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$strQuery = "SELECT production_date,oil FROM  production WHERE well = '$h_well' AND production_date BETWEEN '$h_start' AND '$h_end' ORDER BY production_date ASC";
$result = mysqli_query($conn, $strQuery);

// Print out rows
$valuesArray = array();
$datesArray = array();

$valuesArray[] = 'Oil';
$datesArray[] = 'x';
while ($row = $result->fetch_assoc()) {
    $datesArray[] = $row['production_date'];
    $valuesArray[] = $row['oil'];
}
?>


<html>
    <head>
        <title>C3 Liner example</title>
        <link href="c3_scr/c3.css" rel="stylesheet" type="text/css" />
        <script src="https://d3js.org/d3.v3.min.js"></script>
        <script src="c3_scr/c3.js"></script><!-- load jquery -->


    </head>
    <body>
        <div id="chart"></div>
        <script>
            var xAxisArr = <?php echo json_encode($datesArray); ?>;
            var dataArr = <?php echo json_encode($valuesArray, JSON_NUMERIC_CHECK); ?>;
            var chart = c3.generate({
                bindto: '#chart',
                data: {
                    x: 'x',
                    columns: [
                        xAxisArr,
                        dataArr
                    ]
                },
                axis: {
                    x: {
                        type: 'timeseries',
                        tick: {
                            format: '%Y-%m-%d'
                        }
                    }
                }
            });
        </script>
    </body>
</html>