Calculating a field to the database

[ove]

Hi,

I have a form with numeric values from 1-5. I would like to have an average of those values inserted into a column in the database. And obviously for it to be updated if the record is updated.
I have been looking at this issue on this forum but all calculated fields seem to refer to ones that aren't stored in the database. I would also need this average value to be shown in other tables later so I suppose it's best to have it in the database.

What would be the best way to achieve this? I'm too confused with triggers, delegate classes etc. to know even where to be looking for the answer.

Thanks!

[Jean]

Hi,
You just have to insert it into the delegate class in your table directory. If your table is called cities then your file will be cities.php.
You can use the triggers

Code: Select all

function after_action_new($params=array()){
     
$app =& Dataface_Application::getInstance();
$record =& $params['record'];
           $ID=$record->val('ID');
$db = $app->db(); // database resource handle...

           $res = mysql_query("update cities set field='$value' WHERE ID='$ID'");
        }

then the same with

Code: Select all

function after_action_edit($params=array()){
        }

Jean

[ove]

Code: Select all

function after_action_new($params=array()){
     
$app =& Dataface_Application::getInstance();
$record =& $params['record'];
           $ID=$record->val('ID');
$db = $app->db(); // database resource handle...

           $res = mysql_query("update cities set field='$value' WHERE ID='$ID'");
        }

Thanks a lot for this info. I got my column value calculated. However, it didn't work just like you described. Or maybe it would've but I just didn't figure it out. Here's the code that I have:

Code: Select all

        function after_action_new($params=array()){
        $app =& Dataface_Application::getInstance();
        $record =& $params['record'];
            $ID=$record->val('rev_id');
            $power=$record->val('power');
            $fuel_eco=$record->val('fuel_eco');
            $comfort=$record->val('comfort');
            $interior=$record->val('interior');
            $exterior=$record->val('exterior');
            $buildquality=$record->val('buildquality');
            $reliability=$record->val('reliability');
            $total=round((($power+$fuel_eco+$comfort+$interior+$exterior+$buildquality+$reliability)/7) ,2);
        $db = $app->db(); // database resource handle...
            $res = mysql_query("update reviews set total='$total' WHERE rev_id='$ID'");
        }


I have a feeling that isn't the most elegant way to do it but I wasn't able to get the values from the fields any other way than making variables out of each one of them first and then doing the math and then finally the sql. But given my zero experience with php I can't complain. At least I'm getting the right answer
So if there are some improvements, please let me know. I might have further questions still coming up. I guess it would be nice to understand the first two lines of that code in plain English.

[shannah]

Here is an easier way using the beforeSave() trigger.

Code: Select all

function beforeSave(&$record){
            $power=$record->val('power');
            $fuel_eco=$record->val('fuel_eco');
            $comfort=$record->val('comfort');
            $interior=$record->val('interior');
            $exterior=$record->val('exterior');
            $buildquality=$record->val('buildquality');
            $reliability=$record->val('reliability');
    $total =
round((($power+$fuel_eco+$comfort+$interior+$exterior+$buildquality+$reliability)/7) ,2);
            $record->setValue('total', $total);
}



after_action_new is a trigger that fires after the new action has successfully completed. It is most commonly used to override where to redirect the user after the new action is complete, but can be used for other things.

For this sort of thing, the beforeSave(), beforeInsert(), or beforeUpdate() triggers are probably more appropriate because these are fired during the actual save process, which may occur in many more places than just the new action.

-Steve