CODEWITHSUNDEEP
phpmysqlsqlconditional-operator
Jay
Jay

Reputation: 312

PHP MySQL query with condition

I got stuck with a simple query which I can't figure out why isn't doing what I expect it to do. I have 3 values set on database like this:

$measure = 'kg';
$country_code = 'DE';
$weight = '5';

WEIGHT_UNIT | COUNTRIES | MAX_WEIGHT | PRICE
kg          | DE,AT     | 10         | 25.55
lbs         | DE,AT,CH  | 5          | 15.99

My PHP query looks like this:

SELECT *
FROM `article_shipping_options`
WHERE `weight_unit` = '$measure'
    AND `countries` LIKE '%$country_code%'
    AND `max_weight` <= '$weight'
LIMIT 1;

The result I was expecting was the row with the 25.55 price.

I know I am doing something wrong here despise my 2 days search on google...any help would be mostly appreciated :)

Upvotes: 1

Views: 2213

Answers (4)

mavili
mavili

Reputation: 3424

Did you mean MAX_WEIGHT >= $weight ?

Upvotes: 1

matt
matt

Reputation: 367

You have $weight set to 5, but in the row's MAX_HEIGHT is 10.

Then the last condition for that row evaluates as 10 <= 5. Since the condition was not met, the row was not returned.

Upvotes: 0

jakber
jakber

Reputation: 3569

I think you have the wrong inequality operator. Shouldn't it be max_weight >= '$weight'?

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Try using FIND_IN_SET() and use max_weight >= '$weight'

SELECT * 
FROM   article_shipping_options 
WHERE  weight_unit='$measure' AND 
       FIND_IN_SET($country_code, countries) > 0  AND 
       max_weight >= '$weight' 
LIMIT  1;

Upvotes: 0

Related Questions