membergroupids IN (18) doesnt work - why?

[kafi]

Can anybody help me with this querry? I have users that are in 2 membergroupids. That is 12 and 18.
When I write condition membergroupids = 12 it works fine and it pull out ousers that are in 12 membergroupids (aka additional usergroup). I guess this works only because 12 is recorderd as first id in table... 12,18...
But when i try conditional membergroupids = 18; or I try membergroupids IN (18) neither doesnt work.

Any idea why? How to accomplish this querry? Is it because there are two "IN" in the querry???

This is what I try and doesnt work for me:

PHP Code:

$query4 = $vbulletin->db->query("SELECT userid FROM user WHERE userid IN ($user) and membergroupids IN (18)"); 
while ($row4 = $vbulletin->db->fetch_array($query4)) {
     echo $row4['userid'].";";
     echo "<br />";
} 


Milion thanks!!!

[WhaLberg]

Are you sure that there are users whose membergroupid is 18?

[Adrian Schneider]

You have it backwards, it would be

18 IN (membergroupids)

but that won't work because it would parse the entire membergroupids list as a string and not a list.

Use,

find_in_set(18, membergroupids)

[kafi]

Hmm I tried this:

PHP Code:

$query5= find_in_set(18, membergroupids);
echo $query5.";"; 


but I have got undefined function - probably I didnt call this function? Is this function OK here anyway? Because i found it in functions.php and it is about forums not about membergroupids... Well I am not coder so maybe I am doing something wrong...

Anyway i managed it with "membergroupids LIKE %18%" query and it works fine sofar.

Thanks for your help and any further feedback is appreciated .-)

[Adrian Schneider]

It's a MySQL function not a PHP function.

Use it instead of IN

[sql]SELECT userid FROM " . TABLE_PREFIX . "user WHERE userid IN ($user) and membergroupids find_in_set(18, membergroupids)[/sql]

your way of doing the LIKE is not good as it will grab also members in group 118, 2187 etc... if you have that much usergroups.. lol

[kafi]

I am aware of that
Ok so how to make it in correct way? I am getting syntax errror.

PHP Code:

$query5 = $vbulletin->db->query("SELECT userid FROM user WHERE userid IN ($user) and membergroupids find_in_set(18, membergroupids)"); 
while ($row5 = $vbulletin->db->fetch_array($query5)) {
     echo $row5['userid'].";";
     echo "<br />"; 


Invalid SQL:
SELECT userid FROM user WHERE userid IN (17291,24998,19,8026,28994,56325) and membergroupids find_in_set(18, membergroupids);

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'find_in_set(18, membergroupids)' at line 1
Error Number : 1064

[Marco van Herwaarden]

Should be:

PHP Code:

$query5 = $vbulletin->db->query("SELECT userid FROM user WHERE userid IN ($user) and find_in_set(18, membergroupids)"); 


[Dismounted]

And if you get touchy about vBulletin Coding Standards...

PHP Code:

$query5 = $vbulletin->db->query("
    SELECT userid
    FROM user
    WHERE userid IN($user)
        AND FIND_IN_SET(18, membergroupids)
"); 


[kafi]

Thanks, it is good to learn handy stuff from beginning

Thanks to all of you, your advice with find_ind_set is working great .-)