CODEWITHSUNDEEP
phpmysql
conan
conan

Reputation: 1337

trying to check value between some number mysql php

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// 

?>

Upvotes: 0

Views: 45

Answers (3)

mickmackusa
mickmackusa

Reputation: 47894

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.

Upvotes: 0

developerwjk
developerwjk

Reputation: 8659

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'";

Upvotes: 0

kuchar
kuchar

Reputation: 637

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

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

Upvotes: 1

Related Questions