Hello.
On Fri, Sep 21, 2001 at 04:29:04PM -0500, [EMAIL PROTECTED] wrote:
> If I want to filter out records when SELECTing from a database, how do I do
> it with an
> and/or type of statement. Like this:
>
> SELECT * FROM websites WHERE keywords LIKE '%akeyword%' AND/OR description
> LIKE '%description%'
>
> I want it to show most relevant (AND) then 2nd to most (OR). Please help.
If I understand you correctly, you want all matches (i.e. OR), but
those first, which match both conditions (AND).
You could use something like
SELECT some_fields,
( IF( keywords LIKE '%akeyword%', 2, 0 ) +
IF( description LIKE '%description%', 1, 0 ) ) AS priority
FROM websites
WHERE keywords LIKE '%akeyword%' OR
description LIKE '%description%'
ORDER BY priority DESC, other_fields
This will sort matches to both first (2+1=3), then matches to keywords
(2), then matches to description. If you want matches to keywords and
to descriptions ranked equally, just replace that "2" by a "1".
Note that MySQL cannot (yet) optimize OR clauses reasonably and
therefore you should try hard to avoid them, if your table has a
significant size. In many cases, several queries will be faster.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
