By jaydunford on

Hello

I'm working on a development drupal 4.7.4 site and have been experimenting with the CCK and Views module to build an 'activity monitoring system'. The prototype system works fine so far - users can create activity reports and can also view all submitted reports with sortable columns thanks to the Views module.

Each activity report contains the following fields:

- Persons name (CCK text)
- Activity date (CCK date module)
- Activity category (CCK text)
- Additional resources (CCK text)
- Number of students involved (CCK integer)

What I need to be able to figure out now is how to keep an incremental counter so each time someone submits an activity report the total number of students involved is updated (sounds easy enough?)

Not really sure how to go about it though. After some research I downloaded the Computed Field module for CCK figuring this may help me, I had a stab at it but my PHP/MySQL skills have much to be desired so have come to a grinding halt.

I'm a little unsure if I even need the Computed Field module - perhaps there is a easier way to total the 'number of students involved' fields from all 'activity report' nodes and be able to display this total by querying the database?

Can anyone point me in the right direction?

If any more info is required - please ask.

Cheers
Jay

⋅ Categories: Drupal 4.7.x

Comments

jaydunford’s picture

jaydunford commented

Bump - anyone able to help?

-Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

jaydunford’s picture

jaydunford commented

Can someone tell me - how many times is it generally considered okay to bump posts in the forum if you are not getting any response?

Cheers ;)
Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

jaydunford’s picture

jaydunford commented

One last bump then...

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

patrickharris’s picture

patrickharris commented

but if you are trying to store computed information, Computed Field module is a good choice. Otherwise you can use the nodeapi hook to alter the node you are saving, for instance on submit. So you could have a cck field that you might hide when the node is being edited (so the user doesn't see it), but insert a value into it on submit.

function phptemplate_nodeapi(&$node, $op, $teaser = NULL, $page = NULL) {
  switch ($op) {
    case 'submit':

if ($node->type == 'MY_SPECIAL_TYPE_OF_NODE') {

# Access form and database variables and do my caculations here
#
# Then store my values in the form that is about to be submitted & saved
	}
      break;

  }
}
jaydunford’s picture

jaydunford commented

Putting it as simply as I can:

I have a custom content type called 'activity'.

When site contributors complete an activity form they enter a number (the number of students involved in that activity) into a CCK integer field.

I need to find a way to monitor the TOTAL number of students involved in ALL the activities submitted to the site by contributors.

Hopefully that has clarified my aims a bit more. With that in mind, do you think that a combination of computed field and nodeapi is the best way to go towards solving this one?

I haven't attempted any nodeapi stuff yet but I assume the function you posted would go in the template.php file in my theme? (once I figure out what code should go in the if statement).

Like I say my PHP / MySQL skills are basic but I would be happy if I could use this problem learn some new stuff.

Thanks Again
Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

patrickharris’s picture

patrickharris commented

If I was trying to store the computed figure within the node, I'd use either Computed Field module, or a node API example like the one I gave before. You might find the Node API example in your php template is simpler.

Maybe you just want to be able to view a computed figure when the node is viewed though? If so, the figure doesn't need to be stored. So you could create your function (an sql query) that works out the computed figure, and then display it in the template for your custom content type.

jaydunford’s picture

jaydunford commented

What would be best is if the method of checking the 'total number of students' figure from all submitted activities was a separate process to actually creating a custom 'activity' node.

So site contributors create these 'activity' nodes (which include the important: 'number of students involved' integer field), and they are not actually interested in the 'TOTAL number of students' figure when they submit an individual 'activity' node.

When the designated person needs to check 'total number of students' figure - they do so via a new custom page which uses PHP/SQL to query the database (this is where I am feeling around in the dark a bit). I'm hoping it's possible to do something like:

-Query the database
-Select just the integer fields (students involved) from all the 'activity' nodes found in the database at that time
-Total up all the 'students involved' results
-Display that total figure on the new custom page

Maybe it's not necessary to use computed field / nodeapi after all?
What do you think of this approach, does this sound do-able?

Cheers
Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

patrickharris’s picture

patrickharris commented

You could create your function (an sql query) that works out the computed figure, and then display it in the template for your custom content type, or where ever you want to display this figure.

To improve PHP & MySQL skills, look for a good book on the topic - perhaps "PHP and MySQL for dynamic web sites" by Larry Ullman ... also you might want to read the IBM articles on building a Drupal web site.

jaydunford’s picture

jaydunford commented

Thanks for all the advice Patrick, I think I'll try this approach and see how it goes.

Thought I should mention that the IBM articles link you just posted goes to my original post?

No big deal though as I searched this site for IBM articles and have a load of results to go through.

Cheers
Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

patrickharris’s picture

patrickharris commented

IBM articles!

jaydunford’s picture

jaydunford commented

I checked out the IBM articles and dug out an eBook I had ( MySQL - PHP Database Applications.pdf ) which helped somewhat.

I'm now confident that it's possible to solve my problem withouth using computed field or nodeapi but I need a little help getting the PHP/SQL code right.

I've created a node for my data extraction tests. At present it contains the following PHP (adapted from the snippets section of the handbook):

$sql = "select * from {node_content_cck_test} where field_number_of_students_involv_value > 0";
$output .= "<ul>";
$result = db_query($sql);
while ($anode = db_fetch_object($result)) {
$output .= "<li>".l($anode->field_number_of_students_involv_value, "node/$anode->nid")."</li>";
}
$output .= "</ul>";
print $output;

This is the data returned by the above code, it shows each activity node's sub-total.

db query test

    * 11
    * 4
    * 2
    * 6
    * 16
    * 28
    * 23
    * 10
    * 6
    * 9
    * 7
    * 2
    * 7

Submitted by: Admin – Wed, 15/11/2006 – 15:36

Of course I don't want to output the above to the user, what I am trying to do is get the 'total number of students' figure (the sum of the above numbers).

By querying the database manually via a Terminal window I have managed to figure out the SQL needed to do this by trial and error. The code below does exactly what I want (albeit from a Terminal window)- returning an accurate total figure:

select sum(field_number_of_students_involv_value) from node_content_cck_test;

I just need a final bit of help integrating the above line of SQL into drupal-friendly PHP which returns something like:

Total number of students enaged in all activities to date = (the dynamic result)

Any help would be appreciated

Thanks
Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk

jaydunford’s picture

jaydunford commented

Just thought that I would add a link to the following post where I found the answer to my problem:

http://drupal.org/node/98328

Cheers
Jay

The Centre for Sustainable Futures ~ http://csf.plymouth.ac.uk