Calc element that count number of choise in multiselect-dropdown-databasejoin

[blue-mouse]

Hi,

I try to find how to write formula for my calc element:
{ccjom_cc_szkolenia___Il_kopi }
that counts number of selelection in other field (in the some form1) that is databasejoin with multiselect-dropdown:
{ccjom_cc_szkolenia___multipersons}
--- this field includes users/persons - and we should select amoung from all users those workers who are trained on training-x.
We need to have field: {ccjom_cc_szkolenia___Il_kopi } that store numbers of trained workers on training-x.

Can you help how to write formula for calc element?

 

[puzzled]

Hi - You may have looked in the wiki already but I found this article quite useful for a similar thing:
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/

 

[cheesegrits]

The multi-select join will have automatically created a many-to-many join, called something like ccjom_cc_szkolenia_multipersons_repeat. This table will have a field called parent_id, which points to the PK of your main ccjom_cc_szkolenia table, i.e. it is the FK (foreign key) which relates the repeat row to the main row.

So ... you need to count the number of rows in that table, which have a parent_id which is the rowid of the row the calc is in.

So ... assuming the PK element for your ccjom_cc_szkolenia table is called 'id', something like this should work:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select("COUNT (*) AS total")->from("ccjom_cc_szkolenia_multipersons_repeat")->where("parent_id = '{ccjom_cc_szkolenia___id}'");
$db->setQuery($query);
return $db->loadResult();

Note that, as with any use of db querying in a calc element, this could be very inefficient when displaying a list, as it will add a query for every row being displayed.

-- hugh

 

[blue-mouse]

But this doesn't work for me,

I try also this in other way:
I changed an element {ccjom_cc_szkolenia___Il_kopi} to element type "field" and try use form php plugin to store number of selected users in field {ccjom_cc_szkolenia___multipersons} in that field,

But the code doesn't work also and i get an error: NULL
Code:
// $db =& JFactory::getDBO();
// $recipient2 = '{multipersons}';

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$id = '{ccjom_cc_szkolenia___id}';

$query->select("COUNT (*) AS total")->from("ccjom_cc_szkolenia_repeat_multipersons")->where("parent_id = '{ccjom_cc_szkolenia___id}'");
$db->setQuery($query);
$result1 = $db->loadResult();
// return $result1;
var_dump($result1);
exit;

$query1 = "UPDATE `ccjom_cc_szkolenia` SET `Il_kopi`='$result1' WHERE `id`='$id'";

$db->setQuery($query1);
$db->query();

 

[troester]

In all your different threads try var_dumping your inputs (placeholders etc.) to see what you get there.
They do obviously not contain what you expect and what you need in your queries.

 

[blue-mouse]

Hi,

I don't know what was wrong with my code in post #4, but i modified this something and now
code works:
// $db =& JFactory::getDBO();
// $recipient2 = '{multipersons}';

$db =& JFactory::getDBO();
$id = '{ccjom_cc_szkolenia___id}';

$query = "SELECT COUNT(*) AS total FROM `ccjom_cc_szkolenia_repeat_multipersons` WHERE `parent_id`='$id'";

$db->setQuery($query);
$result = $db->loadResult();
// var_dump($result);
// exit;
// return $result;


$query1 = "UPDATE `ccjom_cc_szkolenia` SET `Il_kopi`='$result' WHERE `id`='$id'";

$db->setQuery($query1);
$db->query();