Highcharts using JSON - graph not displaying mysql data

Question

I have been able to produce results from mysql using:

$myArray=array();
$tempArray = array();

// Get all records
while ( $row = $results->fetch_assoc())
{
    $tempArray = $row;
    array_push($myArray, $tempArray);
}
echo json_encode($myArray);

$mysqli->close();

 ?>

And I then included this to produce a chart on my page index.php by using the following Javascript.

what concepts/code am I not understanding/missing to produce a chart based upon my ajax json?

EDITED - SOLUTION:

Final PHP code to produce the json:

while ( $row = $results->fetch_assoc())
{   

    $tempArray[0] = $row['unix_timestamp(auct.end_date)'];
    $tempArray[0] *= 1000;
    $tempArray[1] = $row['winning_bid'];

    array_push($myArray, $tempArray);

}
echo json_encode ($myArray, JSON_NUMERIC_CHECK);

$mysqli->close();

 ?>

Final javascript code:

$('#btn_search').click(function(){
    txt_search = $('#txt_search').val();
    $.ajax({                                      
      url: './php/search.php',  
      type: 'GET',
      data: {search: txt_search}, 
      dataType: 'json',                   
      success: function(rows)      
      {

        chart = new Highcharts.Chart({
            chart: {
                renderTo: 'chartdiv',
                type: 'line',
                marginRight: 100,
                marginBottom: 50
            },
            title: {
                text: 'Whisky Tracking',
                x: -20 //center
            },
            xAxis: {
                text: 'EndDate',
                type: 'datetime'
            },
            yAxis: {
                title: {
                    text: 'Price',
                    color: '#CC680E'
                }, 
                plotLines: [{
                    value: 0,
                    width: 20,
                    color: '#CC680E'
                }]
            },
            series: [{
            name:  txt_search,
            xAxis:0,
            data: rows,
                dataLabels: {
                    enabled: true,
                    formatter: function() {
                    return '£'+ Highcharts.numberFormat(this.y, 0);
                    }
                }
            }],         

        });
      }
    }); 
    goToByScroll('tracker');
    return false;
});

Sample Data from the JSON:

[1306732000000,160],[1306745000000,45],[1306788000000,65],[1306788000000,50],[1306712000000,130],[1306733000000,240],[1306744000000,60],[1306788000000,250],[1306710000000,145]

What is not working? Have you got the div for chartdiv on the page? — bhttoan
– bhttoan, Commented Jun 1, 2014 at 17:36
Yes - sorry it doesn't display any data (guessing the array is the problem). The chart displays but with no data — n34_panda
– n34_panda, Commented Jun 1, 2014 at 17:37
What do you expect it to show? At the moment it looks like you have three different rows in the JSON so how do you plan to show all three on the y axis? — bhttoan
– bhttoan, Commented Jun 1, 2014 at 17:39
I am not interested in the name coloumn but rather the winning on the Y axis and the date on the x axis - sorry I should have included that to — n34_panda
– n34_panda, Commented Jun 1, 2014 at 17:40

Paweł Fus · Accepted Answer · 2014-06-03 12:04:29Z

2

The problem is that values are strings, for example, your data:

["2011-05-30 00:00:00","130"]

Should be instead:

[1306706400000, 130]

To it's timestamp in ms and true value.

You can read about JSON_NUMERIC_CHECK option for json_encode(string, JSON_NUMERIC_CHECK) to change strings to numbers. But dates to timestamps you need to change on your own.

Edit: Also the problem was with setting data in doubled array, changed from:

data: [rows]

to:

data: rows

edited Jun 3, 2014 at 12:04

answered Jun 2, 2014 at 11:59

Paweł Fus

45.1k3 gold badges65 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

n34_panda Over a year ago

Thanks, I have tried amending the query to return the datetime column as UNIX_TIMESTAMP but that didn't work. It did change the graph but not anything like I would expect (simply extended the Y-AXIS ranges). Am I right in thinking the first element in the array is the x-axis and the second the y-axis?

Paweł Fus Over a year ago

Indeed, first element is x-value, second is y-value. Make sure you have not set categories in xAxis. Also, could you attach to the question example of actual returnej JSON? Thanks.

n34_panda Over a year ago

I have edited it above, I read something that suggested UNIX_TIMESTAMP required to be divided by 1000 to bring it into line with Javascript...thanks for the info regarding the xy

Paweł Fus Over a year ago

Not dividing, multiplying.. ;) Why have you changed order of elements?

n34_panda Over a year ago

I am going in circles, keep reading posts which are not fully related and I don't understand the changes I make...grrr. Anyway I've attached a screenshot of what the chart looks like.. I am switching the order back - but the y-axis looks to be using the timstamp?

|

Collectives™ on Stack Overflow

Highcharts using JSON - graph not displaying mysql data

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related