Jump to content

Archived

This topic is now archived and is closed to further replies.

holowugz

Query Issue

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
*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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.