how to show data for single day in graph using php mysql

Question

I am using HighCharts and im showing temperature on the graph. I was thinking that is it possible to show data for a single day only or 1 week or over a month using highchart graph. Right now I get all the data which is from the 1st record to the latest one from MySQL DB . Here is the code . this is data.php -

<?php
$con = mysql_connect("192.168.100.107:3306","root","hannan786");

if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("pi", $con);

$sth = mysql_query("SELECT * FROM temperature ");
$rows = array();
$rows['Temperature'] = 'temperature';
while($r = mysql_fetch_array($sth)) {
    $rows['data'][] = $r['temperature'];
}


$result = array();
array_push($result,$rows);



print json_encode($result, JSON_NUMERIC_CHECK);

mysql_close($con);
?>

And this is the code for the main Page where the graph data is displayed -

<!DOCTYPE HTML>
<html>
    <head>
    <meta http-equiv="Refresh" Content="5">

        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Temperature</title>

        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
        <script type="text/javascript">
$(function () {
    var chart;
    $(document).ready(function() {
        $.getJSON("data.php", function(json) {

            chart = new Highcharts.Chart({
                chart: {
                    renderTo: 'container',
                    type: 'line',
                    marginRight: 130,
                    marginBottom: 25
                },
                title: {
                    text: 'Temperature',
                    x: -20 //center
                },
                subtitle: {
                    text: '',
                    x: -20
                },
                xAxis: {
                    categories: ['Temperature']
                },
                yAxis: {
                    title: {
                        text: 'Amount'
                    },
                    plotLines: [{
                        value: 0,
                        width: 1,
                        color: '#808080'
                    }]
                },
                tooltip: {
                    formatter: function() {
                            return '<b>'+ this.series.name +'</b><br/>'+
                            this.x +': '+ this.y;
                    }
                },
                legend: {
                    layout: 'vertical',
                    align: 'right',
                    verticalAlign: 'top',
                    x: -10,
                    y: 100,
                    borderWidth: 0
                },
                series: json
            });
        });

    });

});
        </script>
    </head>
    <body>
  <script src="http://code.highcharts.com/highcharts.js"></script>
  <script src="http://code.highcharts.com/modules/exporting.js"></script>

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

    </body>
</html>

Right Now I get this as output -

Output

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 12 '16 at 20:48
@AashuSpk . Yeah Sure , Here is the table structure - http://prntscr.com/arhz13 — Abdul Hannan Mustajab, Apr 12 '16 at 21:03
@jojonas I am getting all the data from database right from the First Record. I want that we should be able to choose specific date from a box . And the date and time is also not being displayed on hovering over slope. — Abdul Hannan Mustajab, Apr 12 '16 at 22:07
So, I am confused as to what the actual question is. If you pull all of the data, and you send the chart all of the data, the chart is going to show all of the data. There are many different ways that you could go about allowing a user to select a specific time frame, and you'll need to be more specific about what you want, and what about it you are having trouble with. — jlbriggs, Apr 13 '16 at 12:51
@jlbriggs. HEY my actual question is to show graph for last 24 hours. — Abdul Hannan Mustajab, Apr 13 '16 at 13:51
So then you need to only pull the last 24 hours worth of data from your database. — jlbriggs, Apr 13 '16 at 14:30
@jlbriggs so how do I do that and how do I change the x axis values. ?? And show time on hovering the slope of graph. — Abdul Hannan Mustajab, Apr 13 '16 at 14:54
The x axis values depend on what you send to the chart. if you only pull the last 24 hours worth of data from the database, that's all that will show on the chart. You "change the x axis values" by sending the right date to the chart. Again - this is a very broad topic - this is a matter of setting up a user interface to allow them to select a date range, set up the right SQL queries to pull the right data, etc. There are multiple ways to accomplish this. — jlbriggs, Apr 13 '16 at 15:03
@jlbriggs Thanks a lot. Just the last thing. Please could you help me out with the query for last 24 hrs data !!! — Abdul Hannan Mustajab, Apr 13 '16 at 15:11
Not without knowing what your database looks like. I think at this point you really need to look for a basic SQL tutorial. You need to specify a WHERE clause, in which you tell the database which records you want, based on the date values - but that all depends on how they're stored. — jlbriggs, Apr 13 '16 at 15:14
@jlbriggs Here is the database structure . I know SQL PHP commands but I don't know the query for last 24 hours data. — Abdul Hannan Mustajab, Apr 13 '16 at 15:17
Right, because there isn't "a query" for the last 24 hours :) Knowing SQL and knowing "SQL PHP commands" are not the same thing - search google for how to do basic querying - in your case you'll need to establish a start datetime, and an end datetime, and return everything in BETWEEN them, in your WHERE clause. — jlbriggs, Apr 13 '16 at 15:28

score 0 · Answer 1 · answered Apr 12 '16 at 20:54

0

You can use the method setExtremes() of the Highcharts Api. You only have to provide min and max value, that in your case consists of the same Date Object, I think.

This is a fiddle as an example on how to use it.

chart.xAxis[0].setExtremes(
    Date.UTC(2007, 0, 1),
    Date.UTC(2007, 11, 31)
);

Docs are here: http://api.highcharts.com/highstock#Axis.setExtremes

answered Apr 12 '16 at 20:54

theCodeSurgeon

460
2
9

Thanks For Your Reply , But I didnt get you and I think that even you didnt get me. ! – Abdul Hannan Mustajab Apr 12 '16 at 21:00
When I hover over the graph slope , I only get the temperature , Not the date and Time , Do you have any idea , How could I get date as well over there . – Abdul Hannan Mustajab Apr 12 '16 at 21:07
1

you can retrieve the data in the series Object passed to Highcharts, and read them in the tooltip section, customizing your tooltip dynamic text. Fiddle here http://jsfiddle.net/Mn6sB/9/ – theCodeSurgeon Apr 12 '16 at 21:14

how to show data for single day in graph using php mysql

1 Answers1