Help converting to DataTables
Help converting to DataTables
MrBaseball34
Posts: 96Questions: 0Answers: 0
Ok, this is going to be a long one. I am working to convert our current course schedule listing to use a DataTable and having some difficulties understanding how to do a few things. We will be using server-side processing so an ajax call will be used and the server-side script will be written to return the correct data. The filtering, described below is the main thing I need help with. The other thing is how to correctly display the data when it comes back.
I have this query that returns data:
[code]
select e.ProgramCode,
e.ProgramGroup,
d.field_id_48 as Program_Group_Code,
e.EventCode,
t.title as Program_Title,
t.url_title as Program_URL_Title,
t2.url_title as Program_Group_URL_Title,
e.FormalDate,
e.LocCity,
e.LocState,
e.LocAddress,
e.LocName,
e.Price,
e.LicenseeURL,
e.GateKeeper_length,
e.SeatsAreAvailable,
zl.latitude as LocLatitude,
zl.longitude as LocLongitude
from exp_course_events e
inner join exp_channel_data d
on e.ProgramCode = d.field_id_41
inner join exp_channel_data d2
on d2.field_id_47 = d.field_id_48
inner join exp_channel_titles t
on d.entry_id = t.entry_id
inner join exp_channel_titles t2
on t2.entry_id = d2.entry_id
inner join exp_zip_lat_long zl
on zl.zip = e.LocZipCode
where d.channel_id = 26
order by e.StartDate, t.title, e.LocState, e.LocCity
limit 500
[/code]
I am essentially displaying only 3 of the columns returned. However, I am using several of the other columns to determine what to show in the table in a fnRowCallback.
I'm not sure how to setup the columns to only show the ones I want to show and how to correctly format the rest of the data that needs to be shown.
Here is the table structure:
[code]
Type
Date
Program
Cost
Information
[/code]
In the Type column, we first need to set the CourseType variable and then set the column data to the CourseType value:
[code]
if (event['LocState'] == 'OL') {
CourseType = "Online Course";
} else if (event['LocState'] == 'WB') {
CourseType = "Online Classroom";
} else {
CourseType = event['LocCity'] + ', ' + event['LocState'] ;
}
[/code]
In the Date column, we put the value of event['FormalDate'];
In the Program colum, we use the value of event['Program_Title']
In the Cost column, we just need the value of
'$' + event['Price']
In the Information column, we need to check the value of event['SeatsAreAvailable'] and event['LicenseeURL'] to determine what goes column, like this:
[code]
LimitedSeats = (parseInt(event['SeatsAreAvailable']) < 20&&event['LicenseeURL']==null);
HasWaitingList = (parseInt(event['SeatsAreAvailable']) <1&&event['LicenseeURL']==null);
if(parseInt(event['SeatsAreAvailable']) > 0 && LimitedSeats) {
cls = " yellow_btn";
txt = "Limited Seats";
hint = "Register soon, less than 20 seats left!";
} else {
if(HasWaitingList) {
cls = " red_btn";
txt = "Wait List *";
hint = "Click here to be placed on waiting list."
sold_out_count++; // used to show a div at the bottom of page giving more info on sold out courses
} else {
cls = " register_btn";
txt = "Register";
hint = txt;
}
}
[/code]
Then the values in the column are shown like this:
[code]
course_url = '/courses/' + event['Program_Group_URL_Title'] + '/' + event['Program_URL_Title'] + '/' + event['EventCode'];
column_data = 'View Details | ';
if(parseInt(event['SeatsAreAvailable']) > 0 && LimitedSeats) {
column_data += 'Limited Seats';
} else {
if(HasWaitingList) {
column_data += 'Wait List*';
} else {
column_data += 'Register';
}
}
[/code]
We allow the users to filter by Month, Year, State, City and Program Code as well as a miles radius from the selected city. How would I incorporate that into filtering for the DataTable?
I have this query that returns data:
[code]
select e.ProgramCode,
e.ProgramGroup,
d.field_id_48 as Program_Group_Code,
e.EventCode,
t.title as Program_Title,
t.url_title as Program_URL_Title,
t2.url_title as Program_Group_URL_Title,
e.FormalDate,
e.LocCity,
e.LocState,
e.LocAddress,
e.LocName,
e.Price,
e.LicenseeURL,
e.GateKeeper_length,
e.SeatsAreAvailable,
zl.latitude as LocLatitude,
zl.longitude as LocLongitude
from exp_course_events e
inner join exp_channel_data d
on e.ProgramCode = d.field_id_41
inner join exp_channel_data d2
on d2.field_id_47 = d.field_id_48
inner join exp_channel_titles t
on d.entry_id = t.entry_id
inner join exp_channel_titles t2
on t2.entry_id = d2.entry_id
inner join exp_zip_lat_long zl
on zl.zip = e.LocZipCode
where d.channel_id = 26
order by e.StartDate, t.title, e.LocState, e.LocCity
limit 500
[/code]
I am essentially displaying only 3 of the columns returned. However, I am using several of the other columns to determine what to show in the table in a fnRowCallback.
I'm not sure how to setup the columns to only show the ones I want to show and how to correctly format the rest of the data that needs to be shown.
Here is the table structure:
[code]
Type
Date
Program
Cost
Information
[/code]
In the Type column, we first need to set the CourseType variable and then set the column data to the CourseType value:
[code]
if (event['LocState'] == 'OL') {
CourseType = "Online Course";
} else if (event['LocState'] == 'WB') {
CourseType = "Online Classroom";
} else {
CourseType = event['LocCity'] + ', ' + event['LocState'] ;
}
[/code]
In the Date column, we put the value of event['FormalDate'];
In the Program colum, we use the value of event['Program_Title']
In the Cost column, we just need the value of
'$' + event['Price']
In the Information column, we need to check the value of event['SeatsAreAvailable'] and event['LicenseeURL'] to determine what goes column, like this:
[code]
LimitedSeats = (parseInt(event['SeatsAreAvailable']) < 20&&event['LicenseeURL']==null);
HasWaitingList = (parseInt(event['SeatsAreAvailable']) <1&&event['LicenseeURL']==null);
if(parseInt(event['SeatsAreAvailable']) > 0 && LimitedSeats) {
cls = " yellow_btn";
txt = "Limited Seats";
hint = "Register soon, less than 20 seats left!";
} else {
if(HasWaitingList) {
cls = " red_btn";
txt = "Wait List *";
hint = "Click here to be placed on waiting list."
sold_out_count++; // used to show a div at the bottom of page giving more info on sold out courses
} else {
cls = " register_btn";
txt = "Register";
hint = txt;
}
}
[/code]
Then the values in the column are shown like this:
[code]
course_url = '/courses/' + event['Program_Group_URL_Title'] + '/' + event['Program_URL_Title'] + '/' + event['EventCode'];
column_data = 'View Details | ';
if(parseInt(event['SeatsAreAvailable']) > 0 && LimitedSeats) {
column_data += 'Limited Seats';
} else {
if(HasWaitingList) {
column_data += 'Wait List*';
} else {
column_data += 'Register';
}
}
[/code]
We allow the users to filter by Month, Year, State, City and Program Code as well as a miles radius from the selected city. How would I incorporate that into filtering for the DataTable?
This discussion has been closed.
Replies
I'm not sure how to setup the columns to only show the ones I want to show and how to correctly format the rest of the data that needs to be shown.[/quote]
Each of your columns should have a or entry in your , but you can make them non visible in the DataTables initializer object. see http://www.datatables.net/ref#bVisible:
You can use the fnRender callback to process a given column to reformat the result. see http://www.datatables.net/ref#fnRender . The oObj passed into fnRender will let you access an array of the the data for the entire row, so your rendered cell can combine data from multiple fields in your db. This will be helpful for creating links that combine link text, href url, and maybe a title attribute.
for the mile radius, you'll need some code that computes distances between zip codes or addresses. this is out of the scope of datatables, but once you have the values converted to number, datatables can accept parameters to filter by that number.
The only question remaining is how I would send the zip code and miles in the call.
These are now the columns I am returning from the DB:
[code]
ProgramCode, ProgramGroup, Program_Title, EventCode, Course_URL, FormalDate,
LocCity, LocState, LocAddress, LocName, Price, LicenseeURL, GateKeeper_length,
SeatsAreAvailable, LocLatitude, LocLongitude
[/code]
Do the columns returned map to the columns in the DataTable by index or by name?
How would those be passed as well?
You can do it either way. The default/easy way is to map them in order, i.e. as ordered arrays of values that match up (table column 0 maps to db column 0). if you return your JSON using the object form of aaData, which uses column names as indexes rather than assumes ordered array, you can use mDataProp in aoColumns to tell DataTables which data property to put into that column. (you can also set mDataProp: null if you want a column that doesn't use db values.. consider setting sDefaultContent for null data props, since there's an issue in 1.8.1 with detecting null in that case.
see the aaData (array version) and aaData (object version) shown side-by-side under the heading "Example JSON return" on http://www.datatables.net/usage/server-side
Make a call to the google map api to compute distances (either using zip code or lat/long coordinates)
http://briancray.com/2009/04/01/how-to-calculate-the-distance-between-two-addresses-with-javascript-and-google-maps-api/
I asked how can I send the zip code and miles and the other parameters in my 2:03PM comment above to filter the DataTable?
assuming you have textboxes with id's "zipcode" and "miles". edit as needed.
[code]
/* POST data to server */
$(document).ready(function() {
$('#example').dataTable( {
"sAjaxSource": "data_source.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some data to send to the source */
aoData.push( { "name": "zip", "value": $('#zipcode').val() } );
aoData.push( { "name": "miles", "value": $('#miles').val() } );
$.ajax( {
"dataType": 'json',
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
} );
} );
[/code]
(Using the scheduleTable table defined above)
[code]
$('#scheduleTable').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/files/schedule_filter.php",
"aoColumns": [null, /* Location */
null, /* FormalDate */
null, /* Program_Title */
null, /* Price */
null, /* Information */
{ "bVisible": false }, /* ProgramCode */
{ "bVisible": false }, /* ProgramGroup */
{ "bVisible": false }, /* EventCode */
{ "bVisible": false }, /* LocCity */
{ "bVisible": false }, /* LocState */
{ "bVisible": false }, /* LocAddress */
{ "bVisible": false }, /* LocName */
{ "bVisible": false }, /* LicenseeURL */
{ "bVisible": false }, /* GateKeeper_length */
{ "bVisible": false }, /* SeatsAreAvailable */
{ "bVisible": false }, /* LocLatitude */
{ "bVisible": false } /* LocLongitude */
],
"fnServerData": function( sSource, aoData, fnCallback ) {
aoData.push({"name":"state_list", "value":"TX"},
{"name":"month_list", "value":"2011-09"},
{"name":"program_list", "value":"0"},
{"name":"zip_code", "value":"75210"},
{"name":"miles", "value":"100"}
);
$.ajax({"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
});
}});
});
[/code]
My PHP is kind of long but it does return the correct JSON.
you can also view the returned data in firebug in the xhr tab
is there a way you can link me to the project so I can look through the debugger and see where things stop working?
How am I going to pass values from my external filter controls and reload the table with the data filtered? I don't yet have the filtering controls on the page but I will have filtering for month_list, state_list, program_list and zip_code+miles.
Here is the demo (without the filtering controls):
http://www.scic.com/schedule_eddie
How do I make a sort for the date column where it would use data in another column to actually do the sorting?
And why does the pagination are look stupid?
(there are more pagination entries in those css files if these 2 don't do the trick for you)
[code]
.paging_full_numbers .ui-button {
padding: 2px 6px;
margin: 0;
cursor: pointer;
}
.dataTables_paginate .ui-button {
margin-right: -0.1em !important;
}[/code]
1) to pass data into filter, there's an API filter function fnFilter that lets you apply a filter (text) and column, and some options like regular experssion or "smart" matching. http://www.datatables.net/ref#fnFilter
2) I think you're looking for "custom data-source sorting" http://www.datatables.net/development/sorting#data_source . by adding a routine to afnSortData, and setting the "sType" for your column to the name of your afnSortData routine, you can control the sorting of a column based on an array you construct (could be from other columns). the oSettings object passed into your routine will let you access all the columns.
Looks like the sort arrows in the header have also been somehow touched/modified in terms of CSS; by default they use some absolute positioning to put them in the right of the cell rather than below the text.
The footer controls are also *generally* better-looking when side-by-side instead of stacked, but that's a design decision.
@fbas - I'm not sure if you really understand what I mean on the filtering. We will have separate controls for selecting filter criteria and I need to pass that info for the table to be regenerated by my server-side script. I now pass them using the aoData parameter in fnServerData, (See above)
The table will be initialize with values that are stored in a cookie vs. the hard-coded values I am showing above.
I also have several hidden columns and need to sort on one of those when I am sorting the Date column. I'm not sure how the custom data-source sorting helps out here.
----
if your filtering controls are being sent in as parameters or values in cookie, you can still access them and feed them to the fnFilter API function.
It sorts December before September. Not sure how to make it sort dates correctly, here.
Using the fnFilter option may be problematic because the data being passed isn't actually part of the data being returned, so to speak. Can you show me how to pass the data in this fnServerData in an fnFilter call?
[code]
"fnServerData": function( sSource, aoData, fnCallback ) {
aoData.push({"name":"state_list", "value":"TX"},
{"name":"month_list", "value":"2011-09"},
{"name":"program_list", "value":"0"},
{"name":"zip_code", "value":"75210"},
{"name":"miles", "value":"100"}
);
[/code]
[code]"aoColumns": [null, /* Location */
{ iDataSort: 2 }, /* FormalDate */
null, /* hidden date */
// ...
]
// select e.LocName,
// e.FormalDate,
// e.ShortDate
// /// etc.
[/code]
------
the fnFilter needs you to pass in a string value (what to filter on) and the column to filter on (or omit it if you want it to search all searchable columns).
[code]oTable.fnFilter("TX", 9);[/code]
but for server side processing, all fnFilter does is pass these values as parameters (set as that column number's sSearch value, i.e. "sSearch_9=TX"), so if you're pushing the values onto aoData, you're already accomplishing the same thing.
At that point, it just depends on what you tell your server side script to do with those parameters.
Hope that helps a bit!
Greg
What I ended up doing is I added some null dummy columns and used that for state, zip+miles, months, and programs filters changed by calling the fnFilter function. I took out the old way of doing it with the aoData parameters and now it works just by setting the filter and capturing the filter values in the individual column filtering loop adn setting the WHERE clause accordingly.
Thanks a million for everything. Now on to skinning this thing well.
As a back-end person, my huge flaw is making nice front-end designs. Always in awe of nice, easy, intuitive displays.
http://www.scic.com/schedule
I am updating the use different filtering criteria controls to make it easier for the users to find courses.
Any ideas on what to use to do that in an area 940px wide by 50px tall above the table?