DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

coldfusion and datatables filtering based on date range

coldfusion and datatables filtering based on date range

zionistzionist Posts: 19Questions: 3Answers: 0
in Free community support

Hello, i am new to datatables but i have been using coldfusion and flex to build app. Now i want to turn a flex app into an html5 app. In my app have data from a database and am using coldfusion to populate a table. i would like to filter the data based on date range. i have tried to come up with something based on research done online but its not working. Below is my code.

<cfquery name="expenses" datasource="moRentals">
select *
FROM Dashboard, Drivers, Machine
</cfquery>

<cfquery dbtype="query" name="results">
select ID, hireDate, machineID, driverid, distanceMoved, description, expense, revenue, expenseReason, subTotal, driver_id, fname + ' ' + lname AS fullname, picture, machine_ID, machineNumber, mileage

FROM expenses
WHERE driverid = driver_id 
AND machineID = machine_ID
ORDER BY hireDate

</cfquery>

<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mo Rentals using CFGRID</title>

<link rel="stylesheet" type="text/css" href="css/dataTableStyles.css" />

<link type="text/css" href="css/jquery-ui-1.8.4.custom.css" rel="stylesheet" />

</head>

<body>

Between and
Date Machine Operator Name Income Expenditure Profit
image #DATEFORMAT(results.hireDate, "mmm d, yyyy")# #results.machineNumber# #results.fullname# UGX #NumberFormat(results.revenue, ',')# UGX #NumberFormat(results.expense, ',')# UGX #NumberFormat(results.subTotal, ',')# image
$(document).ready( function () { $('#expenseList').DataTable(); } ); // The plugin function for adding a new filtering routine $.fn.dataTableExt.afnFiltering.push( function(oSettings, aData, iDataIndex){ var dateStart = parseDateValue($("#dateStart").val()); var dateEnd = parseDateValue($("#dateEnd").val()); // aData represents the table structure as an array of columns, so the script access the date value // in the first column of the table via aData[0] var evalDate= parseDateValue(aData[0]); if (evalDate >= dateStart && evalDate <= dateEnd) { return true; } else { return false; } }); // Function for converting a mm/dd/yyyy date value into a numeric string for comparison (example 08/12/2010 becomes 20100812 function parseDateValue(rawDate) { var dateArray= rawDate.split("/"); var parsedDate= dateArray[2] + dateArray[0] + dateArray[1]; return parsedDate; } $(function() { // Implements the dataTables plugin on the HTML table var $dTable= $("table.dataTablesTable").dataTable( { "iDisplayLength": 200, "bStateSave": false, "oLanguage": { "sLengthMenu": 'Show <select>2550100200 entries' }, "aaSorting": [[0,'asc']], "aoColumns": [ { "sType": "date" }, null, null ] }); // The dataTables plugin creates the filtering and pagination controls for the table dynamically, so these // lines will clone the date range controls currently hidden in the baseDateControl div and append them to // the feedbackTable_filter block created by dataTables $dateControls= $("#baseDateControl").children("div").clone(); $("#feedbackTable_filter").prepend($dateControls); // Implements the jQuery UI Datepicker widget on the date controls $('.datepicker').datepicker( {showOn: 'button', buttonImage: 'assets/calendar.gif', buttonImageOnly: true} ); // Create event listeners that will filter the table whenever the user types in either date range box or // changes the value of either box using the Datepicker pop-up calendar $("#dateStart").keyup ( function() { $dTable.fnDraw(); } ); $("#dateStart").change( function() { $dTable.fnDraw(); } ); $("#dateEnd").keyup ( function() { $dTable.fnDraw(); } ); $("#dateEnd").change( function() { $dTable.fnDraw(); } ); });

</body>
</html>

This question has accepted answers - jump to:

Answers

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.