Question about queries using search prefs contained in a table

[whitestar73]

Hi all, I'm pretty new to SQL and PhP. I've been self teaching web development over the past couple years, and I'm currently developing a few web sites for myself and others (nothing PRO... yet).

 

I'm having a dilema with a search method I'm trying to use, but basically it's searching a couple tables based on user search preferences that are contained in another table. I can do simple queries, but this concept got a little complicated because of how I'm trying to do it. Maybe I'm doing this the wrong way, but it seemed logical to me when I thought about it.

 

As an example, I have the following tables:

(the user_id is the PRI KEY in each table)

user_search_prefs (contains all user search prefs)

user_interests (contains user interests)

users (master user table, contains general user info)

 

What I want to do is get a list of user ID's from the master user table to be used later on by doing essentially, the following:

SELECT user_id FROM users

WHERE (search prefs intersect with information in users and user_interests)

 

I want to do something like:

(step 1) SELECT * FROM user_search_prefs WHERE my_user_id = $uid

 

(step 2) then using PhP to get the search prefs to be used in the WHERE clause in the query in step 3

 

(step 3)

SELECT user_id FROM users

WHERE users.age = '$age' AND user_search_prefs.interest1 = '$interest1' etc...

 

As a test, I tried a simple query of known values in PhPMyadmin, and got an error returned saying something like "unknown column 'users.age' in the WHERE clause". I -thought- you could reference tables and columns like this, but it seems that this can only be used when you're using aliasing?

 

I had a thought about using INTERSECT in the WHERE clause like:

WHERE

(select * from user_search_prefs WHERE user_id = '$myuserid' INTERSECT select * from user_interests)

AND

(select * from user_search_prefs WHERE user_id = '$myuserid' INTERSECT select * from users)

 

But... this concept is a bit over my head. It's obviously more than a simple SELECT FROM WHERE query.

 

I'd like to know if I'm going in the right direction, and if not, where I -should- be going.

[fenway]

UNION?

[whitestar73]

OK, now I see what the main problem was. INTERSECT isn't supported by mysql. It's funny because the sql book I have uses mysql as its teaching tool, but goes over the intersect command anyway even though it's not supported.

 

So, yes it seems I'll have to try UNION.

 

Back to the drawing board... DING DING  ROUND 2!!