How to increase datatables Column Limit of 12

cpshart

Hi

I am producing a table for payments in the 12 months of the year Jan - Dec, but need to include 2 further columns for share and portfolio, making a total of 14 columns, using Wordpress PHP snippet and MySQL tables.

The table is being limited to 12 columns, is there a way to remove this limitation.

Extracts of the code shown below

<table id="pay-grid" class="" style="width:100%">

<thead>
<tr>
<th>Group</th>
<th>Code</th>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>Jun</th>
<th>Jul</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
</tr>
</thead> ... etc

foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->Group</td>";
    echo "<td>$row->Code</td>";
    echo "<td>$row->Jan</td>";
    echo "<td>$row->Feb</td>";
    echo "<td>$row->Mar</td>";
    echo "<td>$row->Apr</td>";
    echo "<td>$row->May</td>";
    echo "<td>$row->Jun</td>";
    echo "<td>$row->Jul</td>";
    echo "<td>$row->Aug</td>";
    echo "<td>$row->Sep</td>";
    echo "<td>$row->Oct</td>";
    echo "</tr>";}
echo "</table>";
?>
<script type="text/javascript"   src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/buttons/1.5.2/js/dataTables.buttons.min.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.flash.min.js"></script>
<script type="text/javascript"   src="https://cdn.datatables.net/buttons/1.5.2/js/buttons.print.min.js"></script>
<script type="text/javascript">
(function($) { .....etc.

This tables works, but as soon as I add Nov, Dec this takes the system > 12 columns, and simply ignores those extra columns in the output.

See attachment for the output, I can provide more code if you need it.

Many Thanks

Colin

kthorngren

First I would validate the HTML to make sure the Nov and Dec columns are actually added to the table. If you are not using columns.data then Datatables will use what it finds in HTML. This would indicate something might not be right with how you are adding the additional columns.

If you are using columns.data then you will need to add the additional columns there. If you are using columns.data and the number of columns in HTML doesn't match the number defined with columns.data should result in errors. Check your browser's console.

Its hard to say without actually seeing it.

Kevin

cpshart

Hi Kevin

Thanks, I think there is an issue with my SQL query, the code I sent did not include the Nov, Dec in the HTML, as this was my working code, sorry for confusion.

You have given me some ideas to work on.

I am just testing on PHPMyAdmin, I am not using columns.data for this datatable.

I will get back to you after my investigations, many thanks

Cheers Colin

cpshart

Hi Kevin

I have solved this problem, the issue was that Dec I think is a reserved word, so the system was preventing the build of the datatable beyond and including the Dec column, changing its column name to xDec i.e. anything other than Dec solved the problem.

Thanks for your help.

Regards

Colin

allan

Out of interest what is the $row an instance of? A PDO object, or something else?

Allan

cpshart

Hi Allan

Sorry for delay in replying, I am using datatables.net within Wordpress and this is the top snippet of my query. I may have to change all my queries before I go live to PDO objects to avoid SQL injection in the future.

<?php
global $wpdb;    
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;

$rows = $wpdb->get_results("
select  
t.Symbol AS Symbol
... etc
, (
    max(Janx) 
  + max(Febx) 
  + max(Marx) 
... etc
    ) AS Total
  , max(Janx) AS Janx
  , max(Febx) AS Febx
  , max(Marx) AS Marx
.. etc
from  (
  SELECT
  ANY_VALUE(s.symbol) AS Symbol,
... etc
  IFNULL((case when ANY_VALUE(DATE_FORMAT(d.pdate,'%b')) = 'Jan' then CEILING((SUM(IF(t.transaction_type='Buy',t.quantity,t.quantity * -1)) * ANY_VALUE(d.dividend) ) / 100) end),'') AS Janx,
... etc

Regards

Thanks Colin