Help with Dynamic Keyword Query

jnimblett

Greetings,

Problem:

I need to be able to select an image based on desired key words. The more key words that a user enters, the more narrowed down the search results will be. So, if someone were to enter the word 'beach', they would all images that have the key word 'beach'. If they were to enter 'beach, Hawaii', they would get just images that have both 'beach' AND 'Hawaii' assigned to them.

I have two tables

keywords_table (one word per row, with the image id that it is assigned to)

key_image_id
key_word

images_table

image_id
image_name

If it's important, the request will be submitted as a comma delimited list. I will be looping over the keywords and dynamically building a sql query as needed. The problem is... I can't for the life of me figure out the base query.

I am running 4.0 and can't do sub-queries.

Any ideas?

Thanks in advance

TobyRT

Get your search criteria and put them in an array, which we'll call $search

$search = implode(',', $_POST['search']);

SELECT w.key_image_id, w.key_word, i.image_id, i.image_name FROM keywords_table w, images_table i WHERE w.key_word IN ($search)

I think that'll get you at least part way there!

jnimblett

Thanks for the suggestion, Toby.

I belive doing 'IN' will create a wider result set as key words are added to the search. What I am looking to do is narrow down the results with the introduction of more search keywords. So, the results will need to have the assigned keywords 'pet' AND 'fish', rather than 'pet' OR 'fish'.

jnimblett

Here is the final answer... for those following along.

SELECT count(*) AS total, keyword, image_name, image_id
FROM keywords_table, images_table
WHERE ( keyword = 'beach' OR keyword = 'hawaii' )
AND key_image_id = image_id
GROUP BY image_id
HAVING total = 2

HAVING total = 2 //is the number of keywords that you were searching for.

Roger_Ramjet

You shold be using FULL TEXT SEARCH for this.