List:General Discussion« Previous MessageNext Message »
From:Ashley M. Kirchner Date:December 28 2006 6:21pm
Subject:Filling blanks on SELECT
View as plain text  
    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:ashley@stripped>   .   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.

Thread
Filling blanks on SELECTAshley M. Kirchner28 Dec
  • Re: Filling blanks on SELECTChris White28 Dec
    • Re: [MySQL] Re: Filling blanks on SELECTAshley M. Kirchner28 Dec
      • Re: [MySQL] Re: Filling blanks on SELECTDan Nelson28 Dec
        • Re: [MySQL] Re: Filling blanks on SELECTAshley M. Kirchner28 Dec
        • SQL syntaxScott Yamahata29 Dec
          • Re: SQL syntaxViSolve DB Team29 Dec
          • Re: SQL syntaxViSolve DB Team29 Dec
          • Re: SQL syntaxJoerg Bruehe29 Dec
          • Re: SQL syntaxRhino31 Dec