Auto populate a field

[gstyles]

Good day all, I have a table that has test results in and "date of test" is one field "frequency of test" is another with these 2 fields I need to populate a third field "next test"
The SQL I am trying to get to run automatically is
UPDATE gypsum
SET next_test = DATE_ADD(date_of_test, INTERVAL test_frequency MONTH);
Now this works a treat manually via SQL Yog or PHPMyAdmin how do I get this to run within Fabrik? A little guidance please.
Thanks gws.

 

[sales2010]

Hello,

Use php plugin in form. For php syntax, see here

http://fabrikar.com/forums/showpost.php?p=153294&postcount=3

 

[gstyles]

Thank you for your reply sales2010 but I am not a php coder and your example confuses me.
$sstock = $db->loadObjectList(); What is this?
$sstockcalc = $sstock->stock_number - $snum; and this?
foreach ($sstock as $sstock1) { and this?
$squery = "UPDATE jml_fb_sw_offers_stocks SET stock_number = $sstockcalc WHERE parent_id = $soid";
$db->setQuery($squery);
$db->query();
}

 

[sales2010]

..I am not a php coder and your example confuses me.

sorry


so, you have two fields and want to update a third one. To get the values from fields, you can use placeholders:

$date_of_test = '{yourtablename___date_of_test}';
$test_frequency = '{yourtablename___test_frequency}';

then connect to the database:

$db =&JFactory::getDBO();

then run the query in php:

$query = "UPDATE gypsum
SET next_test = DATE_ADD('$date_of_test', INTERVAL '$test_frequency' MONTH)";
$db->setQuery($query);
$db->query();

i'm not very sure about the syntax for Update, never tried this kind of query.

 

[gstyles]

Thank you so much, this almost works,It runs and updates all records to the same date ie whatever record I edited or when I created a new record all other records were updated to that value. Very strange as the sql query works a treat in phpmyadmin.

 

[gstyles]

Hi sales2010. Thanks very much ,this works.EDIT no it doesn't I was looking at the wrong record.silly me.
$date_of_test = '{gypsum___date_of_test}';
$test_frequency = '{gypsum___test_frequency}';
$next_test = {gypsum___next_test}
$db =&JFactory::getDBO();
$query = "UPDATE gypsum
SET '$next_test' = DATE_ADD('$date_of_test', INTERVAL '$test_frequency' MONTH)";
$db->setQuery($query);
$db->query();

 

[gstyles]

Hi all, this nearly works except it adds the new date to all records not just the one being edited.The sql query works in phpmyadmin as it should but when it is called via the example above (kindly done by sales2010) it doesn't do what it should. Does anybody have an idea how to sort this? Thanks gws.

 

[sales2010]

Hello,

Sorry for late response.

If you don't want to update all the records, you should add a 'where' clause in query. To update only current record, add this to php. This code will retrieve current primary key:

$id={rowid};

Then in query put the where condition before ";

Where id ='$id'

 

[gstyles]

You sir are an absolute star. Thank you very much for all your help.It now does what it should and I have a much better idea of how the php plugin now works.