trying to check value between some number mysql php

Question

I want to check if dis is greater or less or between a value. Look at my code, for example, if I got a dis A and val 2, then I will get dis A, but if I got dis A and val 1, then return false because value 1 is not greater than table val 1. My code already can achieve this. But, I don't know how to do the between, e.x, I make two row B on my table, less than 9 and greater than 4. how to check when I have dis B and val is 7, and it is between 4 and 9, and return dis B. Help please. I could alter the table structure if need.

<?php
dis  val  op
A     1   greater
B     9   less
B     4   greater
D     4   less

 SELECT dis 
 FROM $table 
 WHERE dis = 'A' AND
 CASE WHEN op = 'greater' THEN val < $val ELSE val > $val END// 

?>

It is already there, CASE WHEN op = 'greater' THEN val < $val ELSE val > $val END already done the job, already can check the dis value greater than a value or less then a value. — conan
– conan, Commented Aug 9, 2016 at 22:21

kuchar · Accepted Answer · 2016-08-09 22:04:01Z

1

Try this http://www.w3schools.com/sql/sql_between.asp .

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

answered Aug 9, 2016 at 22:04

kuchar

6371 gold badge8 silver badges24 bronze badges

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

Comments

developerwjk · Accepted Answer · 2016-08-09 23:36:47Z

0

You don't use CASE in a WHERE clause. You use CASE in the SELECT field list to create a calculation field that returns a different value base on some if-logic:

$sql = "SELECT dis,
  CASE WHEN val > $min and val < $max THEN 'between' ELSE 'nope' END as 'BetweenOrNot'
FROM $table 
WHERE dis = 'A'";

edited Aug 9, 2016 at 23:36

answered Aug 9, 2016 at 23:28

developerwjk

8,6342 gold badges20 silver badges33 bronze badges

Comments

mickmackusa · Accepted Answer · 2018-01-05 02:53:18Z

Unless I am misunderstanding something about your design intentions, you are dealing with a sub-optimal database structure which is leading to convoluted querying.

Please consider this table structure/data:

CREATE TABLE `test` (
  `dis` varchar(10),
  `min` int(3) UNSIGNED NOT NULL DEFAULT 0,   // assuming you can assign an arbitrary min
  `max` int(3) UNSIGNED NOT NULL DEFAULT 100  // assuming you can assign an arbitrary max
);

INSERT INTO `test` (dis,`min`) VALUES ('A',1);
INSERT INTO `test` VALUES ('B',4,9);
INSERT INTO `test` (dis,`max`) VALUES ('D',4);

This will provide a table of data like this: (SELECT * FROM test)

|  dis  |  min  |  max  |
+-------+-------+-------+
|  A    |  1    |  100  |
|  B    |  4    |  9    |
|  D    |  0    |  4    |
+-------+-------+-------+

From this setup, you can simply call sql's BETWEEN to seek your qualifying rows. Notice that you reduce table bloat by only storing each dis value once -- these can even be PRIMARY KEYS which will make everything faster/better.

Now you can call this kind of query:

$val=5;
$query="SELECT dis FROM test WHERE dis='A' AND $val BETWEEN `min` AND `max`";

And expect a single/distinct dis in the resultset.

|  dis  |
+-------+
|  A    |
+-------+

You can have a play with this Demo Link.

See also how SELECT * FROM test WHERE 5 BETWEEN min AND max will return rows A and B.

Collectives™ on Stack Overflow

trying to check value between some number mysql php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related