Tag Based Image Search

[bagpiperdude90]

Ok, so, I'm building a photo gallery. It is a gallery of all sorts of different airplanes at different locations, etc.

 

table: photos
id | description | size | data (blob) | etc

 

 

table: tags
photo_id | tag | tag_type

 

 

Now, when a user uploads a picture, it might be of a Delta Airlines 737 at KATL (airport code for Atlanta, GA). So, these changes would be made:

 

table: photos
id | description | size | data (blob) | etc
---------------------------------------
1  | blah blah   | xxkb| blah blah    |

 

 

table: tags
photo_id | tag | tag_type
-------------------------
1 | Delta Airlines | airline
1 | 737             | type
1 | KATL           | airport

 

 

Now, I want users to be able to use a keyword search feature. In the search box, they could type in "Delta 737 KATL" and it would search the tag table for %Delta% %737% %KATL%. The results would show that photo_id 1 meets the criteria, and shows it. However, it would NOT show pictures of a Delta 737 and KDEN (Denver) - it needs to match ALL words.

 

But it WOULD match a picture of a Delta 737 at KATL taken at night (tag for that would be tag:night tag_type:time).

 

Now, I have no idea how to do that in MySQL.

 

My best guess is to have PHP figure the results. Query:

 

$query = "SELECT * FROM `tags` WHERE `tag` LIKE '%Delta%' OR '%737%' OR '%KATL%' ORDER BY `photo_id`"

 

Then, use PHP to find three of the same photo_id's, and you know you have a match.

 

However, what if the user searched for 5 terms? PHP would have to know to look for 5 of the same id's.

 

I need the tags to stay this way, because I need users to also search for "Delta" in a specific "airline" search box. So the query then would be like:

 

$query = "SELECT * FROM `tags` WHERE `tag` = 'Delta' AND `tag_type` = 'airline'";

 

Along the same lines, to search for "KATL" in a specific airport box:

 

$query = "SELECT * FROM `tags` WHERE `tag` = 'KATL' AND `tag_type` = 'airport'";

 

So, in this case, the user could either search for "Delta KATL" in the main keyword search, or fill out two separate boxes - "Delta" in the airline box and "KATL" in the airports box.

 

How would I merge the two specific searches (airline and airport)?

 

$query = "SELECT * FROM `tags` WHERE (`tag` = 'KATL' AND `tag_type` = 'airport') OR (`tag` = 'Delta' AND `tag_type` = 'airline')";

 

So, main question: How to provide keyword searching based on multiple table rows in a tag table.

 

Any ideas?

[bagpiperdude90]

Ok, maybe a better option would be:

 

Take every word the user enters and search for it using OR, then order by the most hits or something, and group by photo_id.

 

Searching for "Delta 737", lets say that applies to three tags spanned over two images.

 

photo_id: 1 | tag: 737 | tag_type: type

photo_id: 2 | tag: Delta | tag_type: airline

photo_id: 2 | tag: 737 | tag_type: type

 

Now, GROUP BY photo_id would eliminate one of the photo_id 2's. But, also, photo_id 1 would show up first, even though photo_id 2 has a higher hit count. How do I make photo_id 2 appear first, and in only one instance?

[fenway]

You can COUNT() the number of rows returned for each.