J!Extensions Store™
Forum
Welcome, Guest
Please Login to access forum.
Jevents - show only future events (1 viewing) 
Go to bottom
TOPIC: Jevents - show only future events
#3043
MACR, Inc.
Fresh Boarder
Posts: 1
User Offline
Jevents - show only future events Karma: 0  
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
 
Logged Logged  
  The administrator has disabled public write access.
#3044
John Dagelmore
Admin
Posts: 3716
User Offline
Re:Jevents - show only future events Karma: 79  
Hi Bernie,

if you use the where clause dtstart > now() the program adds it quoting the value as 'now()', this is correct end expected, done for security reason and to prevent SQL errors.


BUT, you have full control on the query used because you can edit the raw SQL query and discard the SQL compiiler parameters.

So you can have a edited custom SQL query added the the final textarea, then click 'Save' button to apply it.


Code:

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" )
take care to use the correct MySql syntax. Finally, for JEvents there is also a predefined data source to import already configured to include only future events, find the file also in attachment for your convenience (unzip the archive and import the json file into JSitemap) Greetings, John
File Attachment:
File Name: jevents_future_events.zip
File Size: 1348
 
Logged Logged  
  The administrator has disabled public write access.
#3061
Petra Webstein
Fresh Boarder
Posts: 12
User Offline
Re:Jevents - show only future events Karma: 0  
Hello John

Great to see this discussion. I have imported the file you supply us with in your post.

I find that I have the following results:

From a menu link, I list events that fall under a category f.e. WORKSHOPS. When I test this in the analyser I get the following statement:


Later on using the dataset to list the events I am having the same results as in status 303.

Google does not seem to be picking this up.

Thanks Petra
 
Logged Logged  
  The administrator has disabled public write access.
#3063
John Dagelmore
Admin
Posts: 3716
User Offline
Re:Jevents - show only future events Karma: 79  
Hello Petra,

try to determine if the 303 status code is for all links or only for this kind of links of JEvents. If the Analyzer reports 200 for other links it could be how JEvents works with this type of link, otherwise the Analyzer could be blocked on the server.

John
 
Logged Logged  
  The administrator has disabled public write access.
#3090
Petra Webstein
Fresh Boarder
Posts: 12
User Offline
Re:Jevents - show only future events Karma: 0  
It seems to be JEvents specific. JEvents adds a date to the links. The date is the TODAY date. So whenever that date is in there, there is the 303 error.
 
Logged Logged  
  The administrator has disabled public write access.
#3091
Petra Webstein
Fresh Boarder
Posts: 12
User Offline
Re:Jevents - show only future events Karma: 0  
Wanted to give you a screenshot as well.
 
Logged Logged  
  The administrator has disabled public write access.
Go to top