2. Programming Q&A
  3. mysql - Getting multiple values for a date for Google Charts - Stack Overflow

mysql - Getting multiple values for a date for Google Charts - Stack Overflow

admin2025-04-17  2

Let try to make this as simple as possible. I'm attempting to grab sensor records from my MySQL server. There is 10 sensors that have their values compiled to a table in my DB. Now for the sake of trying to make this easier lets just say 3 days of records lets start with Jan 1, 2025 since that's a weird thing as is when sending to Google Charts in this example:

sensorID | sensorValue | sensorDate
1 | 394 | 2025-01-01 00:01:00 (time is irrelevant as explained below)
2 | 432 | 2025-01-01 00:01:10
3 | 434 | 2025-01-01 00:01:15
1 | 405 | 2025-01-02 00:01:00
2 | 416 | 2025-01-02 00:01:10
3 | 328 | 2025-01-02 00:01:15
2 | 428 | 2025-01-03 00:01:10 <--- Yes this can happen if a sensor didn't respond on first check
1 | 421 | 2025-01-03 00:01:15 ----- However the YYYY-M-D is all I'm concerned about with ID.
3 | 301 | 2025-01-03 00:01:20 ----- Below the correct order is placed in the build of the array.

Now I'm pulling the data with the following Query:

$result0 = mysqli_query($conn, "SELECT DISTINCT sensorID, DATE_FORMAT(sensorDate, '%Y-%m-%d') as sensorDate_change, sensorValue FROM sensorRecords ORDER BY sensorDate, sensorID") ;

The above I believe will keep the value in order with the date and id. But anyways on to the real problem.

What I need to do is build the data.addRows for the chart. Now based on Googles wacky way of numbering the month this isn't a big deal, I can just extract the Y-m-d values to -1 the month since 0 is January. Yeah, that's weird.

Now what I need to do is pull the values for a date for (for this example 3 days) and format them for the data.addRows like below.

[new Date(2025, 0, 1), 394, 432, 434],  [new Date(2025, 0, 2), 405, 416, 328],  [new Date(2055, 0, 3), 421, 428, 301]

UPDATE:

Here is the code I have come up with but alas, I have a new problem. It repeats itself 3 times for each of the 3 records. I'm so close and going nuts on getting this to to work properly.

Code:

    <?php
    // include Global PHP
    include_once 'global.php' ;
    // include MySQL Database Configuration File
    include_once 'rcf_db.php' ;
    
    // Begin Google Chart Rows <<<<<<<<<<
     echo 'data.addRows([ ' ;

    // Grab Data from Sensor Records
    $result0 = mysqli_query($conn2, "SELECT DISTINCT DATE_FORMAT(sensorDate, '%Y-%m-%d') as date_change, sensorDate FROM sensorRecords ORDER BY sensorDate") ;
    
    while($row = mysqli_fetch_array($result0)) {
        $recordDate = $row['date_change'] ;
        $sensorDate = $row['sensorDate'] ;
        $dateValue = strtotime($recordDate) ;
        $year = date('Y',$dateValue) ;
        $monthNo = date('m',$dateValue) ;
        $monthNo = $monthNo - 1 ;
        $monthDay = date('d',$dateValue) ;
        
        // Build Google Chart Rows Part 1
        $build_part_1 = '[new Date('.$year.', '.$monthNo.', '.$monthDay.'),' ;
        
        // Build Google Chart Rows Part 2
        $SQL1 = "SELECT sensorValue FROM sensorRecords WHERE DATE_FORMAT(sensorDate, '%Y-%m-%d') = '".$recordDate."' ORDER BY sensorID;";
        $result1 = mysqli_query($conn2, $SQL1) ;
        
            // Do for Each Matching Date
            if(mysqli_num_rows($result1) > 1) {
                $strings = [] ;
                foreach($result1 as $row) {
                    $strings[] = $row['sensorValue'] ;
                }
            }
        
        // Echo Results
        $build_part_2 = implode(', ', $strings).'], ' ;
        echo $build_part_1.' '.$build_part_2.'' ;
    }
    
    // Close Google Chart Rows
    echo ']);' ;
    
    // Free Result Set
    mysqli_free_result($result0) ;
    mysqli_free_result($result1) ;
    
    // Close DB Connections
    $conn2->close() ;
    ?>

