PHP MySQL Charts/Graphs - Teh Forum

<body> <div id="header"> <span class="left"> <span class="back"><a href="lthread_list.php?webtag=DEFAULT"><span class="image mobile_back" title="Back"></span></a></span> <span class="image mobile_logo" title="Beehive Forum Logo"></span> </span> <ul> <li> <a class="reply_all" href="lpost.php?webtag=DEFAULT&reply_to=40466.0&return_msg=40466.20"> <span class="image mobile_reply_all"></span><span class="text">Reply to All</span> </a> </li> <li> <a class="navigation" href="#"> <span class="image mobile_navigation"></span><span class="text">Show messages</span> </a> </li> <li> <a class="main" href="#"><span class="image mobile_menu" title="Menu"></span> </a> </li> </ul> </div> <div class="menu main"> <ul> <li><a href="lthread_list.php?webtag=DEFAULT">Messages</a></li> <li><a href="lpm.php?webtag=DEFAULT">Inbox</a></li> <li><a href="lsearch.php?webtag=DEFAULT">Search</a></li> <li><a href="llogon.php?webtag=DEFAULT">Login</a></li> </ul> </div> <div class="menu navigation"><a href="lmessages.php?webtag=DEFAULT&msg=40466.1">1–10</a><a href="lmessages.php?webtag=DEFAULT&msg=40466.11">11–20</a><a href="lmessages.php?webtag=DEFAULT&msg=40466.21">21–30</a><a href="lmessages.php?webtag=DEFAULT&msg=40466.31">31–40</a><a href="lmessages.php?webtag=DEFAULT&msg=40466.41">41–47</a></div> <div id="page_content"> <h3 class="thread_title"><a href="index.php?webtag=DEFAULT&amp;msg=40466.20">PHP MySQL Charts/Graphs</a> </h3> <div id="messages" data-navigation="40466_20_47_10"> <a name="a40466_20"></a><div class="message" id="message_40466_20"> <div class="message_header"> <div class="message_from"> From: Matt<span class="message_time"> 6 May 2013 18:23</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 6 May 2013 18:57"></span></span> <span class="message_count">20 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.20">40466.20</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.18">40466.18</a></div> <div class="message_body"> 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.<br /><br /> 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.<br /><div class="quotetext"> <b>code:</b></div> <pre class="code"> SELECT UNIX_TIMESTAMP(datetime_column) as unix_timestamp FROM table ORDER BY datetime_column DESC.</pre> <p> </p> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.20&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.20"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_21"></a><div class="message" id="message_40466_21"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time"> 6 May 2013 19:10</span> <div class="clearer"></div> </div><div class="message_to">To: Matt <span><span class="image post_read" title="Read: 6 May 2013 20:05"></span></span> <span class="message_count">21 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.21">40466.21</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.20#a40466_20" target="_self">40466.20</a></div> <div class="message_body"> This is the query I'm using...<br /><br /><div class="quotetext"> <b>code:</b></div> <pre class="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']); } </pre> <p> </p> <br /><div> So I'm sorting by "id" and it's set as an INDEX. Should this also be a Primary Key?</div> <br /><div class="edit_text">EDITED: 6 May 2013 19:13 by ANT_THOMAS</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.21&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.21"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_22"></a><div class="message" id="message_40466_22"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time"> 6 May 2013 19:12</span> <div class="clearer"></div> </div><div class="message_to">To: ALL<span class="message_count">22 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.22">40466.22</a></div> <div class="message_body"> Hahaha I really can't use the code tags <span class="emoticon e_happycry" title=":'D"><span class="e__">:'D</span></span></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.22&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.22"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_23"></a><div class="message" id="message_40466_23"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time"> 6 May 2013 19:13</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 6 May 2013 19:13"></span></span> <span class="message_count">23 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.23">40466.23</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.20#a40466_22" target="_self">40466.22</a></div> <div class="message_body"> That's better.... I think.</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.23&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.23"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_24"></a><div class="message" id="message_40466_24"> <div class="message_header"> <div class="message_from"> From: af (CAER)<span class="message_time"> 6 May 2013 19:29</span> <div class="clearer"></div> </div><div class="message_to">To: Ken (SHIELDSIT) <span><span class="image post_read" title="Read: 6 May 2013 20:00"></span></span> <span class="message_count">24 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.24">40466.24</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.19">40466.19</a></div> <div class="message_body"> The number of users active on my <a href="http://bounty.caer.me/">Guild Wars 2 Bounty Tracker</a> 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 <span class="emoticon e_unsure" title=":-/"><span class="e__">:-/</span></span><br /><br /> 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.<br /><div class="edit_text">EDITED: 6 May 2013 19:31 by CAER</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.24&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.24"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_25"></a><div class="message" id="message_40466_25"> <div class="message_header"> <div class="message_from"> From: koswix<span class="message_time"> 6 May 2013 20:00</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 6 May 2013 20:08"></span></span> <span class="message_count">25 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.25">40466.25</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.16">40466.16</a></div> <div class="message_body"> Some thoughts:<br /><br /> 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?<br /><br /> 2) increase the number of grid lines, 5 degrees apart seems a bit much.<br /><br /> c) I am picky<br /><br /> iv) that seems like an awfully big temperature variance, for optimum results your weed farm should remain between about 21 C and 26 C.<br /></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.25&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.25"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_26"></a><div class="message" id="message_40466_26"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time"> 6 May 2013 20:10</span> <div class="clearer"></div> </div><div class="message_to">To: koswix <span><span class="image post_read" title="Read: 6 May 2013 20:48"></span></span> <span class="message_count">26 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.26">40466.26</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.20#a40466_25" target="_self">40466.25</a></div> <div class="message_body"> a) I agree. It's the default setting.<br /><br /> ii) I am going to increase the grid lines.<br /><br /> γ) There's nothing wrong with being picky<br /><br /> 4) The sun reaches my balcony at about 5pm<br /><br /><br /></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.26&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.26"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_27"></a><div class="message" id="message_40466_27"> <div class="message_header"> <div class="message_from"> From: Matt<span class="message_time"> 6 May 2013 20:11</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 6 May 2013 20:15"></span></span> <span class="message_count">27 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.27">40466.27</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.20#a40466_21" target="_self">40466.21</a></div> <div class="message_body"> 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?</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.27&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.27"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_28"></a><div class="message" id="message_40466_28"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time"> 6 May 2013 20:20</span> <div class="clearer"></div> </div><div class="message_to">To: Matt <span><span class="image post_read" title="Read: 7 May 2013 06:18"></span></span> <span class="message_count">28 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.28">40466.28</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.20#a40466_27" target="_self">40466.27</a></div> <div class="message_body"> Yeah maximum plus 20 more to give few extra at the start.<br /><br /> 1hour - 80<br /> 2 hours - 140<br /> 6 hours - 380<br /> 12 hours - 740<br /> 1 day - 1440<br /> 1.5 days - 2180<br /> 2 days - 2900<br /> 7 days - 10100<br /><br /> It defaults to 2900<br /><br /> 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.<br /><div class="edit_text">EDITED: 6 May 2013 20:21 by ANT_THOMAS</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.28&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.28"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a40466_29"></a><div class="message" id="message_40466_29"> <div class="message_header"> <div class="message_from"> From: koswix<span class="message_time"> 6 May 2013 20:53</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 6 May 2013 21:07"></span></span> <span class="message_count">29 of 47</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=40466.29">40466.29</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=40466.20#a40466_26" target="_self">40466.26</a></div> <div class="message_body"> <ul><li> Good</li> </ul><ol><li> I'm glad</li> </ol><div style="margin-left:40px;"> January) You're right, but it often causes offence. Which is nice.<br /><br /> 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.</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=40466.29&return_msg=40466.20" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="40466.29"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div></div> <div class="message_page_footer"> <ul> <li class="right_col"><form accept-charset="utf-8" method="get" action="lmessages.php" target="_self"><input type="hidden" name="webtag" id="webtag" class="bhinputtext" value="DEFAULT" dir="ltr" /><input type="hidden" name="msg" id="msg" class="bhinputtext" value="40466.30" dir="ltr" /><input type="submit" name="submit" id="keep_reading" value="Keep reading…" class="button" /></form></li> </ul> </div> </div> </body>