table with ranges

[Phibermaster]

Hi all!

I am very new to php/mysql development so i might be asking something very obvious. But here is my problem:
I want to create a table where different ranges of scores is set to a standardized score. The problem is that i dont know how to set a range in mysql. For example, lets say that the scores from 0-3 equals 1, scores 4-6 equals 2 in the table etc. When i use my php-script i want to use a score from the webform of say 5 and then get the correct sttandardized score of 2. So, how do i set this up in mysql?

If i dont make any sense, i will give you more explanation!

[Phibermaster]

More info

The table have fifteen columns. One of those is the column for the standardized value which i a number from 1 to 19. Then i have fourteen others columns where different ranges of scores will fall into one of the standardized values. The ranges in the different cells are all different, and can be like the above example, but also a range of scores from say 110-129.

[NogDog]

If I understand correctly, I would probably have 2 columns:
score (primary key)
standard_value
Then to get the standard value for a given score:

$sql = "SELECT standard_value FROM table_name WHERE score = $score";

[Phibermaster]

Thanks, but the problem is more how to write the correct ranges in the individual cells in the table. Sorry for not making that clear in the original post. What i want is a column for the standardized value, numbered from 1 to 19. Then i need the score column to contain ranges of scores in each cell. However, i cant put in for example 121-136 in one cell, since it seems that mysql dont allow that.

I will try to illustrate the two columns here.

standardvalue = Scorevalues
1 => 0-13
2 = > 14-26
3 = > 27-36
4 = > 37-45
5 = > 46-53
6 => 54-60
7 => 61-67
8 = > 68-73
9 => 74-78
10 => 79-83
11 => 84-88
12 => 89-93
13 => 94-98
14 => 99-103
15 => 104-108
16 => 109-114
17 => 115-120
18 => 121-126
19 => 127-133

Now, if i have an absolute score of say 66 points i want to find the correct standardvalue (in this case 7). How do i set up the table?

[NogDog]

I think it would be more efficient to set it up as:

Score Standard
0 1
1 1
2 1
...etc....
12 1
13 1
14 2
15 2
...etc....
25 2
26 2
27 3
28 3
...etc...
133 19

Then the query is very simple, and assuming that Score is your primary key, it will also be very efficient and quick.

If however you insist on having the primary key be the standard value with a range of scores, then I would do it as 3 columns:

standard minScore maxScore
1 0 13
2 14 26
3 27 36
4 37 45
5 46 53
...etc....
16 109 114
17 115 120
18 121 126
19 127 133

Then you query would be something like

SELECT standard FROM table_name WHERE $score BETWEEN minScore AND maxScore

[Phibermaster]

Thanks. I have reconsidered and will set up score as the primary key as you suggested. It will be a somewhat bigger table than i expected but it will work. :-)