The Results: :(

data.addRows([ [new Date(2025, 0, 01), 394, 432, 434], [new Date(2025, 0, 01), 394, 432, 434], [new Date(2025, 0, 01), 394, 432, 434], [new Date(2025, 0, 02), 405, 416, 328], [new Date(2025, 0, 02), 405, 416, 328], [new Date(2025, 0, 02), 405, 416, 328], [new Date(2025, 0, 03), 421, 428, 301], [new Date(2025, 0, 03), 421, 428, 301], [new Date(2025, 0, 03), 421, 428, 301], ]);

Obviously I only want each date of data to appear only once, not 3 times. Recently had time to put in some more work into my Custom Home Automation System and wanted to add some metrics for devices in it.

Any help on how to build that final line would be greatly appreciated. It's SO close....

Let try to make this as simple as possible. I'm attempting to grab sensor records from my MySQL server. There is 10 sensors that have their values compiled to a table in my DB. Now for the sake of trying to make this easier lets just say 3 days of records lets start with Jan 1, 2025 since that's a weird thing as is when sending to Google Charts in this example:

sensorID | sensorValue | sensorDate
1 | 394 | 2025-01-01 00:01:00 (time is irrelevant as explained below)
2 | 432 | 2025-01-01 00:01:10
3 | 434 | 2025-01-01 00:01:15
1 | 405 | 2025-01-02 00:01:00
2 | 416 | 2025-01-02 00:01:10
3 | 328 | 2025-01-02 00:01:15
2 | 428 | 2025-01-03 00:01:10 <--- Yes this can happen if a sensor didn't respond on first check
1 | 421 | 2025-01-03 00:01:15 ----- However the YYYY-M-D is all I'm concerned about with ID.
3 | 301 | 2025-01-03 00:01:20 ----- Below the correct order is placed in the build of the array.

Now I'm pulling the data with the following Query:

$result0 = mysqli_query($conn, "SELECT DISTINCT sensorID, DATE_FORMAT(sensorDate, '%Y-%m-%d') as sensorDate_change, sensorValue FROM sensorRecords ORDER BY sensorDate, sensorID") ;

The above I believe will keep the value in order with the date and id. But anyways on to the real problem.

What I need to do is build the data.addRows for the chart. Now based on Googles wacky way of numbering the month this isn't a big deal, I can just extract the Y-m-d values to -1 the month since 0 is January. Yeah, that's weird.

Now what I need to do is pull the values for a date for (for this example 3 days) and format them for the data.addRows like below.

[new Date(2025, 0, 1), 394, 432, 434],  [new Date(2025, 0, 2), 405, 416, 328],  [new Date(2055, 0, 3), 421, 428, 301]

UPDATE:

Here is the code I have come up with but alas, I have a new problem. It repeats itself 3 times for each of the 3 records. I'm so close and going nuts on getting this to to work properly.

Code:

    <?php
    // include Global PHP
    include_once 'global.php' ;
    // include MySQL Database Configuration File
    include_once 'rcf_db.php' ;
    
    // Begin Google Chart Rows <<<<<<<<<<
     echo 'data.addRows([ ' ;

    // Grab Data from Sensor Records
    $result0 = mysqli_query($conn2, "SELECT DISTINCT DATE_FORMAT(sensorDate, '%Y-%m-%d') as date_change, sensorDate FROM sensorRecords ORDER BY sensorDate") ;
    
    while($row = mysqli_fetch_array($result0)) {
        $recordDate = $row['date_change'] ;
        $sensorDate = $row['sensorDate'] ;
        $dateValue = strtotime($recordDate) ;
        $year = date('Y',$dateValue) ;
        $monthNo = date('m',$dateValue) ;
        $monthNo = $monthNo - 1 ;
        $monthDay = date('d',$dateValue) ;
        
        // Build Google Chart Rows Part 1
        $build_part_1 = '[new Date('.$year.', '.$monthNo.', '.$monthDay.'),' ;
        
        // Build Google Chart Rows Part 2
        $SQL1 = "SELECT sensorValue FROM sensorRecords WHERE DATE_FORMAT(sensorDate, '%Y-%m-%d') = '".$recordDate."' ORDER BY sensorID;";
        $result1 = mysqli_query($conn2, $SQL1) ;
        
            // Do for Each Matching Date
            if(mysqli_num_rows($result1) > 1) {
                $strings = [] ;
                foreach($result1 as $row) {
                    $strings[] = $row['sensorValue'] ;
                }
            }
        
        // Echo Results
        $build_part_2 = implode(', ', $strings).'], ' ;
        echo $build_part_1.' '.$build_part_2.'' ;
    }
    
    // Close Google Chart Rows
    echo ']);' ;
    
    // Free Result Set
    mysqli_free_result($result0) ;
    mysqli_free_result($result1) ;
    
    // Close DB Connections
    $conn2->close() ;
    ?>

The Results: :(

data.addRows([ [new Date(2025, 0, 01), 394, 432, 434], [new Date(2025, 0, 01), 394, 432, 434], [new Date(2025, 0, 01), 394, 432, 434], [new Date(2025, 0, 02), 405, 416, 328], [new Date(2025, 0, 02), 405, 416, 328], [new Date(2025, 0, 02), 405, 416, 328], [new Date(2025, 0, 03), 421, 428, 301], [new Date(2025, 0, 03), 421, 428, 301], [new Date(2025, 0, 03), 421, 428, 301], ]);

Obviously I only want each date of data to appear only once, not 3 times. Recently had time to put in some more work into my Custom Home Automation System and wanted to add some metrics for devices in it.

Any help on how to build that final line would be greatly appreciated. It's SO close....

  • mysql
Share edited Feb 2 at 10:18 Your Common Sense 158k42 gold badges225 silver badges368 bronze badges asked Jan 31 at 4:04 Gregory MunsonGregory Munson 217 bronze badges 11
  • Your query is fine, you just need to loop over the results 3 at a time. See Looping Over Result Sets in MySQL for approaches. – David C. Rankin Commented Jan 31 at 6:48
  • While you can write this out in PHP, it may be easier to simply write the query result to a temporary text file and then call awk from php with shell_exec or exec. For example, processing the temp file from your query output (without the header), you can use awk 'd != $2 { split($2, arr, "-"); if (d) { printf "], " }; printf "[new Date(%s, %d, %d), %d", arr[1], arr[2]-1, arr[3]-0, $3; d = $2; next } { printf ", %d", $3 } END { print "]" }' file to produce [new Date(2025, 0, 1), 394, 432, 434], [new Date(2025, 0, 2), 405, 416, 328], [new Date(2025, 0, 3), 428, 421, 301] - option? – David C. Rankin Commented Jan 31 at 7:08
  • 1 For example with your data in the tst.sensors database, from the command line, you can simply do mariadb -Bse "SELECT DISTINCT sensorID, DATE_FORMAT(sensorDate, '%Y-%m-%d') as sensorDate_change, sensorValue FROM tst.sensors ORDER BY sensorDate, sensorID;" | awk 'd != $2 { split($2, arr, "-"); if (d) { printf "], " }; printf "[new Date(%s, %d, %d), %d", arr[1], arr[2]-1, arr[3]-0, $3; d = $2; next } { printf ", %d", $3 } END { print "]" }' to produce [new Date(2025, 0, 1), 394, 432, 434], [new Date(2025, 0, 2), 405, 416, 328], [new Date(2025, 0, 3), 428, 421, 301] – David C. Rankin Commented Jan 31 at 7:12
  • Otherwise, in PHP, you would iterate over the cursor, and the easiest way to produce the final line would simply be to use string-concatenation to build the string piece by piece similar to how it is processed in awk above. Loop and write the "[new Date(2025, 0, 1), value " part based on the first record, then while the date part doesn't change, just append the ", value" from each record. When the date changes start again with "[new Date(....), value " and repeat until the iteration is done and append the final "]". I leave the actual PHP to you. – David C. Rankin Commented Jan 31 at 7:21
  • Updated the Original Post with what I have come up with so far. It's SO close to being what I need. Thanks for any help! – Gregory Munson Commented Feb 1 at 5:58
 |  Show 6 more comments

1 Answer 1

Reset to default 1

In your SELECT DISTINCT DATE_FORMAT(sensorDate, '%Y-%m-%d') as date_change, sensorDate FROM sensorRecords query,
remove the , sensorDate (and the corresponding PHP variable).

The DISTINCT applies to the whole result row (not only the immediately following field), so if you do DISTINCT on a day + a full timestamp until seconds, the different seconds number will return 3 different results for the same day. That's why you loop 3 times over each day.

转载请注明原文地址:http://anycun.com/QandA/1744881360a88947.html

mysqlGetting multiple values for a date for Google ChartsStack Overflow