PHP从2个数字之间的mysql获取数据

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

Answer 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