Filling blanks on SELECT
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]
Re: Filling blanks on SELECT
Ashley M. Kirchner wrote:
?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'];
}
?
I'd try php here, something like:
?php
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$mins[] = empty($row['the_minute']) ? 'blank value' :
$row['the_minute'];
$temp_f[] = empty($row['avg_temp_f']) ? 'blank value' :
$row['avg_temp_f'];
$temp_c[] = empty($row['avg_temp_c']) ? 'blank value' :
$row['avg_temp_c'];
}
?
Of course, you'd want to replace blank value with whatever a blank
record is (0, '', etc.).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Filling blanks on SELECT
Chris White wrote: I'd try php here, something like: Problem is, PHP doesn't know which record is blank. I select for 60 records and MySQL returns 55. How is PHP supposed to know which 5 are blank? -- 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]
Re: [MySQL] Re: Filling blanks on SELECT
In the last episode (Dec 28), Ashley M. Kirchner said: Chris White wrote: I'd try php here, something like: Problem is, PHP doesn't know which record is blank. I select for 60 records and MySQL returns 55. How is PHP supposed to know which 5 are blank? Mysql doesn't know either. All it's doing is storing numbers. Is it possible to pass the timestamp to your graphing program and tell it to use time as its X-axis (both gnuplot and ploticus can, for example)? If plotted as a bar chart with 1-minute-wide bars, missing records will just be blanks. If not, you'll probably have to read the records and store them in an array indexed by minute. Then when you're done reading from mysql, walk the array from 0 to 59 and write out each element. That way you're guaranteed 60 output rows. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Filling blanks on SELECT
Dan Nelson wrote: If not, you'll probably have to read the records and store them in an array indexed by minute. Then when you're done reading from mysql, walk the array from 0 to 59 and write out each element. That way you're guaranteed 60 output rows. Eh, did something different. Now I'm opening the db, inserting a time stamp whenever the script runs, which is every minute. At the end of the data gathering routine, it will go back and update that record for that time stamp. If for some reason gathering data fails, it simply won't update the db after that, but at least there's a time stamp with empty data points there. Of course, this doesn't solve the problem if the machine itself goes down, but hey, there's only so much one can do. This now allows me to still get 60 record returned, regardless of records being blank (or NULL) or not. The graphing program will do the rest. -- 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]
