I would like to show only future events in my sitemap. I have encountered 1 problem and i'd like to have 1 new ability.
The PROBLEM:
If I use the where clause dtstart > now what your program adds isn't correct. This is the sql statement i get:
where...
AND DATE_FORMAT(FROM_UNIXTIME(`#__jevents_vevdetail`.`dtstart`), '%Y'
AS `year`, DATE_FORMAT(FROM_UNIXTIME(`#__jevents_vevdetail`.`dtstart`), '%m'
AS `month`, DATE_FORMAT(FROM_UNIXTIME(`#__jevents_vevdetail`.`dtstart`), '%d'
AS `day` > 'now()'
when i paste this into an sql query i get an error.
Error in query (1064): Syntax error near 'AS `year`, DATE_FORMAT(FROM_UNIXTIME(`jos_jevents_vevdetail`.`dtstart`), '%m'
A' at line 11
Also, i would like to have a little more control over this... like an override.
on my site i have taken into account timezones. so, i can have this accurate to about 5 minutes. However in order to do that i need to be able to do something like this.
AND from_unixtime(ed.dtstart) > CONVERT_TZ( now(), fv.value, "US/Eastern" )
Full sql query:
SELECT e.ev_id
FROM jos_jevents_vevent AS e
LEFT JOIN jos_jevents_vevdetail AS ed ON e.ev_id = ed.evdet_id
LEFT JOIN jos_community_fields_values AS fv ON fv.user_id = e.created_by
LEFT JOIN jos_community_fields AS cf ON cf.id = fv.field_id
WHERE
e.state = 1
AND
fv.field_id = 19
AND from_unixtime(ed.dtstart) > CONVERT_TZ( now(), fv.value, "US/Eastern" )
Let me know how to proceed.
thanks,
Bernie