By scruffd0g on

I've been running around trying to figure this out on the site, and I've come close, but I can't quite get there.

I've created a new content type (cntnt) and this content type has a CCK text field (open) where they can use a checkbox to select either "completed" or "not completed".

What I'd like to do is show a box that looks like this:

X Cntnt posted on the site
(Y Completed, Z Not Completed)

I figured out pretty quickly how to do the 'X Cntnt posted on the site' part using this from http://drupal.org/node/238677:


<?php
  $mytype = 'cntnt';
  $count = db_result(db_query("SELECT COUNT(DISTINCT(n.nid)) FROM {node} n WHERE n.type='%s' AND n.status=1", $mytype));
  echo "Cntnt Posted on the Site: $count";
?>

But, I'm not sure how to pull this off with the cck reference. Using the code above, and more from the referenced post, I was trying something like this:

<?php
  $mytype = 'cntnt';
  $count[items] = db_result(db_query("SELECT COUNT(DISTINCT(n.nid)) FROM {node} n WHERE n.type='%s' AND n.status=1", $mytype));
  $count[open] = db_result(db_query("SELECT COUNT(DISTINCT(c.nid)) FROM {node} n, {content_type_page} c WHERE n.type='%s' AND n.status=1" and c.field_$open_value = 'Completed', $mytype, $string2));
  $count[done] = db_result(db_query("SELECT COUNT(DISTINCT(c.nid)) FROM {node} n, {content_type_page} c WHERE n.type='%s' AND n.status=1" and c.field_$open_value = 'Not Completed', $mytype, $string2));


  echo "Cntnt Posted on the Site: $count[items] ($count[open] Open, $count[done] Completed)";
?>

However, the first problem I'm running into is an error regarding db table for {content_type_page}. I'm not sure what to change this to to make this work.

Any thoughts on this, or any other guidance you might have that would lead me in the right direction to getting this working?

⋅ Categories: Drupal 6.x

Comments

scruffd0g’s picture

scruffd0g commented

I think I might go ahead and switch this over to a taxonomy term, but I'm a bit dissappointed about not having a checkbox selection for the user (using D6.x).

I'm still interested, however, if anyone has any suggestions on the above.

pahoehoe’s picture

pahoehoe commented

but it is pretty brute force.

This code gets all nodes of type "info" and counts the number of them that have the custom field "region" equal to the value "East Coast".

$node_type = 'info';
$sql = "SELECT node.type, node.nid FROM node WHERE node.type = '$node_type' ";
$result = db_query( $sql );

$count = 0;

while ( $obj= db_fetch_object( $result)) 
{
   $node = node_load( array( 'nid' => $obj->nid ));
   if($node->field_region[0]['value'] == "East Coast")
   {
        $count++;
    }
}

print $count;

I needed to do the same thing and I hobbled together a solution from various other posts. I'm new to drupal and PHP programming, so I suspect that there is a much more efficient way to do this. And if you know one, by all means please share.

watbe’s picture

watbe commented

Sorry for hijacking this, but I have a similar problem. I have two content types: team_project and team_news.

For team news, I already have a cck field which references a team_project that it is related to, but I need a code which is embedded in each team_project which shows the team_news it is related to. So at the moment I have one side of this relationship, that the team_news is related to a team_project, but not the reverse.

So looking at your code here, I'm very new to PHP and drupal programming, but I do know some basics, how would I be able to change that query so that it _returns_ all nodes that contain a the value equal to its own title? So let's say I have this set up:

team_project_1 has two team_news created. On each team_news, it shows inside a cck field that the project it is related to is team_project_1. Now I need the page: team_project_1 to show both team_news that is related to it. this relation can only be defined by the node title.

Can you understand this or have I made it too confusing? =P

Thanks for any help in advance, I really appreciate it!

- Wayne

WorldFallz’s picture

WorldFallz commented

Are you using a nodereference field to make the connection? If so, you can use the http://drupal.org/project/nodereferrer module to get the reverse relationship. There's an issue in the issues queue for the d6 version. Also in that issue, toward the bottom, is a comment with the instructions for recreating that functionality with views.

===
"Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime." - Lao Tzu
"God helps those who help themselves." - Ben Franklin
"Search is your best friend." - Worldfallz

watbe’s picture

watbe commented

Ahh this is just what I needed, although in 6.x but that shouldn't be too big of a problem looking at it.

Thanks a lot!

GBain22’s picture

GBain22 commented

Hi pahoehoe,

I have tried your code, executed on a page with PHP filter input format. And everything I try returns a 0. My content type is called "tech" (technical article) and my field is a select box which is field_wikicategory. So my code is:

$node_type = 'tech';
$sql = "SELECT node.type, node.nid FROM node WHERE node.type = '$node_type' ";
$result = db_query( $sql );

$count = 0;

while ( $obj= db_fetch_object( $result))
{
   $node = node_load( array( 'nid' => $obj->nid ));
   if($node->field_wikicategory[0]['value'] == "Drupal")
   {
        $count++;
    }
}

print $count;

Is there any reason why this would return 0?

GBain22’s picture

GBain22 commented

I had a play about with some SQL from a View I had which will echo out the node titles of each "article" I have. My CCK drop down categorises my posts, so it would look at any nodes that have the value "Drupal" - for any Drupal articles I have, and then count them. The code is:

This code could not be copied and pasted as there are values to be changed, such as "node_data_field_wikicategory" - this would have to be changed to "node_data_field_YOURFIELDNAME" depending on what you called your CCK field. You would have to replace every occurence of this "wikicategory" in this example. Also notice "Drupal" as being the value im searching for and "tech" being the machine readable name for my "Wiki Tech Articles" - this would need changing too.

 $node_type = 'tech'; $sql = "SELECT node.nid AS nid, node.title AS node_title FROM node node LEFT JOIN content_type_tech node_data_field_wikicategory ON node.vid = node_data_field_wikicategory.vid WHERE ((node_data_field_wikicategory.field_wikicategory_value) = ('Drupal')) AND (node.status <> 0) AND (node.type in ('tech'))";

$result = db_query( $sql ); 

$drupal=mysql_num_rows($result);

echo $drupal;

This would echo out "5" in my case - as I have 5 articles that contain the value "Drupal" from my dropdown box.

bibstha’s picture

bibstha commented

Subscribing