Listing AND query results before OR results - mySQL

[Jesse_Stratford]

I'm making a search form for a database. Consider a situation where I wish to search first for entries which contain all the words a user provides, then for entries containing any of the words. I've gotten this working very simply by using AND and OR conjunctions in my SELECT statement; I effectively produce two statements (one looking for results with all the words and one looking for results with any word) then combine them and run them as one query.
My problem is that I don't know how to list those results which match closest (that is, results matching the AND query) first, and the results matching the OR query second. I doubt "ORDER BY" is the answer here and running two separate queries on the database doesn't seem like the right way to go either because I don't know how to combine the resources returned to PHP into one resource. Any help much appreciated.
Please let me know if I sound totally incoherent 🙂

Cheers

Jesse

[sfullman]

What it sounds like you're doing is developing a relevance algorithm. An article about horses will probably have "horse" in it many times, while a story by Clancy might refer to a "one-horse town". The first is more relevant.

You can do a lot of things with mysql functions, but I'm not aware that there is a function that will COUNT( number of occurence of a word)

You might try:

(for the phrase three dog night)

SELECT
ID,

(IF(TextField IN 'three',1,0)+
IF(TextField IN 'dog',1,0)+
IF(TextField IN 'night',1,0)
) AS Relevance
from Table
..
order by Relevance DESC

"three dog night" will produce a value of 3,
"dog night", "three dog", and "three night" will produce a value of 2,
any ONE of the three will produce a value of 1

3 would be equivalent to the AND condition, 1 or 2 would be equivalent to the OR condition :-)

This is a very crude relevance algorith but it lets the database do all the work. There's a lot of tradeoffs to consider here of course in performance.

[sfullman]

In the mysql function index, haven't used it but it looks hot, do some research on it!

Sam

http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1203

[Jesse_Stratford]

Thanks heaps Sam. In fact what you discuss (while interesting and applicable in another project i'm working on - thanks in advance) wasn't quite what I was after. My post was answerred at my own site soon after I posted (http://www.actionscript.org/forums/showthread.php3threadid=22624) with a simple bit of code that lets me combine the result sets in the order they came without duplicates.

Cheers

Jesse