|
|
Posted by Hilarion on 11/04/05 17:10
> I would like to SELECT, matching a spending limit, a currency, and then
> any matched searchwords. I'm not sure if my query below will consider
> one searchword match enough to SELECT the item. All 3 clauses must be
> met. How would I word my query please, anyone ?
>
>
> $query = "SELECT id,tracking_url,product,price,description,id_merchant
> from product WHERE price < $maxspend AND id_currency = $currency AND
> keywords LIKE '%$searchword%' OR product LIKE '%$searchword%' OR
> description LIKE '%$searchword%';"
Use parentheses:
SELECT id, tracking_url, product, price, description, id_merchant
FROM product
WHERE price < $maxspend
AND id_currency = $currency
AND (
keywords LIKE '%$searchword%' OR
product LIKE '%$searchword%' OR
description LIKE '%$searchword%'
)
You could also change three LIKEs to one LIKE operating on concatenation:
SELECT id, tracking_url, product, price, description, id_merchant
FROM product
WHERE price < $maxspend
AND id_currency = $currency
AND (keywords || product || description) LIKE '%$searchword%'
The concatenation methods differ depending on SQL dialect you
are dealing with and on the way that SQL affects NULL text concatenation.
The above should work OK in Oracle 8i SQL (where "||" is concatenation
operator and where concatenation of NULL string with not NULL string
gives the second one). For MySQL you would have to change the last
expression to:
CONCAT( IFNULL( keywords, '' ), IFNULL( product, '' ), IFNULL( description, '' ) ) LIKE '%$searchword%'
I'm not sure of it, but probably some SQL engines will perform better
with three LIKE expressions joined with OR, and some will do better
with single LIKE working on concatenated strings.
Hilarion
Navigation:
[Reply to this message]
|