Databasejoin filtered with user id

[Teide]

Hi there,

Been trying to sort this out for a while with no luck...

Will explain what I have:

List 1 called parent with info about parent that is imported. it's has a member_id which more that one parent can have.
List 2 called childrens in the same situation as parents list, with a member_id which more than a child can have.
List 3 called user which is populated by parents and used juser to create a joomla user and have a member_id. once a parent registered the member_id is checked.

List 1 and 2 has a join to list 3 using the member_id in order to get the user_id so list 1 and 2 are prefiltered with the user_id using {$my->id} so parents can only see their related info.

Up to know everything works perfect.

A new list 4 called activities is created also imported by csv with info related to the activities childs can do.
Another list 5 is needed where parents can sign their childs in the activities.

In this form 5 a database join element was created so the parent can only choose the children that member_id is the same as the logged user. This is where the problem is as all parents can choose all childrens.

Any ideas?

Thanks

 

[cheesegrits]

Add a "WHERE" filter to the join, that does something like ...

WHERE {thistable}.member_id = (SELECT member_id FROM list3 WHERE userid = '{$my->id}')

Obviously change table and field names as appropriate (but leave {thistable} as is).

-- hugh

 

[Teide]

Hi Hugh,

Thanks for this and for fabrik.

Works perfect!

 

[Teide]

Hi,

Another query if anybody can help.

List 4 called activities has a checkbox element called age which states what children can participate.
List 2 also has the age of the child as calc element as what is imported is the date of birth.

In the form 5 after selecting the child the parent has to select an activity. What i am trying to achieve is using a database join element only show the activities that matches with the kids age. Form 5 has an element field that using the autofill copy the age of the childs from list 2.

Is this posible or should I try other options?

Thanks

 

[cheesegrits]

You should be able to achieve that if you use a WHERE clause with a placeholder for the age element, and enable AJAX updating. With AJAX update enabled, we then watch any elements you have used as placeholders in the WHERE clause, and do an AJAX call to rebuild the join's options whenever any of those elements are changed.

So theoretically, when the autofill happens that changes the age element, that should trigger the join element to rebuild itself, using the new value of the age in your WHERE clause.

-- hugh

 

[Teide]

Hi Hugh,

It works when the age in the activity list is set us a field with only one age but not if the element is a is checkbox as each activity is available for more than one age. I would like that depending on the child age, only activities that includes that age becomes available for him to choose in the database join field.

For example, let's say activity football is for children ages 4-5-6-7-8-9-10. So a child ages 7 can choose to play football but a child age 3 cannot.

Thanks

 

[Teide]

Hi,

Worked a way around: created a field element "from age" and another field "up to age" and the used AND and worked fine.

Thanks