I am using the Datatables jquery plugin for one of my projects. For one in particular, the number of columns can vary based on how many children a consumer has (yes I realize normalization and proper technique would insert on another row but it is a client requirement).
Datatables must be set up as such:
<table>
<thead>
<tr>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td></td>
</tr>
</tbody>
</table>
my script starts out as:
<table cellpadding="0" cellspacing="0" border="0" class="display" id="sortable">
<thead>
<tr>
<th>parent name</th>
<th>parent phone</th>
<?php
try {
$db->beginTransaction();
$stmt = $db->prepare("SELECT max(num_deps) FROM (SELECT count(a.id) as num_deps FROM children a INNER JOIN parents b USING(id) WHERE a.id !=0 GROUP BY a.id) x");
$stmt->execute();
$rows = $stmt->fetchAll();
for($i=1; $i<=$rows[0][0]; $i++) {
echo "
<th>Child Name ".$i."</th>
<th>Date of Birth ".$i."</th>
";
}
$db->commit();
}
catch (PDOException $e)
{
echo "<p align='center'>There was a system error. Please contact administration.<br>".$e->getMessage()."</p><br />";
}
?>
</tr>
</thead>
In this manner, the final column headers can be 1 or 50 spots long. However, with this dynamic code in place, datatables throws the following error:
""DataTables warning (table id =
'datatable'): Cannot reinitialise
DataTable. To retrieve the DataTables
object for this table, please pass
either no arguments to the dataTable()
function, or set bRetrive to true.
Alternativly, to destroy old table and
create a new one...ETC."'
Yes I have set "bRetrieve" : true in the javascript above and that does not do the trick. If I remove the code above, the file "works" fine but it leaves off the necessary columns for my table.
Any ideas?
Displaying JS
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/jquery-ui.min.js"></script>
<script type="text/javascript" src="../media/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="../media/js/TableTools/TableTools.js"></script>
<script type="text/javascript" src="../media/ZeroClipboard/ZeroClipboard.js"></script>
<script type="text/javascript">
$(document).ready(function() {
TableToolsInit.sSwfPath = "../media/swf/ZeroClipboard.swf";
oTable = $('#sortable').dataTable({
"bRetrieve": true,
"bProcessing": true,
"sScrollX": "100%",
"sScrollXInner": "110%",
"bScrollCollapse": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sDom": 'T<"clear"><"fg-toolbar ui-widget-header ui-corner-tl ui-corner-tr ui-helper-clearfix"lfr>t<"fg-toolbar ui-widget-header ui-corner-bl ui-corner-br ui-helper-clearfix"ip>'
});
});
</script>
</head>
TOP piece of HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Home</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" />
<link rel="stylesheet" type="text/css" href="default.css" />
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
<style type="text/css" title="currentStyle">
@import "TableTools.css";
@import "demo_table_jui.css";
@import "jquery-ui-1.8.4.custom.css";
</style>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/jquery-ui.min.js"></script>
<script type="text/javascript" src="js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="js/TableTools/TableTools.js"></script>
<script type="text/javascript" src="ZeroClipboard/ZeroClipboard.js"></script>
<script type="text/javascript">
$(document).ready(function() {
TableToolsInit.sSwfPath = "ZeroClipboard.swf";
oTable = $('#sortable').dataTable({
"bRetrieve": true,
"bProcessing": true,
"sScrollX": "100%",
"sScrollXInner": "110%",
"bScrollCollapse": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sDom": 'T<"clear"><"fg-toolbar ui-widget-header ui-corner-tl ui-corner-tr ui-helper-clearfix"lfr>t<"fg-toolbar ui-widget-header ui-corner-bl ui-corner-br ui-helper-clearfix"ip>'
});
});
</script>
</head>
<body bgcolor="#e0e0e0">
<div class="main">
<div class="
body">
<div class="body_resize">
<div class="liquid-round">
<div class="top"><span><h2>Details</h2></span></div>
<div class="center-content">
<div style="overflow-x:hidden; min-height:400px; max-height:600px; overflow-y:auto;">
<div class="demo_jui"><br />
<table cellpadding="0" cellspacing="0" border="0" class="display" width="100%" id="sortable">
<thead>
<tr>
<th>First Name</th>
<th>MI</th>
<th>Last Name</th>
<th>Street Address</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
<th>DOB</th>
<th>Gender</th>
<th>Spouse Name</th>
<th>Spouse Date of Birth</th>
<!-- this part is generated with the php, when removed, datatables works just fine with the rest of the page -->
<th>Dependent Child Name 1</th>
<th>Dependent Date of Birth 1</th>
<th>Dependent Child Name 2</th>
<th>Dependent Date of Birth 2</th>
<th>Dependent Child Name 3</th>
<th>Dependent Date of Birth 3</th>
<th>Dependent Child Name 4</th>
<th>Dependent Date of Birth 4</th>
<th>Dependent Child Name 5</th>
<th>Dependent Date of Birth 5</th>
<th>Dependent Child Name 6</th>
<th>Dependent Date of Birth 6</th>
<th>Dependent Child Name 7</th>
<th>Dependent Date of Birth 7</th>
</tr>
</thead>
<tbody>
<tr> ...
UPDATE REGARDING COMMENTS/ANSWERS
I have received a number of responses indicating the number of headers may not match the field count in the
body. As I mention below, eliminating the php script below altogether would eliminate 5+ fields in the header and without question throw the count match off balance. This DOES NOT however cause an error and in fact "resolves" the issue in that datatables functions properly (even though there is NO header record for 5+ fields in the
body.
