PHP get data from mysql between 2 numbers

Question

GAME LEVEL TABLE                    PLAYER TABLE
------------------------            ------------------------
ID  MAX-NOTE    STATUT              ID  PLAYER      NOTE
------------------------            ------------------------
1   1000        LEVEL-I             1   PLAYER1     500
2   2000        LEVEL-II            2   PLAYER2     1200
3   3000        LEVEL-A             3   PLAYER3     100
4   4000        LEVEL-B             4   PLAYER4     3500

is it possible to do something like, echo STATUT for each PLAYER

EXAMPLE : if player has note between (0 and 1000) echo 'LEVEL-I'

but if player has note between (1000 and 2000) echo LEVEL-II ......

i can see how to do it if you alter the game level table to add a min so for id 1 min would be 0, id 2 min=1001 etc. — user6763587
– user6763587, Commented Dec 17, 2017 at 22:26

miknik · Accepted Answer · 2017-12-18 18:31:42Z

1

If you alter your database query you can generate this info on the fly and have it returned as an extra column, eliminating the other table completely:

SELECT ID, PLAYER, NOTE,
    (CASE
        WHEN NOTE < 1000 THEN 'LEVEL-I'
        WHEN NOTE BETWEEN 1000 AND 2000 THEN 'LEVEL-II'
        WHEN NOTE > 2000 THEN 'LEVEL-III'
    END) LEVEL
FROM table

Or you can use a subquery like this:

SELECT ID, PLAYER, NOTE, (SELECT STATUT FROM game_table WHERE NOTE < MAX-NOTE ORDER BY MAX-NOTE DESC LIMIT 1) as LEVEL
FROM table

edited Dec 18, 2017 at 18:31

answered Dec 17, 2017 at 22:51

miknik

5,9671 gold badge13 silver badges28 bronze badges

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

3 Comments

user6763587 Over a year ago

i assume he wants to join on the other table, so you cant hard code the lower-upper values

Bynd Over a year ago

I want to do it automatically, that will tack the level from notes automatically

miknik Over a year ago

My second suggestion will read the levels from the levels table automatically

Collectives™ on Stack Overflow

PHP get data from mysql between 2 numbers

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related