List Manager Forum

Filter View for future dates

User moon_1581 2014-04-16 01:42:03

Hello,

is it possible to create a view that shows a list with only future dates in it. Of course I hav a list with a date field.
I read something in another thread with the SQL query NOW() but I could not figure out how this works and where I have to put the query.

Thank you in advance for your advice.

Alex

Moonsoft support 2014-04-16 10:24:20

Hello,
maybe you read about now function at table manager forum, as it allows direct sql expressions. In order to use such a filter at list manager, you will need to introduce a code tweak, because your expression NOW() is being quoted, so its handled as a text (which will never match your date field). Steps:

- Place at your view the filter condition yourdatefield 'More than' NOW()
- Introduce this line:

if($values_none=="'NOW()'") $values_none='NOW()';

at joomla\components\com_listmanager\models\serverpages.php

below db quote line (about line 407), so it will read

$db->quote($values);
if($values_none=="'NOW()'") $values_none='NOW()';
$values_start=$this.........


It will execute now function as expected while keeping the rest of the text filters working as default.

Hope this helps,regards

User moon_1581 2014-04-24 00:14:54

Hello,

thank you for your advice. I think I got an overview how this works. But I faced the problem, that I use german date format. So I had to convert to

if($values_none=="'NOW()'") $values_none= DATE_FORMAT(NOW(), '%d.%m.%Y');

But as I looked in the MySQL table I figured out that the date is saved as string. So a "greater than" for two dates in string format will not be successful. I have to convert it to datetime or something. Do you have an idea for that?

Moonsoft support 2014-04-24 09:55:18

Hello,

If you want to convert date strings to mysql dates you should use str_to_date function. Please check examples at:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Maybe you should apply this function to 'v.value' in code and use now() without any conversion. Something like this:

if($values_none=="'NOW()'") $values_none='NOW()';

...

case 2: // More
if($values_none='NOW()')
$query .= " str_to_date(v.value,'%d,%m,%Y') > ".$values_none;
else
$query .= " v.value > ".$values_none;
break;



Hope this helps. Regards,

User moon_1581 2014-04-29 00:16:09

Hi,

I finally made it. Your code worked as suggested. I just had to replace "," with "." in the date format string.

$query .= " str_to_date(v.value,'%d.%m.%Y') > ".$values_none;

Thank you for your help.

...
List Manager

Build different lists for your site

Buy now!
...
Support/development

Perfect for small code changes or to correct any bug at your site

Buy now!