Database selection

[keith21]

Hi
I have joined fabrik table with joomla groups table. I can create new records perfectly fine. Although when I open that record it does not show the multi selection off groups. Database shows that is has more than one group imputed.

This is code i have used:

$db = JFactory::getDbo();
$db->setQuery("SELECT u.id, u.title, p.singlelineaddress FROM sin_usergroups u INNER JOIN sin_prid p ON u.title = p.prid WHERE parent_id = '3' order by u.id asc ");
$rows = $db -> loadObjectList();
foreach ($rows as $row) {
    $options[] = JHTML::_('select.option',$row->id,"$row->title - $row->singlelineaddress");
}
return $options;

I have attached a some pics as well.

 

[cheesegrits]

What is sin_prid?

Are you talking about a list join or a databasejoin element?

-- hugh

 

[keith21]

PRID is a property table, with property information. The prid table is joined to the usergroups table on title. I am using a the dropdown element.

Thanks
Keith

 

[cheesegrits]

I'm still struggling to understand your setup.

What exactly do you mean by:

I have joined fabrik table with joomla groups table.

-- hugh

 

[keith21]

I have joined a table called sin_prid to joomla usergroups table. The reason for doing this is to add groups to users. I can make a selection and it add it to the table correctly, only on edit it does not select groups.

 

[keith21]

So I found out what the issue was. As soon as I use the JUser plugin for Forms and select Property(PRID) field as the User group field, it does not retrieve the record. So I'm guessing that plugin has the issue. I have added some pics.

 

[cheesegrits]

Yup, the group field is expecting a single ID.

I'll be working on the juser plugin this week, with what I think is a similar issue. I'll let you know if I come up with anything.

-- hugh

 

[keith21]

That is brilliant.
Thanks

 

[keith21]

Hi Hugh
Did you manage to look at this over the weekend ?

Thanks

 

[cheesegrits]

Well, yes and no. The site I was working on, I was able to solve their issue of the groups getting blown away on edit by using the 'usergroup' element as the element the juser plugin uses for the group ID's. And our 'usergroup' element has a specific features that let you specify whether it should be set to whatever the groups the logged on user belongs to when the form is edited, ot whether to specify a 'user' element on the form the usergroup element should loo at and retrieve the groups for that user one edit.

So what we did on that site was use a simple hidden field for the copy of the form we use for users to add themselves, preset to the groups we want, then a different copy of the form for editing, with the usergroup element.

-- hugh

 

[keith21]

HI Hugh
Any idea when the multi group thing will be sorted on juser plugin?

Thanks
Keith

 

[cheesegrits]

As per my previous post, it is sorted. If you need to use multiple groups, use the usergroup element.

-- hugh

 

[keith21]

Hi hugh
I need to have it as a dropdown list linked to the juser plugin. Can you guide me to where I can make the changes and what file?

Thanks
Keith

 

[cheesegrits]

Not sure what you mean. A database join configured as a dropdown? That's a single value.

-- hugh

 

[keith21]

Its a dropdown with multi selection. It works fine without the juser plugin, but as soon as I add the juser plugin it fails to show the multi selected items on edit mode. I guess that the juser plugins does not work when you have more than one group selected. It seems to only select one item only.
The code I am using is :

$db = JFactory::getDbo();
$db->setQuery("SELECT u.id, u.title, p.singlelineaddress FROM sin_usergroups u INNER JOIN sin_prid p ON u.title = p.prid WHERE parent_id = '3' order by u.id asc ");
$rows = $db -> loadObjectList();
foreach ($rows as $row) {
    $options[] = JHTML::_('select.option',$row->id,"$row->title - $row->singlelineaddress");
}
return $options;

 

[keith21]

Not to worry I have fixed it now.
Thanks
Keith

 

[cheesegrits]

What was the fix?

-- hugh

 

[keith21]

I am using database join, under data -> contact label = {thistable}.title,' - ',(SELECT DISTINCT sin_prid.singlelineaddress FROM sin_prid WHERE sin_prid.prid = {thistable}.title)
and data where = where {thistable}.parent_id = '3' AND title NOT LIKE '%Editor(View_Edit)%'