Jump to content

Query on a list range


JohanM

Recommended Posts

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 by JohanM
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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
;

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.