Demaestro
msg:4000066 | 5:45 pm on Oct 2, 2009 (gmt 0) |
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.
|
martymac
msg:4000079 | 6:02 pm on Oct 2, 2009 (gmt 0) |
This did not return any results, only a blank page.
|
martymac
msg:4000080 | 6:03 pm on Oct 2, 2009 (gmt 0) |
Nevermind, I believe I fixed it. I used OR instead of AND
|
Demaestro
msg:4000168 | 8:47 pm on Oct 2, 2009 (gmt 0) |
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?
|
Demaestro
msg:4000171 | 8:58 pm on Oct 2, 2009 (gmt 0) |
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?
|
martymac
msg:4000178 | 9:05 pm on Oct 2, 2009 (gmt 0) |
Demaestro you are right. The query is not breaking, but it is returning 2 for your example. How would I remedy this?
|
martymac
msg:4001485 | 3:33 pm on Oct 5, 2009 (gmt 0) |
Anyone else have any thoughts on this?
|
Demaestro
msg:4001581 | 4:56 pm on Oct 5, 2009 (gmt 0) |
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.
|
martymac
msg:4001662 | 6:30 pm on Oct 5, 2009 (gmt 0) |
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!
|
Demaestro
msg:4002178 | 3:56 pm on Oct 6, 2009 (gmt 0) |
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
|
martymac
msg:4002970 | 2:38 pm on Oct 7, 2009 (gmt 0) |
hey that worked! thanks!
|
Demaestro
msg:4002972 | 2:44 pm on Oct 7, 2009 (gmt 0) |
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.
|
martymac
msg:4003144 | 6:52 pm on Oct 7, 2009 (gmt 0) |
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?
|
lukesimswilson
msg:4003639 | 10:53 am on Oct 8, 2009 (gmt 0) |
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
|
|
