Last updated November 20, 2011.
I’m trying to use a combination of date, calendar, views, and cck to make a calendar of upcoming events and I’m not having much luck. The basic problem is that my calendar view is sorting based on the
changed field in the
node table, not on the date and time set for my events.
I'm going to make this detailed so thanks so much in advance for your help!
To get to this point I:
I used CCK to make a new content type
event to which I added one additional field
event_datetime of type
datetime using the text field custom input format as Mo/Da/Year – TimeAM/PM (ex: 10/25/2008 – 11:45am).
Then in views I cloned the default calendar view. I used filter to set
Node: Type = Events to remove non-events from my calendar and
Content: Event Date and Time (field_event_datetime value) asc to hopefully sort get events to show up on the correct days. I will include a full copy of my calendar view's 'default' settings pain at the end of the post.
This isn't working. Instead I’m getting a great calendar view that shows all of my events nodes on the date the event is created / updated (today) not the date set for the event to occur in the event creation process (some time in the future).
The MySQL table
content_type_event shows the values I set when creating my events as
field_event_datetime_value2. Manually editing the
created feild and
changed feild in the
nodes table moves them on the calendar view. In sum it appears my calendar is sorting based on node creation and not event datetime.
Bottom Line: Can someone please tell me how in the world I make views sort based on the correct field?
Views keeps coughing up a huge SQL query that looks like it would be helpful to someone with more knowledge, so here that is:
SELECT node.nid AS nid,
node.changed AS node_changed,
node.title AS node_title,
node_data_field_event_datetime.field_event_datetime_value AS node_data_field_event_datetime_field_event_datetime_value,
node.type AS node_type
FROM node node
LEFT JOIN content_type_event node_data_field_event_datetime ON node.vid = node_data_field_event_datetime.vid
WHERE ((node.status <> 0) AND (node.type in ('event')))
AND ((ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(-14400)) <= '2008-10-31 23:59:59' AND ADDTIME(FROM_UNIXTIME(node.changed), SEC_TO_TIME(-14400)) >= '2008-10-01 00:00:00'))
ORDER BY node_data_field_event_datetime_field_event_datetime_value ASC
I tried just blindly dropping that into phpMyAdmin and got back reasonable results:
nid node_changed node_title node_data_field_event_datetime_field_event_datetime_value node_type
7 1224949001 Test Event 1 2008-11-04 11:36:41 event
8 1224949115 Event Test 2 2008-11-25 11:38:35 event
This just makes me think that I'm not smart enough to figure views out because it is clearly getting the right info out of MySQL it's just sorting on the wrong column.
Settings from my testcalendar view's default settings tab:
Style: Calendar navigation
Use AJAX: No
Use pager: No
Items to display: Unlimited
More link: No
Link display: Calendar page
Exposed form in block: No
Empty text: None
Date: Date (node.changed)
Node: Title Title
Content: Event Date and Time (field_event_datetime value) asc
Node: Published True
Node: Type = Events