Query Issue

[holowugz]

Hey i have a problem,
on my site i have a script called departments.php an example link to this script is departments.php?ID=24

the script checks if ID is a number and then runs the following query:

$query_RsDeptMembers = "SELECT * FROM vb3_user WHERE membergroupids LIKE '%{$dept_id}%')";

the column membergroupids is a comma seperated list of all of the usergroups this person is a member of.

now the query works fine, but if for example they was another member who was NOT in usergroup 24 so 24 would not be in the field membergroupids for him, but he was a member of usergroup 124, then he would be selected.

i did think about altering the query so it was like:
SELECT * FROM vb3_user WHERE membergroupids LIKE '%,{$dept_id},%')
But if ID is at the beginning then there would be no , at the beginning and if the ID was the last one in the list there would be no , at the end.

Is there another way i can search the membergroupids field to make sure that the number is there, and that it is not part of another number?

[Barand]

You could ensure your lists of numbers  do have a "," at the beginning and end (,1,3,5,24,124,).

Better is not to store lists like that but store in a separate table

Group table :
[pre]
userid  groupid
  1      1
  1      3
  1      5
  1      24
  1    124[/pre]

[holowugz]

believe me i would have done it like that,
but unfortunately this is from our vbulletin user table.
so i cant change it.

any other way?

[holowugz]

*resolved*

SELECT * FROM vb3_user WHERE membergroupids LIKE '{$dept_id},%' OR membergroupids LIKE '%,{$dept_id}' OR membergroupids LIKE '%,{$dept_id},%'

that works, cheers for the help though