Thanks to Chris yesterday, I managed to figure some things out by
myself. Now I'm faced with another problem. Given the same database again:
+------------+-----------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------+-----------------------+------+-----+-------------------+----------------+
| uid | mediumint(8) unsigned | NO | PRI | NULL |
auto_increment |
| temp_f | float(4,1) | YES | | NULL
| |
| temp_c | float(4,1) | YES | | NULL
| |
| windchill | float(4,1) | YES | | NULL
| |
| dewpoint | float(4,1) | YES | | NULL
| |
| time | timestamp | NO | | CURRENT_TIMESTAMP
| |
+------------+-----------------------+------+-----+-------------------+----------------+
I gather data from it based on select intervals and dump the result
into arrays. My problem is what to do if there's a gap.
For example, let's assume I have data for 10:11, 10:12, 10:14,
10:15, 10:20. Notice the missing ones in there, 10:13, 10:16 to 10:19 -
say the server had a hiccup and couldn't gather info.
Now, I'm doing a 60 minute select based on Chris' suggestion as follows:
select hour(time) as the_hour,
minute(time) as the_minute,
avg(temp_f) as average_temp_f
from data
where time > now() - interval 60 minute
group by the_hour, the_minute;
After that I put it all into an array (or arrays since that's what
the resulting code needs to be fed into something else)
<?php
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$mins[] = $row['the_minute'];
$temp_f[] = $row['avg_temp_f'];
$temp_c[] = $row['avg_temp_c'];
}
?>
These arrays then get fed into a charting program. The problem is
that it (the chart) has no idea that there are blanks in there. It
takes the values as they're fed. So my question is, is there a way to
have MySQL return an empty record for the gaps? If I'm asking for 60
records, and there are only 55, is there some way of figuring out
(within MySQL) which ones aren't there and return a blank instead?
Am I going way outside the scope of MySQL here?
--
W | It's not a bug - it's an undocumented feature.
+--------------------------------------------------------------------
Ashley M. Kirchner <mailto:[EMAIL PROTECTED]> . 303.442.6410 x130
IT Director / SysAdmin / Websmith . 800.441.3873 x130
Photo Craft Imaging . 3550 Arapahoe Ave. #6
http://www.pcraft.com ..... . . . Boulder, CO 80303, U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
