CodingPHP MySQL Charts/Graphs

 

Press Ctrl+Enter to quickly submit your post
Quick Reply  
 
 
  
 From:  Ken (SHIELDSIT)  
 To:  af (CAER)     
40466.19 In reply to 40466.3 
What are you monitoring?

I figured you'd have some sort of fancy AJAXy thing to update in real time.
-------------------------
I'm not sick, but I'm not well
0/0
 Reply   Quote More 

 From:  Matt  
 To:  ANT_THOMAS      
40466.20 In reply to 40466.18 
Are you sorting the data (ORDER BY) on the primary key? If you're using another column you'll be wanting to add an index to it. MySQL will really thank you if you do.

You might get better performance by storing the date and time as MySQL's native datetime column and using the MySQL UNIX_TIMESTAMP() function to convert it to a Unix timestamp for Flot, just don't order on the function results as MySQL isn't very good at that, i.e.
code:
SELECT UNIX_TIMESTAMP(datetime_column) as unix_timestamp FROM table ORDER BY datetime_column DESC.

 

doohicky

0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  Matt     
40466.21 In reply to 40466.20 
This is the query I'm using...

code:
$query = "SELECT * FROM (SELECT * FROM outside ORDER BY id DESC LIMIT $limit) AS foo ORDER BY id ASC";

	    $result = mysql_query($query);
	    while($row = mysql_fetch_assoc($result))
	    {
	    $dataset1[] = array(($row['unixtime'] * 1000 ),$row['temperature']);
            }

 


So I'm sorting by "id" and it's set as an INDEX. Should this also be a Primary Key?

0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  ALL
40466.22 
Hahaha I really can't use the code tags :'D
0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  ANT_THOMAS      
40466.23 In reply to 40466.22 
That's better....  I think.
0/0
 Reply   Quote More 

 From:  af (CAER)  
 To:  Ken (SHIELDSIT)     
40466.24 In reply to 40466.19 
The number of users active on my Guild Wars 2 Bounty Tracker app. The numbers have been a bit... wrong the past few days as I've been experimenting with different sync methods and some people apparently haven't refreshed their browser since Saturday :-/

There's no point in updating it in real-time, really – it'd just mean a huge amount of data that wouldn't really provide any more useful information than the current polling 2-minute interval.
0/0
 Reply   Quote More 

 From:  koswix  
 To:  ANT_THOMAS      
40466.25 In reply to 40466.16 
Some thoughts:

1) drop shadow on a graph looks like wank and makes it harder to read. I keep thinking my eyes are failing to focus properly. WHERE DID YOU LEARN HOW TO GRAPH, POWERPOINT LOL?

2) increase the number of grid lines, 5 degrees apart seems a bit much.

c) I am picky

iv) that seems like an awfully big temperature variance, for optimum results your weed farm should remain between about 21 C and 26 C.

 ▪                    
             ┌────┐    ┌────┐                      
          │    │    │    │ ▪                    
          │    └────┘    │                      
          │   ──┐  ┌──   │ ▪                    
   ┌──────┤    ▪    ▪    │                      
  ┌┘      │              │ ▪                    
┌─┤       └──┐  │  │  ┌──┘                      
│ │          │ ││  ││ │   ┌─┐                   
│ │          └─┼┤  └┴─┴───┘ │                   
│ │           ─┘│           │                   
│ │   ┌──────┐  └┬──────────┘                   
  │   │      │   │                              
  │   │      │   │                              
  └───┘      └───┘                              
If Feds call you and say something bad on me, it may prove what I said are truth, they are afraid of it.

0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  koswix     
40466.26 In reply to 40466.25 
a) I agree. It's the default setting.

ii) I am going to increase the grid lines.

γ) There's nothing wrong with being picky

4) The sun reaches my balcony at about 5pm


0/0
 Reply   Quote More 

 From:  Matt  
 To:  ANT_THOMAS      
40466.27 In reply to 40466.21 
That looks fine. How are you limiting the results when selecting the different scales, are you just assuming there will always be a maximum number of readings a day and adjusting the limit?

doohicky

0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  Matt     
40466.28 In reply to 40466.27 
Yeah maximum plus 20 more to give few extra at the start.

1hour - 80
2 hours - 140
6 hours - 380
12 hours - 740
1 day - 1440
1.5 days - 2180
2 days - 2900
7 days - 10100

It defaults to 2900

To me graphs are about being able to see trends and anything less than 1 day doesn't really tell you much so I might get rid of them when I have a decent amount of data.
0/0
 Reply   Quote More 

 From:  koswix  
 To:  ANT_THOMAS      
40466.29 In reply to 40466.26 
  • Good
  1. I'm glad
January) You're right, but it often causes offence. Which is nice.

MMX) put foil over the windows/walls. It'll help stabilse the temperature variance and also reflect back the light from your halogens to reduce your energy consumption. As an added bonus, your weed farm will also show up a bit less on the infra-red camera in the police helicopter.

 ▪                    
             ┌────┐    ┌────┐                      
          │    │    │    │ ▪                    
          │    └────┘    │                      
          │   ──┐  ┌──   │ ▪                    
   ┌──────┤    ▪    ▪    │                      
  ┌┘      │              │ ▪                    
