By celloandy on

I'm working on a module that I want to fetch a set of CCK nodes that have date fields, based on a date range (i.e., fetch all nodes whose CCK date fields are between September 1st and September 8th). Is there a nice, api-ish way to do this, or do I have to query the database directly?

⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

Why not use a view?

celloandy’s picture

celloandy commented

I don't actually want to display them. I just want to count how many occur on each day within the span, do some calculations, and put together a little display in a block.

drawk’s picture

drawk commented

Yeah, you'll probably want/need to db_query for it then.

nevets’s picture

nevets commented

You could use views to set up the query and then use could to build the query and walk through the results. Using views mean you would not care how the data is stored.

beeradb’s picture

beeradb commented

actually my previous answer was totally wrong, misread the question.

beeradb’s picture

beeradb commented

Ok, here's the real answer. This assumes D5, but I'm sure similar methods are available in 6, you'll just have to do a little legwork to track it down. This is a bit tricky because depending on how the field is set up the CCK moves which data the table is stored in, http://drupal.org/node/112792 has a diagram explaining the different cases. For this reason it's a bad idea to hardcode table names for your query, since the date value is stored outside of the main node table, and can change depending on how the field is assigned to content. You'll have to rely on a few of the CCK's functions to help you out.

This assumes you are trying to get the field "field_date" from the page content type. It will look something like this:

// Get an array of content type information for the page content type.
$page_fields = content_types('page');
// Get the table and column information for the 'field_date' attached to this content type.
$db_info = content_database_info($page_fields['fields']['field_date']);

// Create the query.
$page_count = db_result(db_query("SELECT count(n.nid) FROM {node} n INNER JOIN {". $db_info['table'] ."} fd ON fd.%s > %d AND fd.%s < %d", $db_info['columns']['value']['column'], $starting_timestamp, $db_info['columns']['value']['column'], $ending_timestamp));

That's not perfect, but it should get you close enough you can find your way.