Query on a list range

[JohanM]

I'm bashing my head for some time now on how to resolve this issue (using PHP and MySQL) ...

$array = ['C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5']

I'm getting in request 2 parameters: value_from and value_to

Each record in the db table has those 2 columns (value_from and value_to)

 

Say for example i have 2 records looking like this:

id: 2, name: test1, value_from: C1, value_to: L3

id: 3, name: test2, value_from: C3 value_to: T5

 

in the request i get this json payload:

value_from: C4,

value_to: L2

 

with this parameters i should somehow retrieve 1 record - with the id: 2 because input parameters fit the range

Any idea how this might be possible or what should the sql query look like?

P.S. The $array is just a code representation of user selected input based on the front and yes, it cannot be changed, rearranged, meaning it has to look like that

Edited May 21, 2021 by JohanM

[Barand]

It's not pretty, but the working with comma-separated lists in SQL never is

SELECT id
     , name  
FROM johanm
WHERE FIELD('C4', 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5')
          >= FIELD(value_from, 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5')
      AND
      FIELD('L2', 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5')
          <= FIELD(value_to, 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5');

 

[Barand]

You can also do it using JSON functions (which I thought might be more elegant until I hit the problem of extracting the numeric values of the json paths returned by JSON_SEARCH() eg "$.[12]" )

SELECT id
      , name
FROM johanm j
      JOIN (
            select @jarray :=  '["C1", "C2", "C3", "C4", "C5", "T1", "T2", "T3", "T4", "T5", "T6", "L1", "L2", "L3", "L4", "L5"]'      
           ) init
WHERE
    SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', j.value_from), ']', 1), '[', -1) + 0  <= 
        SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', 'C4'), ']', 1), '[', -1) + 0
    AND
    SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', j.value_to), ']', 1), '[', -1) + 0  >=
        SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', 'L2'), ']', 1), '[', -1) + 0
;

 

[JohanM]

@Barand Tnx for the answer, but I'm getting no results on either of those 2 queries

[Barand]

They both worked when they left the shop.

DATA

TABLE: johanm   (data provided by you)
+----+-------+------------+----------+
| id | name  | value_from | value_to |
+----+-------+------------+----------+
|  2 | Test1 | C1         | L3       |
|  3 | Test2 | C3         | T5       |
+----+-------+------------+----------+

Query 1 with results...

SELECT id
     , name  
FROM johanm
WHERE FIELD('C4', 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5')
          >= FIELD(value_from, 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5')
      AND
      FIELD('L2', 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5')
          <= FIELD(value_to, 'C1', 'C2', 'C3', 'C4', 'C5', 'T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'L1', 'L2', 'L3', 'L4', 'L5');
          
+----+-------+
| id | name  |
+----+-------+
|  2 | Test1 |
+----+-------+

Query 2 with (same) results... (JSON version requires MySQL v5.7.7 +)

SELECT id
      , name
FROM johanm j
      JOIN (
            select @jarray :=  '["C1", "C2", "C3", "C4", "C5", "T1", "T2", "T3", "T4", "T5", "T6", "L1", "L2", "L3", "L4", "L5"]'      
           ) init
WHERE
    SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', j.value_from), ']', 1), '[', -1) + 0  <= 
        SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', 'C4'), ']', 1), '[', -1) + 0
    AND
    SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', j.value_to), ']', 1), '[', -1) + 0  >=
        SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_SEARCH( @jarray, 'one', 'L2'), ']', 1), '[', -1) + 0;
        
+----+-------+
| id | name  |
+----+-------+
|  2 | Test1 |
+----+-------+