select
columnB, count(columnB) as count_matching_rec
from
table_name
where
columnA = $serch_term1 and columnA = $serch_term2
group by
columnB
having
count(coulmnB) = 2

**This might be what you want... the issue would be if you added a 3rd search term you would have to change the "HAVING" clause to be 3... like this:

having
count(coulmnB) = 3

I am not sure if this is 100% right but post back and we will see if we can get it.

Basically what is happening in this query is we are counting the amount of identical coulmnBs that are being returned then telling the query we only want them if it has a count of 2... hopefully indicating that it matched both search terms.

This did not return any results, only a blank page.

Nevermind, I believe I fixed it. I used OR instead of AND

Right, sorry now that I think about it OR would be right.

Do you have it doing what you want or does it need some refining?

This query will break if it matched search_term1 twice

If you have data like this you will get a bad result

example.net ¦ websiteA.com
example.ca ¦ websiteA.com
example.net ¦ websiteA.com

If you input 'example.net' and 'example.org' then "websiteA.com" would still be returned because it matched 'example.net' 2 times but not example.org... this would be a false positive if I understand this correctly. Could the data appear this way?

Demaestro you are right. The query is not breaking, but it is returning 2 for your example. How would I remedy this?

Anyone else have any thoughts on this?

Marty, 1 way to avoid this would be to control duplicate entries, if that isn't possible then you can try to do this in 2 queries then do some work in code to find ones that match both.

select
'term1' as term_match, columnB
from
table_name
where
columnA = $serch_term1
UNION
select
'term2' as term_match, columnB
from
table_name
where
columnA = $serch_term2

Then looping through the results check for columnB values from term1 that match columnB values from term2, discard anything else that doesn't match and keep what does.

A little more laborious but it will be more logically sound.

Alright, I took your advice and re-structured the MySQL query. I now have a question about how to loop through the data but I will post that in the PHP forum. Thanks again for all of your help!

Out of curiosity try this out.

select
t1.columnB
from
table_name t1, table_name t2
where
t1.columnA = $serch_term1
and
t2.columnA = $serch_term2
and
t1.columnB = t2.columnB

hey that worked! thanks!

Sweet... came to me while I wasn't even thinking about it.

I knew there was a way but I didn't have time to delve deep into it. My brain must have kept working on it in the background or something cause it just spit out the answer randomly.

My next task is figuring out how to adapt so that it dynamically changes depending on how many search terms can be entered (there can be anywhere from 1 to 20 terms).

Any thoughts?

SELECT count(*) as counter,B FROM temp where A in("example.net","example.org") group by B

This will show you how many matches their are for example.net and example.org

you will have to loop through the results and filter the results that have a counter of less than 2

You could put 20 search terms inside the IN statement e.g in("example.net","example.org","example.org","example.org") and so on.....

then just filter out the results returned that are less that the amount you are searching for