NeXTensio Forum :: Filtering by date

I want to filter my Nextensio list by date, specifically I want to retrieve all records from the previous the 7 days, 1 month,  1 year etc

I can use

WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= image_img.date_img

in the recordset, how can I make this WHERE clause work in the filter option. 

Thanks in advance

Hello,

Please detail the scenario - would you like to implement a custom filter button for this?

Ionut

Hi, 

Here is the page, hopefully this will help to illustrate what I want to achieve. http://www.photocritter.net/forum/posts.php?mod=toppo&show_filter_tfi_ListrsImage6

The page displays photographs in order of their rating score.  I want to filter the recordset with options from a drop down list in the date field. By default I want to filter the recordset to show only images submitted in the last week with options to filter by last month, last year and all.

The SQL
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= image_img.date_img
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= image_img.date_img
WHERE DATE_SUB(CURDATE(),INTERVAL 365 DAY <= image_img.date_img
should be suitable for the job, in fact they return the correct results if I include one of them direclty in SQL statement to produce the Nextensio recorset, however I can't achieve the same results when using the  statements in the filter pane or a drop down list? Can you not directly use SQL in a Nextensio filter? How can I use a drop down list in the date field to obtain the results required

Copy of the recorset

SELECT image_category.title_cat, message_msg.idusr_msg, message_msg.id_init_msg, message_msg.idimg_msg, message_msg.id_msg, message_msg.id_category_msg, image_img.rate_img, image_img.date_img, image_img.rate_tech_img, image_img.rate_orig_img, image_img.view_img, image_img.filename_img, image_img.title_img

FROM ((message_msg LEFT JOIN image_category ON image_category.id_cat=message_msg.id_category_msg) LEFT JOIN image_img ON image_img.id_img=message_msg.idimg_msg)

WHERE message_msg.id_msg=message_msg.id_init_msg  AND {$NXTFilter_rsImages} AND {$NXTFilter_rsImages}

ORDER BY {$NXTSort_rsImages}

Thanks again

Stephen

Hello Stephen,

You can filter the NeXTenios List by a date value using a value like this into the filter: "> 2007/04/10". Check the help documentation for more details.

Yo should try this on a new page and see how the code has to look like.

So you need to have the "date" field from the list to have the filter set to display "As menu". You will define 3-4 static menu properties then apply the wizard.

Then go into the code for the date filter dropdown and customize it in order to have the values generated from some PHP variables in page (because you need the value to be like in my example). So the code for the dropdown will look like:

              <td><select name="tfi_listalbum_alb1_date_alb" id="tfi_listalbum_alb1_date_alb">
                <option value="<?php echo $_SESSION['a'];?>" <?php if (!(strcmp($_SESSION['a'], @$_SESSION['tfi_listalbum_alb1_date_alb']))) {echo "SELECTED";} ?>>1 day</option>
                <option value="<?php echo $_SESSION['b'];?>" <?php if (!(strcmp($_SESSION['a'], @$_SESSION['tfi_listalbum_alb1_date_alb']))) {echo "SELECTED";} ?>>2 days</option>

...
              </select>
              </td>

In the beginning of your page, you will define the $a, $b etc. session variables defined like:

session_start();
$date1 = date("m/d/Y", mktime(0, 0, 0, date("m")  , date("d")-1, date("Y")));
$_SESSION['a'] = "> ".$date1;

etc.

(you will format the date values according to your screen Date Format setting from InterAKT Control Panel)

I hope this helps.

regards,
Ionut

Hi lonut,

That works perfectly.

Many thanks

Stephen