[thelist] using MATCH in MYSQL

[Dunstan Orchard]

Hi there,

I want to run the following _roughly_ drafted query, can anyone help me tidy
it up into proper SQL language please?


SELECT * FROM images, imagekeyword, imagelocation, imagecategory WHERE MATCH
(images.title) AGAINST ('bird flight sailor') || MATCH (imagekeyword.keyword)
AGAINST ('bird flight sailor') || MATCH (imagelocation.location) AGAINST
('Australia USA') || MATCH (imagecategory.category) AGAINST ('Animals People')


Example of 'images.title' DB entry = "Gull in flight" (many words per row,
TEXT)

Example of 'imagekeyword.keyword' DB entry = "Bird" (one word per row,
TINYTEXT)

Example of 'imagelocation.location' DB entry = "Australia" (one word per row,
TINYTEXT)

Example of 'imagecategory.category' DB entry = "Animals" (one word per row,
TINYTEXT)


Basically I am giving people this search page:
http://www.1976design.com/testarea/photo/search.php

and trying to produce a query to get the results of their form input.


[1] I want to search 'images.title' and 'imagekeyword.keyword' against the
(possibly multiple) search term input(s).

[2] Then I want to search 'imagelocation.location' against the (possibly
multiple) location input(s).

[3] Then I want to search 'imagecategory.category' against the (possibly
multiple) category input(s).


And then produce a result set.

One of the problems I face is that only 'images.title' is a TEXT field, all
the others are TINYTEXT and so can't be indexed. Is it worth me turning all
the other fields into TEXT so they can be indexed and searched using MATCH?


Thanks for any advice,


- dunstan (p.s. could I get a CC please? Thanks)

---------------------------
Dorset, England
http://www.1976design.com/
http://www.orchard.it/
http://www.maccaws.org/