[RESOLVED] Using IN and something that doesn't work right...

vaaaska

I have this query that doesn't work properly:

$rs = $db->fetchArray("SELECT ".PX."objects.id,title,obj_ref_id,
obj_name,user_title,".PX."objects.udate,".PX."objects.cdate,
".PX."objects.cby,status 
FROM ".PX."objects,".PX."objects_prefs,".PX."users 
WHERE ".PX."objects.obj_ref_id=".PX."objects_prefs.obj_id 
AND ".PX."objects.status='$status' 
AND ".PX."objects.creator = ".PX."users.ID 

AND ($user[ID] IN (obj_members))

ORDER by ".PX."objects.udate $order LIMIT $page,$user[view_lim]");

I held out the line in question above...if I insert the numbers that are in the table for obj_members it works just fine...

($user[ID] IN (36,1))

But if I use 'obj_members' as in the above it simply won't work. I've tried a variety of quotes around these things to no avail. I think it should work but perhaps logically it won't (which I don't get).

Any ideas?

thanks...

tomhath

AND ($user[ID] IN (obj_members))

What is obj_members? It looks like you meant to use a php variable, did you mean $obj_members?

vaaaska

No, obj_members is a column in the table. Apparently what I'm trying to do isn't possible this way though...

Roger_Ramjet

Rather than tell you how to fix this I'm going to show you how it should be written: with JOINS

I know you are specifying the join conditions in the WHERE clause but that is the worst way to do it.

What you need here are inner joins

 $rs = $db->fetchArray("SELECT ".PX."objects.id,title,obj_ref_id,
obj_name,user_title,".PX."objects.udate,".PX."objects.cdate,
".PX."objects.cby,status
FROM ".PX."objects 
INNER JOIN ".PX."objects_prefs ON (".PX."objects.obj_ref_id=".PX."objects_prefs.obj_id)
INNER JOIN ".PX."users ON (".PX."objects.creator = ".PX."users.ID) 
WHERE ".PX."objects.status='$status' AND obj_members=$user[ID] 
ORDER by ".PX."objects.udate $order LIMIT $page,$user[view_lim]");

The one thing I'm not sure about is which table that obl_members column is in. Should not matter if the name is unique though.

vaaaska

But... obj_members=$user[ID] would never work. obj_members will come in the form of an imploded array (3,54,1,32). obj_members is in the object_prefs table...

There are two things I really struggle with...understanding regex (better) and the greater powers of mysql. First of all, the mysql manual is nowhere near as great as the php manual (user comments galore!) and I find their explanations much too ellliptical and/or vague.

I do use the occasional inner join...but your example clarifies things a little in my mind about them. 😉

tomhath

This might do what you are thinking of, but it will have other problems:

AND obj_members REGEXP '$user[ID]'

You'll need to work out the regular expression so an ID of '6' doesn't match a string like (36,1). Might be something like this:

"AND obj_members REGEXP '[(,]" . $user[ID] . "[,)]'";

vaaaska

Nice! I know my use of regular expressions is pretty rudimentary but this seems to be passing my tests...

AND obj_members REGEXP '(^$user[ID],)|(,$user[ID],)|(,$user[ID]$)'

Thanks, really, alot for the help on this one - it's saving me from doing a heck of alot of queries...explodes...array_mergers...implodes...inserts for something that isn't really all that important...

😉

Roger_Ramjet

Well, I said I was not sure about the obj_members, now I know why.

Should be storing that data in a related subtable that holds obj_ids and user_ids and thus realtes an object to it's members. Like that you can also relate users to the objects they are members of just as easily. Simple joins instead of having to use regular expressions etc.

table obj_members
obj_id
user_id

You can then do things like
find the objects with the most members
users likewise
the average members/project memberships
objects with no members
users likewise
correlations between memberships:
members of obj 112 are never members of obj 67
75% of obj 112 members are members of obj 113