How to filter by date?
tanc - March 10, 2008 - 06:09
| Project: | Ajax Table |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
I've now got my ajax table displaying with the correctly formatted dates. I ended up bringing in the dates as a unix timestamp and formatting them with the callback. My problem is that the date search input won't filter the results at all. I am following the example with the editable date fields as closely as possible but I don't want my date fields to be editable. Can anyone offer more guidance on how to correctly set up a date filter?
My code is:
<?php
function ajaxtable_table_ajaxtable_mytable($override='') {
// Gets our profilenid from the session table
$profilenid = $_SESSION[profilenid];
$myquery = "SELECT field_ride_distance_value, field_ride_format_average_value, field_ride_format_maximum_value, UNIX_TIMESTAMP(field_ride_date_value) as field_ride_date_value, field_ride_reference_value FROM {content_type_ride_stat} n JOIN {nodefamily} nf ON nf.child_nid = n.nid WHERE nf.parent_nid = ".$profilenid;
$search_inputs = array(
array(
'type' => 'date',
'id' => 'date_search',
'cal_params' => array(
'ifFormat' => '%d-%m-%y',
'showsTime' => 'false',
'timeFormat' => '24',
),
'help_title' => 'Date Search',
'operator' => '>',
'help' => 'Anything after this date',
'col' => 'field_ride_date_value',
'size' => 8,
),
);
$inputs = ajaxtable_search_inputs($search_inputs);
$theme['search'] = '
<table class="ajaxtable-default-search-table">
<tr>
<td class="ajaxtable-default-search-table-left"></td>
<td>{search_help}</td><td>Search for {search_input} </td>
<td> | After this date: ' . $inputs['date_search']['input'] . '</td>
<td>' . $inputs['date_search']['help'] . '</td>
<td>{search_button}</td>
<td class="ajaxtable-default-search-table-right"></td>
</tr>
</table>
';
$table = array(
'query' => $myquery,
'columns' => array(
array(
'col' => 'field_ride_reference_value',
'label' => 'Route or ride reference',
'sortable' => TRUE,
'searchable' => TRUE,
'help' => 'Your route reference',
),
array(
'col' => 'field_ride_date_value',
'label' => 'Date',
'sortable' => TRUE,
'searchable' => TRUE,
'default_sort' => 'desc',
'help' => 'The date of the ride',
'callback' => 'ajaxtable_col_date_myexample',
)
)
);
$override = array(
'search_inputs' => $search_inputs,
'theme_override' => $theme,
);
ajaxtable_override($table,$override);
return $table;
}
function ajaxtable_col_date_myexample($col,$value,$row,$table,$callback_array) {
$value = date('d-m-y',$row['field_ride_date_value']);
return ($value);
}
?>My table is correctly filled (using only two columns currectly) from my sql query. The text filter for the route reference works perfectly, its just the date filter which isn't. I don't understand how this part of the ajax table works. Any help is greatly appreciated.
#1
It seems as if the calendar input is quite limited. I've worked out that the Date API is storing all dates in the ISO format which needs to be converted in the SQL query to a unix timestamp ready to be formatted by the callback function and displayed. This works fine. The problem is getting the calendar input function to convert to the format needed by the database so it can process the query properly and the only way I could find of doing this is to alter the module. I've added this line:
$search_values[0] = date_convert($search_values[0], DATE_UNIX, DATE_ISO );in the section where it checks for additional inputs. This converts the timestamp produced by the calendar input into the required iso format and allows the database table to be queried properly. This is by no means an ideal solution and only works in my case where the Date API has stored a CCK field in the ISO format.I'm thinking that it would be good to be able to have some more control over formats, as many ajax tables are going to be based on CCK node tables which will be using the Date API which in turn stores all date values in the db in ISO format. Do you think you could allow for another parameter to set the date format and a function to do the conversion? Otherwise this excellent module is going to be limited to stock nodes or nodes which don't use the Date API.