Query to will exclude past events and find the next one coming up [#325374]

I am working on a custom module that needs to find the next occurrence of a node by it's date field. I have spent the better part of the day trying to figure out how to perform a query that will exclude past events and find the next one coming up.

The basic code I have right now:

	// query db to find next STOMP event
	$type = 'stomp';
	$status = 1;

	$sql = "SELECT * FROM {node} n WHERE type = '%s' AND status = %d ORDER BY n.created ASC";
	$result = db_query(db_rewrite_sql($sql), $type, $status);
	
	//extract the output
	while ($data = db_fetch_object($result)) {
		$node = node_load($data->nid);
	}

This works great but does not account for the CCK date field. I am really struggling with figuring out a way to perofmr a query that will search the 'stomp' content type and compare the CCK date field with the current date to find the next one to occur.

Any help is much, much appreciated!

Comments

Comment #1

3cwebdev commented 24 October 2008 at 02:07

Title:

Please advise on how to perform this date query

» Got it to work

Okay, I figured it out :)

For reference:

Using date field as unix timestamp type.


	// query db to find next event
	$type = 'event'; // content type
	$status = 1; // published

	$sql = "
	SELECT * 
	FROM {node} n, {content_type_event} dt
	WHERE n.type = '%s' 
	AND n.status = %d
	AND dt.nid = n.nid
	AND dt.field_datetimestamp_value >= '%s'
	ORDER BY dt.field_datetimestamp_value ASC
	;";
	
	$result = db_query(db_rewrite_sql($sql), $type, $status, $timestamp);
	
	$data = db_fetch_object($result); 	//extract the output
	$node = node_load($data->nid); // load the node

Should this snippet be moved from the issue cue to somewhere where it might be more easily found?