I'm sure there must be an accepted technique for this, but it's
something I haven't tried before, so if anyone can point me in the
right direction I'd be grateful.
I'm writing a search facility for a site where the data is stored in
several tables - let's say 5 for this example - and I want to order
my results according to where (if anywhere) matches are found. So...
Let's say I have tables 'speakers', 'topics', 'speakers_topics',
'articles', 'other'.
'speakers' is a table of speakers, with id, name and some text fields.
'topics' is a list of topics they address
'speakers_topics' relates the above two by pairs of id numbers
'articles' and 'other' are further tables of text data with possibly
more than one row for some speakers, identified by id.
I want to search the data in the following order:
name from 'speakers'
topics
text data from 'speakers'
text data from 'articles' and 'other'
...and order the results according to where in that hierarchy a match is found.
So, if the user's search term matches one speaker's name field,
another's topic and someone else's text data, that's the order in
which the results should be ordered. Also, if the same person is
matched from, say, both name and text fields (which is very likely,
as their name will almost certainly appear in some of the text), the
name should take precedence in the ordering.
To complicate matters further, I'd like if possible to extend this to
an and/or situation. If the user enters two or more words, any
results that match all the words should be ordered above those that
match only some of the words.
I can probably do this relatively easily with a series of separate
queries (I'm doing all this from PHP, by the way), but that strikes
me as inefficient. Can it all be done in one big query, perhaps with
subqueries?
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Revolution: an abrupt change in the form of misgovernment.
-- Ambrose Bierce
