PHP MySQL query with condition

Question

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 :)

Don't store comma separated values in a single database column if you're going to use it for querying. — Ja͢ck
– Ja͢ck, Commented Mar 17, 2013 at 13:12
@ypercube, I did read wiki from QUERY atg and it says, A request to retrieve information from a database or other information system. This tag can be used to refer to SQL queries against a database (SQL-Server, Oracle, MySQL, etc.) so I added it here. The last question was a mistake. — John Woo
– John Woo, Commented Mar 17, 2013 at 13:13

mavili · Accepted Answer · 2013-03-17 13:30:42Z

1

Did you mean MAX_WEIGHT >= $weight ?

edited Mar 17, 2013 at 13:30

answered Mar 17, 2013 at 13:02

mavili

3,4344 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mavili Over a year ago

but if the operator WAS the issue, it would be a solution.

Jay Over a year ago

SOLUTION: then you might have defined the max_weight field as a numerical field in which case you should remove the quotation marks around the $weight variable? – by mavili under matt post.

jakber · Accepted Answer · 2013-03-17 13:02:01Z

1

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

answered Mar 17, 2013 at 13:02

jakber

3,57923 silver badges20 bronze badges

1 Comment

Jay Over a year ago

Thanks for your reply! Yes, I've tried that too with no success. This thing is just driving me crazy :D I used operators alot in the past but this is the first time I got stuck like this.

John Woo · Accepted Answer · 2013-03-17 13:00:35Z

0

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;

FIND_IN_SET()

answered Mar 17, 2013 at 13:00

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

Jay Over a year ago

Thanks for the quick reply, I appreciate it! Unfortunatelly FIND_INT_SET does the same thing as my query is doing. I think the problem lies on the operator part.

matt · Accepted Answer · 2013-03-17 13:03:58Z

0

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.

answered Mar 17, 2013 at 13:03

matt

3671 silver badge7 bronze badges

7 Comments

Jay Over a year ago

Yes, I've tries that too...no success so far :(

mavili Over a year ago

then you might have defined the max_weight field as a numerical field in which case you should remove the quotation marks around the $weight variable?

Jay Over a year ago

BINGO! Thank you so much! I can't believe I did such stupid error. I've paid half of my sunday for it but thanks to you I can rest a bit now!

Jay Over a year ago

@everybody: Thank you all for trying to help, I really appreciate it!

mavili Over a year ago

@Jay so what was your problem??

|

Collectives™ on Stack Overflow

PHP MySQL query with condition

4 Answers 4

2 Comments

1 Comment

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related