Jump to content


Photo

Query Issue


  • Please log in to reply
3 replies to this topic

#1 holowugz

holowugz
  • Members
  • PipPipPip
  • Advanced Member
  • 46 posts

Posted 09 July 2006 - 10:11 PM

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?

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 09 July 2006 - 10:30 PM

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]

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 holowugz

holowugz
  • Members
  • PipPipPip
  • Advanced Member
  • 46 posts

Posted 09 July 2006 - 10:39 PM

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?

#4 holowugz

holowugz
  • Members
  • PipPipPip
  • Advanced Member
  • 46 posts

Posted 10 July 2006 - 12:19 AM

*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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users