Special Query (WHERE)

[Sarcoth]

I'm trying to get my query to only pull usergroups that have been selected. This isn't a permission to view thing, it is more like choosing which usergroups will be displayed.

Here is my current query:

PHP Code:

$users = $db->query_read_slave("
    SELECT user.*, usergroup.usergroupid, usergroup.title, user.options, usertextfield.*, userfield.*,
    IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
    FROM " . TABLE_PREFIX . "usergroup AS usergroup
    LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.usergroupid = usergroup.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
    LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
    LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid)
    LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
    WHERE (usergroup.showrosteraddpermissions = 1)
"); 


This works great, but it requires that I add a column to the usergroup table and then I have to go into each usergroup via the admincp and select YES if I want them to be added to the query. It can be a bit tiresome doing it that way.

What I'd like to do is to set up a option menu for my mod and just type in the usergroups I want to be allowed in the query. Here is what I have in my product.xml file:

	<settinggroup name="showroster" displayorder="65535">
		<setting varname="showroster_displayed_groups" displayorder="10">
			<datatype>free</datatype>
			<defaultvalue><![CDATA[19,13,24,20,17,18]]></defaultvalue>
		</setting>
	</settinggroup>

I also have the correct phrasetype's in there as well and this is appearing in the options menu when the xml is installed. The only problem is that I can't figure out what to do with my query so that only the usergroups in the showroster_displayed_groups appear in the query.

I've tried a lot of different idea's, but it always ends in a database error. Does anyone have any thoughts on what I could do. Even a small suggestion in the right direction would be appreciated.

Thank you for your time.

[Sarcoth]

So, here is what I am thinking. I added the following to my .php code.

PHP Code:

$displaygroups = array();
$displaygroups = explode(',', $vbulletin->options['showroster_display_groups']);
$users = $db->query_read_slave("
    SELECT user.*, usergroup.usergroupid, usergroup.title, user.options, usertextfield.*, userfield.*,
    IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
    FROM " . TABLE_PREFIX . "usergroup AS usergroup
    LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.usergroupid = usergroup.usergroupid OR FIND_IN_SET(usergroup.usergroupid, user.membergroupids))
    LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
    LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid)
    LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
    WHERE (user.usergroupid IN $displaygroups)
"); 


Obviously, that isn't working since $displaygroups isn't working in the query because it is an array. I'll keep researching and trying to figure something out. Hopefully someone will see my error here.

--------------- Added [DATE]1267129932[/DATE] at [TIME]1267129932[/TIME] ---------------

Okay, I just got another idea.

How about I create a new table called showroster and a field named displaygroups. How can I take the comma seperated numbers from my $vbulletin->options['showroster_display_groups'] field (from the options menu) and dump them into that table and field?

I think if I get that far, I could add a WHERE to the query to compare that with the user.usergroupid.

Perhaps: WHERE (user.usergroupid = showroster.displaygroups)

?

[Sarcoth]

I'm having way too much difficulty getting the query to work the way I want it...so how about I try another route.

I have the setting variable "showroster_displayed_groups" in my options menu. It has the default values of 19,13,24,20,17,18. Would the following code (which I posted above) read them into an array like I think it should?

PHP Code:

$displaygroups = explode(',', $vbulletin->options['showroster_display_groups']); 


If so, since userinfo['usergroupid'] is a working array variable on my page, could I use a foreach control to pull out each number from my $displaygroups array and compare it to the userinfo['usergroupid']?

How could I write the foreach control to do that?

[Sarcoth]

Well, apparently it is difficult to get help on this sort of thing, but I think I finally figured it out.

This probably wasn't the best way to do it, but I tried it so many different ways I don't know what else I could have done.

I have a setting called showroster_display_groups which is a comma separated ID list. The admin enters the usergroupid's that they want to appear on my roster in the Settings page.

I added this to my showroster.php (custom) file:

PHP Code:

$displaygroups = array();
$displaygroups = explode(',', $vbulletin->options['showroster_display_groups']); 


Further down in the file, there is the query. Since I couldn't figure a way to get the WHERE to work, I removed it. That opened my roster up to including every usergroup.

A few lines below the query, I have a while statement that pulls the data to use from the query (at least that is what I think it does).

I added this at the start of the while statement:

PHP Code:

foreach ($displaygroups AS $mygroups) {
    if ($mygroups == $userinfo['usergroupid']) { 


I closed that at the end with two brackets and BINGO. It finally worked.

I can now remove my bitfield.xml file from my mod and focus on using the single .php file for my mod.

Next I need to figure out how to pull variables from my custom php file and use it in plugins. If anyone has any suggestions on that, please see my other thread.

https://vborg.vbsupport.ru/showthread.php?t=236314