Calculating a field to the database

A place for users and developers of the Xataface to discuss and receive support.

Calculating a field to the database

Postby ove » Wed Nov 18, 2009 11:55 pm

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!
ove
 
Posts: 2
Joined: Wed Nov 18, 2009 11:17 pm
Location: Finland

Postby Jean » Thu Nov 19, 2009 1:08 am

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
Jean
 
Posts: 259
Joined: Wed Nov 07, 2007 1:30 am
Location: Pau, France

Postby ove » Thu Nov 19, 2009 7:55 am

Jean wrote:
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.
ove
 
Posts: 2
Joined: Wed Nov 18, 2009 11:17 pm
Location: Finland

Postby shannah » Mon Nov 23, 2009 11:28 am

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
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 29 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved