data.addRows正确的数据格式

Question

I´ve become desperate looking for a solution how to handle this situation.

I´m trying to create a google chart table, where the number of columns are automatically updated each year.

Procedure in SQL adds now column each year and counts all the numbers, starting in 2006, webpage shows results in google chart table and charts.

Problem:

Dynamically add each new year a new column (solved).

Dynamically add a row to an array for each year.

Correct data format.

Procedure in SQL adds now column each year and counts all the numbers, starting in 2006.

SQL TABLE:

ID  Type    Month O2015 O2016   O2017   O2018   O2019
1   1       1     4346  3180    3409    4345    857

PHP Select:

$sqltest1= "SELECT  Month, O2006, O2007, O2008 FROM TESTOVACI100 WHERE Type = 1;";
$test1= sqlsrv_query($conn, $sqltest1);

Google chart code:

function drawSecondTable() {
    var data = new google.visualization.DataTable();  
    var d = new Date();
    var n = d.getFullYear();

    //Funguje
    var columns = '<?php echo $column_count ;?>'; 

/*
    var showdata = <?php
                while($row = sqlsrv_fetch_array($obratmesice, SQLSRV_FETCH_ASSOC)){
                    echo "['".$row["Month"]."', ".$row["O2006"].", ".$row["O2007"].", ".$row["O2008"]."],";}                                             
            ?>   
*/

data.addColumn('number', 'Month');  
for (var i = 2006; i < n + 1; i++){
    data.addColumn('number', 'O' + i);}     

Data.addRow(showdata);   

    var table = new google.visualization.Table(document.getElementById('table_div2'));     
    table.draw(data, {showRowNumber: false, width: '100%', height: '100%'});
  }

As you can see in a commented part of the code, I´m adding one column manually - month, the rest of them is being added automatically due to the actual year.

My question is... how to automatically add rows with data?

I´ve tried one approach in the commented section of the code, but I´m unsure, how to do that without adding a new row each year manually.

Is there a way, how to solve this with some loop?

Thanks, everyone for answers.

Answer 1

assuming $column_count does not include the "Month" column,
you can loop each index of $row as follows...

    var showdata = <?php
    while($row = sqlsrv_fetch_array($obratmesice, SQLSRV_FETCH_ASSOC)){
      echo "[".$row["Month"].", ";
      for ($i = 0; $i < $column_count; $i++) {
        echo $row["O".($i + 2006)].", ";
      }
      echo "];";
    }
    ?>

be sure data is lowercase here...

data.addRow(showdata);

with multiple rows...

    var showdata = [<?php
    while($row = sqlsrv_fetch_array($obratmesice, SQLSRV_FETCH_ASSOC)){
      echo "[".$row["Month"].", ";
      for ($i = 0; $i < $column_count; $i++) {
        echo $row["O".($i + 2006)].", ";
      }
      echo "],";
    }
    ?>]; 

    data.addRows(showdata);