join query problem

prima-nocte

I'm having difficulty with my php/mysql query with joins.
It for a multi keyword search engine which i'm working on

I have these tables in my database:
search_urls
- id
- url
- kind

search_keywords
- id
- keyword

search_keyword_reference
- keyID
- urlID

search_indexed
- urlID
- indexdate

In the search_keywords are all the keywords i have spidered from the websites which are in the table search_urls.
In the table search_keyword_reference are all the references between the urls and the keywords.

I'm trying to get the results from my database with this query:

SELECT s1.id,s1.url,s1.thumbs,s1.kind,s4.indexdate
FROM search_urls AS s1
JOIN search_keywords AS s3
JOIN search_keyword_reference AS s2 ON s2.urlID = s1.id AND s2.KeyID = s3.id
JOIN search_indexed AS s4 ON s1.id = s4.urlID
WHERE s1.kind = 'movies' AND s3.keyword LIKE '%foo%' AND s3.keyword LIKE '%bar%' GROUP BY s1.id

If i use an OR in the LIKE statements i get all results which have references to "foo" or "bar" .. but i only want the urls which have both references (to "foo" AND "bar") but with the AND in there i get no results.

Does anybody see what i'm doing wrong here?

sfullman

Your basic query would be (and I prefixed the fields which I recommend to make hte queries more apparent):

SELECT
search_urls.*, COUNT(*) 
FROM 
search_urls, search_keywords, search_keyword_reference
WHERE
/** 3 tables, 2 joins properly **/
url_id = uk_urlID AND kw_id=uk_keyID AND
/** here's your actual search query **/
url_kind = 'movies' AND 
( url_keyword LIKE '%foo%' OR url_keyword LIKE '%bar%') 
GROUP BY s1.id

Make sure you've got good indexes.

Samuel