read datatable rows values to pass to google charts

cpshart · August 2020

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

I am trying to get the column values for all rows created in the datatable using the following excerpt of code, I have added the majority of client code further below. The system is also available to check on my website if required. I intend to use the data to pass to the Google charts engine.

    var data = table
    .rows()
    .data();
 
alert( 'The table has '+data.length+' records' );  


  // loop table rows
  table.rows({ search: "applied" }).every(function() {
    var data = this.data();
    var cell = table.cell(this.index(), 4).render('display');
console.log('cell value:'+cell);
console.log('data:'+data);
  }

The first alert returns 0

I am not however getting any data being returned, but it is displaying in the datatable and JSON from the server.

Let me know if you require access, as the site is password protected.

run the script here

https://www.dividendlook.co.uk/holdings-by-shareh/

client file

https://www.dividendlook.co.uk/wp-admin/post.php?post=26220&action=edit

main part of client script

<tr>
<th>Symbol</th>
<th>Name</th>
<th>Quantity</th>
<th>Price</th>
<th>Value</th>
</tr>
    
</tfoot>
    
<tbody>     
<?php
global $wpdb;    
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;
    
$rows = $wpdb->get_results("
SELECT 
        s.symbol AS symbol,
        s.name AS name, 
        SUM(ANY_VALUE(h.quantity)) AS quantity,
        s.price AS price,
        IF(s.currency='GBX',(SUM(ANY_VALUE(h.quantity)) * ANY_VALUE(s.price) / 100)
        ,(SUM(ANY_VALUE(h.quantity)) * ANY_VALUE(s.price))) AS value
        
        FROM
        dm_holdings h
        INNER JOIN dm_stocks s ON (s.id = h.stock_id) 
        INNER JOIN dm_portfolios p ON (p.id = h.portfolio_id)
        
        WHERE
        h.user_id >= IF(%CURRENT_USER_ID%=4,2,%CURRENT_USER_ID%)
        AND
        h.user_id <= IF(%CURRENT_USER_ID%=4,3,%CURRENT_USER_ID%)
        AND
        p.reporting_status = 'yes'
        GROUP BY
        s.symbol
");
        
foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->symbol</td>";
    echo "<td>$row->name</td>";
    echo "<td>$row->quantity</td>";
    echo "<td>$row->price</td>";
    echo "<td>$row->value</td>";
    echo "</tr>";
}
    

<?php
>

        

?>


<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>
    
<script type="text/javascript">
(function($) {
 
$(document).ready(function() {
var table = $('#holdings-by-shareh').DataTable( { 
        orderCellsTop: true,
        fixedHeader: true,
    
    
        ordering: true,
        dom: "lBfrtip",
        "scrollY":      true,
        "scrollX":      true,
        ajax: {
        url:    "../../Editor-PHP-1.9.4/controllers/holdings_by_shareh.php",
        type: "post",
        data: function(d) {
            d.userid = $('#passuserid').val();
            }
        },
        columns: [
            { data: "dm_stocks.symbol" },
            { data: "dm_stocks.name" },
            { data: "dm_holdings.quantity" },
            { data: "dm_stocks.price" },
            { title: "Value", data: null,
                render: function ( data, type, row ) {
                    return (row.dm_stocks.currency == 'GBX')
                    ? (( Number(row.dm_stocks.price) * Number(row.dm_holdings.quantity) )  / 100 ).toFixed(2)
                    : ( Number(row.dm_stocks.price) * Number(row.dm_holdings.quantity) ).toFixed(2)
                }
            }
        ],
        columnDefs: [ // apply dt-nowrap to specific columns
          { className: "dt-nowrap", "targets": [ 1 ] }
    ]
} );            
    
    var data = table
    .rows()
    .data();
 
alert( 'The table has '+data.length+' records' );  


  // loop table rows
  table.rows({ search: "applied" }).every(function() {
    var data = this.data();
    var cell = table.cell(this.index(), 4).render('display');
console.log('cell value:'+cell);
console.log('data:'+data);
  } 
    
} );    
    
}(jQuery));</script>

Thanks for any help.

Colin

kthorngren · August 2020

var data = table
.rows()
.data();

This is executing before Datatables has completed initialization. Meaning the Ajax request is asynchronous and the Javascript does't stop waiting on the response. You can put the code into initCompelte or use a button click event to show the data. Basically you need to wait until Datatables has completed initialization before accessing the row data.

Kevin

cpshart · August 2020

Hi Kevin

Many thanks for your very quick response, it now makes sense, I will integrate into my code and try and get the charts working !!

Best regards

Colin

cpshart · August 2020

Hi Kevin

Your solution has worked, with thanks and I am now trying to format the data to the required format for google charts by enclosing each x, y axis pair in square brackets followed by a comma of the form (including a x and y title) as follows

[symbol, value],
[GSK.LSE, 15212],
[HAS.LSE,11900],
[HSTG.LSE,10088],
[LAND.LSE,11472]

client code generating data for google chart

  table.rows({ search: "applied" }).every(function() {
    var data = this.data();
    var cell = table.cell(this.index(), 4).render('display');

    symbolArray.push(data.dm_stocks.symbol);
    valueArray.push(parseFloat(cell));  
      
    dataArray.push([data.dm_stocks.symbol, parseFloat(cell)]);

This part of code below

dataArray.push([data.dm_stocks.symbol, parseFloat(cell)]);

produces the data as follows

GSK.LSE,15212,HAS.LSE,11900,HSTG.LSE,10088,LAND.LSE,11472

Problem
I need it to enclose the data in square brackets and add a comma between each row of data, and add a title for each axis as shown.

this extract of code works for producing a static google chart, I have commented out my section referencing my array which will replace the static section above when working

    google.charts.load("current", {packages:['corechart']});
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        
      var data = google.visualization.arrayToDataTable([
        ["Element", "Density", { role: "style" } ],
        ["Copper", 8.94, "#b87333"],
        ["Silver", 10.49, "silver"],
        ["Gold", 19.30, "gold"],
        ["Platinum", 21.45, "color: #e5e4e2"]
      ]);
/*
      var data = google.visualization.arrayToDataTable([
        dataArray
      ]);
*/              
      var view = new google.visualization.DataView(data);
      view.setColumns([0, 1,
                       { calc: "stringify",
                         sourceColumn: 1,
                         type: "string",
                         role: "annotation" },
                       2]);

      var options = {
        annotations: {
          stem: {
            color: 'transparent'
          },
          style: 'line'
        },
        title: "Holdings By Share",
        width: 1500, // original 600
        height: 350, // original 400
        bar: {groupWidth: "95%"},
        legend: { position: "none" },
      };
      var chart = new google.visualization.ColumnChart(document.getElementById("columnchart_values"));
      chart.draw(view, options);
  }

any help much appreciated.

Thanks

Colin

kthorngren · August 2020

dataArray.push([data.dm_stocks.symbol, parseFloat(cell)]);

You are using a standard Javascript method to build the array, nothing Datatables specific. You are pushing an array of two elements onto the array so it should result in the array of arrays you want.

produces the data as follows
GSK.LSE,15212,HAS.LSE,11900,HSTG.LSE,10088,LAND.LSE,11472

Where and how are you looking at this? Are you using a console.log statement right after the dataArray.push([data.dm_stocks.symbol, parseFloat(cell)]); statement?

Can you post a link to your page so we can take a look?

Kevin

cpshart · August 2020

Hi Kevin

I have PM'd you access to my site before, let me know if you need it again ?

run the script here

https://www.dividendlook.co.uk/holdings-by-shareh/

edit client script here

https://www.dividendlook.co.uk/wp-admin/post.php?post=26220&action=edit

Yes the console.log statement is shown below

    dataArray.push([data.dm_stocks.symbol, parseFloat(cell)]);
      
  console.log('1. The symbolArray is:'+symbolArray);
  console.log('1. The dataArray is:'+dataArray);
  });
 
// store all data in dataArray
  console.log("2.The dataArray is :"+dataArray);

Many Thanks

Colin

kthorngren · August 2020

Using a debugger breakpoint I can see the array being built properly:

console.log("2.The dataArray is :"+dataArray);

This produces a commas separated representation of the array. Not the actual array. I did something similar here:
http://live.datatables.net/gonicuyi/1/edit

Is there a problem you are trying to fix or is it just the output is incorrect?

Kevin

cpshart · August 2020

Hi Kevin

Thanks for looking at this, I see from your debugger that the data is in the correct format i.e. includes square brackets, I assume the output from console.log does not display the brackets, something I was unaware of, so I wrongly assumed the data was incorrect, sorry !!.

I need the data in this format to pass to the Google Chart engine, which I should now be able to complete the code.

Best regards.

Colin

kthorngren · August 2020

If you look at the example I provided it shows to use a comma instead of a plus in the console.log statement.

Kevin

cpshart · August 2020

Hi Kevin

Sorry I have been out, that is useful to know, I have just tested console.log with a comma and it shows the square brackets so I see the data is correct, I am still trying to get it to produce a google chart, do you have an example using datatables data. I must be very close..

I am getting an error

Data column(s) for axis #0 cannot be of type string×

which suggests that the numeric data (see attached) is being interpreted as a string

storing value as Float (tried parseInt, same error)

    dataArray.push([data.dm_stocks.symbol, parseFloat(cell)]);

extract of Google Chart code, resulting in error above

    google.charts.load("current", {packages:['corechart']});
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
/*      
      var data = google.visualization.arrayToDataTable([
        ["Element", "Density", { role: "style" } ],
        ["Copper", 8.94, "#b87333"],
        ["Platinum", 21.45, "color: #e5e4e2"]
      ]);
*/
  var data = new google.visualization.arrayToDataTable([
//        ["Symbol", "Value"],
    dataArray
//    false
      ]);  
        
      var view = new google.visualization.DataView(data);
      view.setColumns([0, 1,
                       { calc: "stringify",
                         sourceColumn: 1,
                         type: "string",
                         role: "annotation" },
                       2]);

if you have any ideas they would be welcome ..

Thanks Colin

kthorngren · August 2020

do you have an example using datatables data.

There is nothing special about Datatables data. The blue numbers in your screenshot indicate these are numbers not strings. The problem, I think, is this code:

  var data = new google.visualization.arrayToDataTable([  // this [
//        ["Symbol", "Value"],
    dataArray
//    false
      ]); // and this ]

dataArray is an array of arrays. I noted where you have extra set of []. Which results in this:

[
  [
    ["HAS.LSE", 11900],
    ["HSTG.LSE", 10088],
    ....
  ]
]

Remove the extra []. If you need to add ["Symbol", "Value"] to the beginning of the dataArray array then use unshift().

Kevin

cpshart · August 2020

Hi Kevin

Thanks for your quick response again, I am getting close to a solution for google charts, useful tips above.

I have just got highcharts working with datatables data using an array of arrays.

I will post the solution once done.

Best Colin

read datatable rows values to pass to google charts

read datatable rows values to pass to google charts

Answers

Howdy, Stranger!

Categories

DataTables

read datatable rows values to pass to google charts

read datatable rows values to pass to google charts

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables