Posted by Kenoli on 12/17/06 00:02

I am doing a compound lookup by deriving person_id values for a query
from various tables by selecting arrays of person_id values that meet
my criteria from those tables. I am ending up with a query whose logic
would be something like:

SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '2'
OR person_id = '4' OR person_id = '6' OR person_id = '10') AND
(person_id = '1' OR person_id = '4' OR person_id = '10' OR person_id =
'20') AND (person_id = '4' OR person_id = '7' OR person_id = '6' OR
person_id = '10');

I have the id values from the lookup tables in separate arrays. I'm
thinking that the equivalent to the above query would be an array made
up only of values that show up in all three arrays. I could then
create a simpler query like:

SELECT * FROM tbl_person WHERE first_name = "John" AND (person_id = '4'
OR person_id = '10');

(The person_id values here are ones that show up in all three of the
parenthetical expressions in the first query.)

Does this make sense? Is there a php function that will create an
array from several arrays that is made up only of the values that show
up in all the arrays? If not, any idea how to do this?

Is there a simpler way entirely for approaching this entire issue?
Here is the setup:

I have three tables, each of which has criteria a person may search on
like:

tbl_person
person_id
name
region
member(y/n)

tbl_interest (this is a linking table so there may be several interest
areas associated with each person_id)
person_id
Interest_id

tbl_contact
person_id
contact_id
city
State
Country

>From a form people will enter values for the columns in these tables.
The way I am doing this now is to derive the person_id values from each
table associated with the lookup values from the form. Then I am
combining them into a final query I will use to select rows from the
person table as indicated above.

Any ideas for simplifyting this would be greatly appreciated.

--Kenoli

[Back to original message]