Forum Moderators: coopster
i am adding a search capability in my webpage, i need to search for words selected by users using buttons in a form. i use this cause they will be searching for a word per field on the table.
my query will be somethign like this:
SELECT t1.f1, t1.f2, t1.f3, t1.f4 from t1 where t1.f1="red" ¦¦ t1.f2="manual" ¦¦ t1.f3="4WD" ¦¦ t1.f4"new";
the query work fine but the results are not sorted by number of matches, example if car1 is red, manual, 4WD, and new it should be the first one cause its "score" in matches is 4, then car2 if "score" is 3 will come next in the list, those with only one matching field will be the last ones, of course if no matching fields the car will not be in the results.
how can i sort this? is there a way i can sort this result before passing it to PHP for displaying, or just pass the raw, unsorted result in PHP, store it in an array then sort it there, in any ways i need your help guys to do the sorting.
NOTE: there is this same thread here:
[webmasterworld.com...]
but they dont sort the result for the "search all words".
$sql = "(SELECT f1, f2, f3, f4 FROM t1 WHERE f1='red' && f2='manual' && f3='4WD' && f4='new') UNION (SELECT f1, f2, f3, f4 FROM t1 WHERE f1='red' && f2='manual' && f3='4WD') UNION (SELECT f1, f2, f3, f4 FROM t1 WHERE f1='red' && f2='manual' && f4='new') UNION (SELECT f1, f2, f3, f4 FROM t1 WHERE f1='red' && f3='4WD' && f4='new') UNION (SELECT f1, f2, f3, f4 FROM t1 WHERE f2='manual' && f3='4WD' && f4='new') UNION (SELECT f1, f2, f3, f4 FROM t1 WHERE f1='red' ¦¦ f2='manual' ¦¦ f3='4WD' ¦¦ f4='new')";
This query will give first results that match all 4, then any 3, then rest unsorted by match.
You can always shorten it. And maybe there's really such function that works as google and someone here will know it.
Best regards
Michal Cibor
