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:
$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:
$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. :p

Use it instead of IN

[nexialys]

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

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.

$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:

$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...
$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 .-)

[Opserty]

And if you get touchy about vBulletin Coding Standards...
Don't you mean query_read() :p

[Marco van Herwaarden]

I guess he means that you should use table-prefixes. ;)

[kafi]

I thought he is talking about "layout" ... :)