Buy Now
Sign in

Mix Data from multiple tables (column contains array)

Proximate , September 26 2019, 10:01
P
Proximate 122
September 26 2019, 10:01 #389304
So i got this problem here:
I have to db tables, one saves the event parameters and another table contains a list of ALL categories.
While creating a new event we can select the "available" categories based on the categories table.
Now i would like to ONLY show the available (aka for this event selected) categories in the event-registration-form.
the actual structure is the follows:
categories table (all cats listed here):
aid | name | ....
events table (all events are listed here)
aid | name | ... | ac_cats | ....
the av_cats column actually contains the "available" category keys as an array:
[arrival_a] => 2021-05-04 10:00:00
[arrival_b] => 2021-05-03 19:00:00
[av_cats] => Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
[4] => 5
[5] => 6
[6] => 8
[7] => 10
[8] => 11
[9] => 12
[10] => 13
[11] => 14
[12] => 15
[13] => 18
)

[headerimg] => 20190925152518_mountains-small.jpg
the av_cats col is handled as special field: "av_cats/json" in the read_data function.
read_data for categories(full List own read data fn):
[read_categories] => Array
(
[log] => Array
(
[0] => SELECT `Category`.`aid` AS `Category.aid`, `Category`.`name` AS `Category.name` FROM `simm_categories` AS `Category` LIMIT 100;
)

[var] => Array
(
[1] => Orienteering-Marathon lang Men
[2] => Orienteering-Marathon kurz Family
[3] => Score-Marathon
[4] => Trail-Marathon lang Men
[5] => Trail-Marathon kurz Men
[6] => Orienteering-Marathon lang Mixed
[8] => Orienteering-Marathon kurz Men
[10] => Orienteering-Marathon kurz Women
[11] => Orienteering-Marathon kurz Mixed
[12] => Trail-Marathon lang Mixed
[13] => Trail-Marathon kurz Women
[14] => Trail-Marathon kurz Mixed
[15] => Trail-Marathon kurz Family
[18] => Orienteering-Marathon lang Women
)

)
My target is to get a List of Key=>Value with only the selected Elements from read_event are used
In ChronoForms v6
Otherwise the participants are able to register for a category that does not exist for this event.
I've already tried to add Models to the read_event fn different loops and php code but did not get anything near to a solution....
Did someone already done something like this?
in PHP i would create a new array and then do something like "foreach as $k=>$v $array[$v] = av_cats get read_categories.$v"
but is this possible in chronoforms modules? (and yes i tried that already but was not able to see nor use my array)
kind regards
P
Proximate 122
September 28 2019, 08:32 #389348
ah nevermind - the PHP custom code was the right approach:
$available_cats = $this->get('read_event.Event.av_cats',array());
$cats = $this->get('read_categories',array());
$event_categories = array();

foreach($available_cats as $v){
$id = intval($v);
$event_categories[intval($v)] = $cats[intval($v)];
};

return $event_categories;