By shanejeffery86 on

Hey all.

I have been racking my brain over this all morning and I must not be understanding something.

I am doing some custom work on the Search Type module, adding in additional conditions and the following is a part of the development:

$cat_sql = "SELECT content_field_event_category.nid FROM content_field_event_category 
	  				LEFT JOIN content_type_event ON (content_type_event.nid = content_field_event_category.nid)
	  				WHERE field_event_category_nid IN (" . substr($cat_sql, 0, strrpos($cat_sql, ", ")) . ")
	  				AND (DATE_FORMAT(ADDTIME(field_date_value, SEC_TO_TIME(-18000)), '%Y-%m-%d') >= '$current_year-$current_month-01')";	
	  	$result = db_query($cat_sql);
		while($category_nid = db_result($result)){
			$category_nids[] = $category_nid;
		}

Now, if I do an echo/die of the $cat_sql so as to see the query prior to execution, and I bring it over the mySQL and execute it there, the query returns 6 results. That is the correct number of results.

However, if I just let this code run without interruption and I do a print_r($category_nids) after the WHILE loop is complete, there is nothing in the array. There should be 6 elements in the array.

I have a feeling I am doing something wrong with the database abstraction functions, but I cannot figure it out.

Any help would be greatly appreciated!

⋅ Categories: Drupal 6.x

Comments

cayenne’s picture

cayenne commented

Try a print_r($category_nid); to see if there is anything there right after the While statement.

Secondarily, Try imitating the example:

See http://api.drupal.org/api/group/database/6

However, your code looks like it should work.

:)

shanejeffery86’s picture

shanejeffery86 commented

Nope. Nothing in $category_nid after the while loop.

That is what is baffling me. If I echo out the query and take it over to mySQL, the query returns exactly what I need. But, in Drupal, it looks like db_query and/or db_result is screwing something up. Is there any way to get either of those Drupal functions to output an error if there is a problem with the query?

Thanks!

shanejeffery86’s picture

shanejeffery86 commented

You know what -- screw it.

I am working around it. Seems that db_result or db_query was not digging the "IN" syntax in my SQL statement.

Just get out of the drupal mode and go back to regular querying:

$query = "SELECT content_field_event_category.nid FROM content_field_event_category 
	  				LEFT JOIN content_type_event ON (content_type_event.nid = content_field_event_category.nid)
	  				WHERE field_event_category_nid IN (" . substr($cat_sql, 0, strrpos($cat_sql, ", ")) . ")
	  				AND (DATE_FORMAT(ADDTIME(field_date_value, SEC_TO_TIME(-18000)), '%Y-%m-%d') >= '$current_year-$current_month-01')";
	  	$result = mysql_query($query) or die(__LINE__.' '.__FILE__." ".mysql_error());
	  	while($category_node = mysql_fetch_assoc($result)){
	  		$category_nodes[] = $category_node;
	  	}

Might not be the way I am supposed to do it. But at this juncture, it works. :)

cayenne’s picture

cayenne commented

Sometimes that's the best way to go. I couldn't get two tables to join for a Views table and finally just wrote a module!

:)