Español
English
Forum
List Manager Support
List Manager supportForums
No se permite escribir sin estar logado. Por favor, login
Filter View for future dates 16.04.2014 01:42
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
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
Re: Filter View for future dates 16.04.2014 10:24
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
Moonsoft Team
www.moonsoft.es
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
Moonsoft Team
www.moonsoft.es
Re: Filter View for future dates 24.04.2014 00:14
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?
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?
Re: Filter View for future dates 24.04.2014 09:55
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,
Moonsoft Team
www.moonsoft.es
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,
Moonsoft Team
www.moonsoft.es