Using DataTables for the first time - help needed
Using DataTables for the first time - help needed
RAWaddell
Posts: 42Questions: 5Answers: 0
I'm trying to switch a table in a search result to use DT but it looks nothing like what it should - I get the results populated in the table OK, but it just looks like a non-styled table.
HTML
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="js/logic_DataTables_prototype_v1.0.0.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.js"></script>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script>
$(document).ready( function () {
$('#show-entries').DataTable( {
data: dataSet,
columns: [
{ title: "Select" },
{ title: "ID" },
{ title: "Year" },
{ title: "First_Name" },
{ title: "Last_Name" },
{ title: "Entry #" },
{ title: "Barcode" },
{ title: "Entrant Name" },
{ title: "Title of Entry" },
{ title: "Category" },
{ title: "Paid" },
{ title: "Date Paid" },
{ title: "Date Created" },
{ title: "Print" },
]
} );
$('#show-entries').DataTable( {
"columnDefs": [
{
"targets": [ 3 ],
"visible": false,
"searchable": false
},
{
"targets": [ 4 ],
"visible": false
},
{
"targets": [ 5 ],
"visible": false
}
]
} );
} );
</script>
</head>
<body>
...
<table id="show-entries" class="display" style="width:90%" >
<tr class="tbhead">
<th> Select </th>
<th> ID </th>
<th style="display:none"> Year </th>
<th style="display:none"> First_Name </th>
<th style="display:none"> Last_Name </th>
<th> Entry # </th>
<th> Barcode </th>
<th> Entrant Name </th>
<th> Title of Entry </th>
<th> Category </th>
<th> Paid </th>
<th> Date Paid </th>
<th> Date Created </th>
<th> Print </th>
</tr>
</table>
</body>
</html>
JS
$(document).ready(function() {
var check="checklist";
var unCheck ="unchecked-list";
var cantCheck ="cantCheck";
//select all button
$('#selectall').click(function() {
if(this.checked) {
//checked here
$('#show-entries .unchecked-list').prop('checked', true);
$('#show-entries .unchecked-list').attr('class', 'checklist');
}
else {
//unchecked here
$('#show-entries .checklist').prop('checked', false);
$('#show-entries .checklist').attr('class', 'unchecked-list');
}
});
//checking checkbox
$(document).on('change','.checklist, .unchecked-list',function() {
if(this.checked) {
//checked here
$(this).attr('class', 'checklist');
}
else {
//unchecked here
$(this).attr('class', 'unchecked-list');
}
});
$('#formsearch').click(function() {
//getting values of text boxes
var contestYear= $('#contest-year').val();
var entryNumber= $('#entry-number').val();
var barCode= $('#barcode').val();
var firstName= $('#first-name').val();
var lastName= $('#last-name').val();
var title= $('#title-name').val();
var isPaid = $('input[name=paidStatus]:checked').val();
//remvoing previous row(s)
$('.child').remove();
$.ajax({
type: "POST",
url: "php/searchreceive.php",
data:{"c_year": contestYear, "e_number": entryNumber, "bCode":barCode, "fName":firstName, "lName": lastName, "title":title, "isPaid":isPaid}
}).done(function(status) {
status=status.trim();
if(status=="InvalidSession"){
// Redirect to logout page
window.location.href='../common/php/logout.php';
}
if(status=="NoResults") {
alert("No records found - please try again.");
}else {
var result = JSON.parse(status);
var p;
var paidOp;
for(var i=0; i<result.length; i++) {
// Loop through each record in 'status'
//getting date paid
var datePaid =result[i]["DatePaid"];
if(datePaid==null) {
datePaid = "";
}
//getting yes/no for paid
p = result[i]["Paid"].trim();
if(p==1) {
paidOp="Yes";
}
else {
paidOp="";
//datePaid="";
}
//getting date created
var dateCreated =result[i]["DateCreated"];
if(dateCreated==null) {
dateCreated = "";
}
//process sring length of Entrant_Name & Model_Name
var entrantName =result[i]["Entrant_Name"];
var n = entrantName.length;
if(n>30) {
entrantName =entrantName.substring(0, 27);
entrantName =entrantName.concat('...');
}
var modelName =result[i]["Model_Name"];
var n = modelName.length;
if(n>30) {
modelName =modelName.substring(0, 27);
modelName =modelName.concat('...');
}
$('#show-entries').append('<tr class="child"><td ><input type="checkbox" class='+unCheck+' id='+result[i]["ID"]+'></td>\
<td>'+result[i]["ID"]+'</td>\
<td style="display:none">'+result[i]["Year"]+'</td>\
<td style="display:none">'+result[i]["First_Name"]+'</td>\
<td style="display:none">'+result[i]["Last_Name"]+'</td>\
<td>'+result[i]["Entry_Form_Number"]+'</td>\
<td>'+result[i]["Barcode_Text"]+'</td>\
<td>'+entrantName+'</td>\
<td>'+modelName+'</td>\
<td>'+result[i]["Category_Name"]+'</td>\
<td>'+paidOp+'</td>\
<td>'+datePaid+'</td>\
<td>'+dateCreated+'</td>\
<td><input type="button" class="btn-print printrec" value="Print"/></td>\n\
</tr>');
//checking paid or not and disabling checkbox if FALSE
if(result[i]["Paid"]==1) {
//disabling unpaid checkboxes
$('#'+result[i]["ID"]).prop('disabled', true);
//changing classs name of unchecked
$('#'+result[i]["ID"]).attr('class', 'cantCheck');
}
}
// Add click handler to print button(s)
addClickHandlers('.printrec');
}
});
});
});
From Chrome's console:
Uncaught Error: Bootstrap's JavaScript requires jQuery
at bootstrap.min.js:6
logic_DataTables_prototype_v1.0.0.js:193 Uncaught TypeError: $(...).querySelector is not a function
at logic_DataTables_prototype_v1.0.0.js:193
searchEntries_DataTables_prototype_v1.0.0.php:272 Uncaught ReferenceError: dataSet is not defined
at HTMLDocument.<anonymous> (searchEntries_DataTables_prototype_v1.0.0.php:272)
at fire (jquery-1.12.4.js:3232)
at Object.fireWith [as resolveWith] (jquery-1.12.4.js:3362)
at Function.ready (jquery-1.12.4.js:3582)
at HTMLDocument.completed (jquery-1.12.4.js:3617)
Which doesn't make sense to me - I have JQuery in my <head> section. I would really appreciate some help getting started using DT.
Note: I tried to upload screenshots of before and after but this site says they failed to load.
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
you need to swap your script includes. Bootstrap requires jquery so you have to load jquery first. You have it second.
it also looks like you are loading jquery twice.
Order matters.
Thanks bindrid. I did swap the order and removed the other JQuery line but I'm still getting errors:
From the console:
the next problem I see is $('#show-entries').DataTable twice, one right after the other. You can only declare a table once, so you need to combine them.
Also, the first column is column 0. It looks like you might be treating it as column 1.
You page is long and complicated So I would start over and add the code in parts starting with the includes and just displaying the table.
Then add each part and make sure it works before moving on to the next part
Should I combine the sections on [columns] with [columnDefs] under one #show-entries? I did try the simple JavaScript example from https://datatables.net/examples/data_sources/js_array.html and it works just fine (with hardcoded data, of course)
I changed the <script> tag in my header to this:
and I don't get the above errors, although I do see this in the console:
which is weird, since line 288 is this:
But, the resulting table still doesn't have any of the DT functionality.
That suggests that it is parsing CSS as Javascript perhaps? Without a link to a test case it is impossible to say for sure.
ALlan
I've got it partially working with no more console errors, but only some of the DT functionality is showing ("Show 10 entries", "Search", column sorting). The list shows all records returned, not just the first 10. The first row of the table says, "No data available in table" although I can see the records. Can anyone help me with the next step?
HTML
JS
You are adding the data directly to the table using jQuery append() instead of using Datatables to add the data. Datatables has no knowledge of the data that was added. The first thing to do is remove this line from your Datatables init code:
data: dataSet,There are a few options to let Datatables know about the data added to the table. The first and probably simplest is to move the Datatables init code into the
donefunction of the ajax request. Place it after the for loop adds all the data to the table. Would look something like this:Another option is to use
row.add()instead of$('#show-entries').append()in the for loop. This example shows how to add rows withtrtags:http://live.datatables.net/yokuriku/1/edit
Additionally you would use
columns.renderto handle thechecking paid or not and disabling checkbox if FALSEportion of the code.Kevin
I think the problem is that I need to include the Ajax call to get the data inside $('#show-entries').DataTable(){}, but the complication is that the Ajax call is triggered by a Search button on my form. I don't know how to get the Ajax call working inside the $('#show-entries').DataTable(){} but triggered by the button click.
EDIT
Looks like I posted my comment at the same time as Kevin did his.
Thanks Kevin - I will try your suggestions.
In that case you don't want to move the init code into the ajax call. In this case you could try using
rows().invalidate(). After the for loop try using$('#show-entries').DataTable().rows().invalidate().draw(). This should tell Datatables to read the table data and update it's cache.Or you could use the
row.add()api like I described above.Kevin
Moving the DT init to the Ajax done function solved the "No data available in table", but that's it. I'll have to try Kevin's other suggestions.
If you use the ajax call more than once you will get the Cannot reinitialize Datatables error. You will need to add the
retrieveto keep from getting this error.If you post an example of your data,
dataSet = JSON.parse(status);, then we may be able to provide you an example that you can start from. We can probably show you an easier way to make this work. Processing what is indatasetshould be easy but not sure where data likeentrantNameis coming from. Just a couple rows of data should suffice.Kevin
Kevin - I can get the raw MySQL results as an example of what's returned to the Ajax call, but if you want to see exactly what it looks like when it gets to the JS then I don't know how to do that.
After this line:
dataSet = JSON.parse(status);use this command:
console.log(dataSet);It should display in your browser's console.
Kevin
Thanks Kevin. Hopefully, this is the info you need:
HTML
JS
Console Output
Array of records returned
BTW, 'Entrant Name' is a concatenation of first_name and last_name done in SQL:
I've put together a basic example with your data. I included some of the formatting options but not everything. Here is the example:
http://live.datatables.net/vinipiye/1/edit
It starts by initializing a blank Datatable. Then click the "Load Data" button and it will load the data. This would simulate your ajax call by clearing the table then using
rows.add()to add the data. You will want to read up on the following topics that I used in the example:Modifying the original data for display in the table:
columns.renderorthogonal data
Ellipsis renderer
Clearing and updating the table data:
clear()rows.add()draw()Additionally you may want to read the Datetime sorting blog to handle your date column sorting. You might not need this.
Hope this gets you started. Please feel free to update the example to workout how to build your table. Post further questions.
Kevin
Additionally your ajax done function probably only needs to be this (lets have DT do all the work):
I also added code to help you get the row clicked with the "print" button. It uses
meta.rowin the render function to get Datatalbes row ID and adds that as theidfor the button in each row. The the click event shows you how to get the clicked row.Kevin
Hi Kevin - THANK YOU! I'm going to try out your sample code and I'll post back here what I ended up with.
BTW, live.datatables.net/vinipiye/1/edit doesn't work for me in Safari - I see errors at the bottom of the JS section and nothing happens when I click 'Load table'
EDIT
I don't see anything in your code above for this:
I also added code to help you get the row clicked with the "print" button. It uses meta.row in the render function to get Datatalbes row ID and adds that as the id for the button in each row. The the click event shows you how to get the clicked row.
Is that on your sample page?
Sorry, something happened while I was messing with the example. This should work better:
http://live.datatables.net/vinipiye/2/edit
Kevin
Thanks! I've got your sample working on my local dev server - now on to my page!!
Quick question - for my 'Print' button I only need certain column data (as I pass them to the print page via GET), not all - is there a way to get only specific column values?
You could do something like this:
This would get the Last_Name and Entry_Form_Number data for the row. The example should be updated to show this.
Kevin
Success! I got the Search, Reset and Print buttons working - just need to implement the 'Select all' and 'Process Checked as Paid' buttons. Also, fix the table width as it seems to auto adjust outside my container border.
Thank you very much, Kevin. Rarely have I seen someone take the time to help a newbie like you have - very much appreciated.
Is there an easier way to find all selected rows (i.e. the left-most checkbox column)? In the previous implementation, it went like this::
JS
Glad you got it working.
You may want to look at the Select extension for your checkboxes. Its pretty easy to get the selected rows and to select all rows using the API. Or maybe the Datatables Checkboxes plugin would be useful for you.
The general recommendation is to use
style="width:100%"for Datatables to properly calculate the widths. Not sure if that is your issue.I noticed you are using bootstrap. You can use the Datatables bootstrap styling includes to style the table using bootstrap. Here is the example:
https://datatables.net/examples/styling/bootstrap.html
You can get the files here:
https://datatables.net/download/index
Sounds like a busy weekend for you
Kevin
But a fun weekend! I love learning new stuff.
I changed the table to 100% but am using class="display compact" - that is working perfectly; no more bleed outside my container.
Replacing the initial checkbox field with the DT one has produced an odd effect - either I see two checkboxes in the same column, or this:
HTML
js/logic_DataTables_prototype_v1.0.3.js
The two checkboxes are seen if I have this instead:
Actually, this seems to work - does this make sense?