┌─┤       └──┐  │  │  ┌──┘                      
│ │          │ ││  ││ │   ┌─┐                   
│ │          └─┼┤  └┴─┴───┘ │                   
│ │           ─┘│           │                   
│ │   ┌──────┐  └┬──────────┘                   
  │   │      │   │                              
  │   │      │   │                              
  └───┘      └───┘                              
If Feds call you and say something bad on me, it may prove what I said are truth, they are afraid of it.

0/0
 Reply   Quote More 

 From:  Ken (SHIELDSIT)  
 To:  ANT_THOMAS      
40466.30 In reply to 40466.28 
Soon you will be able to tell us all that it has been the hottest day in the history of hot!
-------------------------
I'm not sick, but I'm not well
0/0
 Reply   Quote More 

 From:  af (CAER)  
 To:  ANT_THOMAS      
40466.31 In reply to 40466.23 
FWIW: http://caer.me:2988/server.js

That's the source to the Node app that's serving the monitoring page.

Also, in the time between writing the above paragraph and starting to write this one, I rewrote a big chunk of both the monitoring, server and client-side code. The monitoring code now stores counts etc. in the database (previously it appended to a log file), while the server code now sends the data in a nicer format. The client code has been updated to take that into account, and is now simpler as a result.

Oh and Redis is cool, you should try it. Much more lightweight and straightforward than *SQL.

edit:

oops, just noticed my MIME type thing is a bit stupid.

edit 2:

How come you're not requesting the graph data via AJAX? Seems a simpler way to do it, imo, and less for your server to do.
0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  af (CAER)     
40466.32 In reply to 40466.31 
AJAX...because I don't know how to. I've never knowingly used any AJAX code in anything I've made. Basically all I tend to do when I make something online is tack together the bits of things I already know and I've already made else where and then learn a little bit more to get whatever I want working sufficiently :$
0/0
 Reply   Quote More 

 From:  af (CAER)  
 To:  ANT_THOMAS      
40466.33 In reply to 40466.32 

It's really simple. First you make a PHP thing to serve the data:

<?php
// this is get_data.php
$time = $_GET['time'];
$dataset1 = magic_function_to_fetch_outdoors_data_from_db($time);
$dataset2 = magic_function_to_fetch_inoors_data_from_db($time);

$data = array(
  "dataset1" => $dataset1,
  "dataset2" => $dataset2
);

echo json_encode($data);
?>

Then you modify your HTML links like so:

<ul id="period">
  <li><a href="get_data.php?time=1h">1 hour</a></li>
  <li><a href="get_data.php?time=2h">2 hour</a></li>
  <li><a href="get_data.php?time=6h">6 hour</a></li>
</ul>

Then you have some JS that looks something like this:

var $chart = $("#placeholder"),
    options = {
        lines: { show: true },
        points: { show: false }
        // etc.
    };

function plotChart(data) {
    $.plot($chart, [
        { data: data.dataset2, color: "#DE1B55", label: "Indoors" },
        { data: data.dataset1, color: "#5F2BCF", label: "Outdoors" }
    ], options);
}

$("#period").on("click", "a", function () {
    var $link = $(this);

    $link.addClass("working");

    $.ajax({
        url: this.href,
        type: "GET",
        dataType: "json"
    }).done(function (data) {
        plotChart(data);
    }).fail(function () {
        alert("O noes!");
    }).always(function () {
        $link.removeClass("working");
    });

    return false; // stops the browser from trying to follow the link
});

And that's basically it.

0/0
 Reply   Quote More 

 From:  Drew (X3N0PH0N)  
 To:  ALL
40466.34 
Not really directly relevant but I saw this today, seems pretty coool: https://plot.ly/plot
0/0
 Reply   Quote More 

 From:  CHYRON (DSMITHHFX)  
 To:  ANT_THOMAS      
40466.35 In reply to 40466.16 
Might you not speed it up with fewer samples? Every minute seems excessive (maybe you needed that for developing/debugging).

----
"Some ideas make you dumber the moment you learn of them."
0/0
 Reply   Quote More 

 From:  CHYRON (DSMITHHFX)  
 To:  Drew (X3N0PH0N)     
40466.36 In reply to 40466.34 
That choked my dual core 2.5g.

----
"Some ideas make you dumber the moment you learn of them."
0/0
 Reply   Quote More 

 From:  ANT_THOMAS   
 To:  CHYRON (DSMITHHFX)     
40466.37 In reply to 40466.35 
There isn't really any developing/debugging as such. I've been playing with temperature sensors with my RPis and I wanted to put one outdoors and I decided it would be nice to look at the data in a graph. Every minutes is definitely overkill but it's very quick when I access the data locally, I think it would be slow even if the data was held on the hosting server, especially when it gets to weeks and months worth of data.
0/0
 Reply   Quote More 

 From:  af (CAER)  
 To:  CHYRON (DSMITHHFX)     
40466.38 In reply to 40466.35 
At that rate, 24 hours' worth is only 60k or so – 1440 data points per series, which is not a huge amount, and like I said earlier, even my phone can handle that with no noticeable slowing.
0/0
 Reply   Quote More 

Reply to All  
 

1–20  21–40  41–47

Rate my interest:

Adjust text size : Smaller 10 Larger

Beehive Forum 1.5.2 |  FAQ |  Docs |  Support |  Donate! ©2002 - 2024 Project Beehive Forum

Forum Stats