CODEWITHSUNDEEP
phpmysql
JasonDavis
JasonDavis

Reputation: 48933

Best way to store a short number in mysql

I am trying to fix a project that someone else coded, it is a mess!

On an edit user profile page they have something like this below which will list all available radio select boxes for the options a user can select for there education level, this is just 1 item, there is many items like it so based on the answers here, I can apply it to many other features on this code.

They used an array for many profile fields, it would store possible answers, A user can only choose 1 answer though in this case. So for education, there is 7 possible answers and a number corresponding with it's number in the array is stored in a mysql DB, they store this 1 number as a varchar 255 length which can't be good.

Should I store it as a varchar with 1 for length or use enum and list the 7 options, or something else?

Education

<?PHP
// Array of possible education profile selections
$arr_education[1]="No Answer";
$arr_education[2]="High school";
$arr_education[3]="Some college";
$arr_education[4]="In college";
$arr_education[5]="College graduate";
$arr_education[6]="Grad / professional school";
$arr_education[7]="Post grad";

// shows the array above as checkboxes on a form
foreach($arr_education as $ind=>$val) {  
    echo '<input type="radio" name="education" value="' .$ind. '" ' if($education==$ind){ echo "checked";}. '>' .$val. '<br>';
}



//on a users profile page there would be something like this
$education = 7; // seven would be Post Grad from our array above
echo $arr_education[$education]

//Now here is the mysql table, they chose to use a varchar(255) which seems retarded, using the above code, the max result would be 1 charachter long
education   varchar(255)

?>

Upvotes: 1

Views: 584

Answers (5)

Josh Davis
Josh Davis

Reputation: 28730

If this was a new project, I'd recommend using ENUM. It's space efficient (it's actually stored on 1 byte) and simple to use. However, if you have to support legacy code then I'd recommend sticking to raw numbers and change that column into a TINYINT.

Additionally, I'd recommend replacing the numbers in your PHP source code with constants, so that you don't have to guess what the code does. For example:

define('EDU_NO_ANSWER', 0);
define('EDU_HIGH_SCHOOL', 1);
define('EDU_SOME_COLLEGE', 2);
// etc...

if ($education >= EDU_SOME_COLLEGE)
{
    // student went to college, but you didn't need that
    // comment to know that
}

Upvotes: 3

too much php
too much php

Reputation: 90998

If the possible items are not going to change often, then you should use an ENUM, it is the most space efficient, and also makes all of your queries much more readable than having magic numbers defined elsewhere.

Upvotes: 1

Scott Lundberg
Scott Lundberg

Reputation: 445

If it was me, I would create a separate table with the profiles in it, then populate the Select tag with the results of a SQL query. Make the index an integer (after all you never know what this will grow into..) It would also allow the enduser to change the descriptions without having to touch code (assuming they have SQL access)

Upvotes: 0

bisko
bisko

Reputation: 4078

Why not use TINY INT ? It suits better your needs if the answers would be < 255 :) My tests show that it's faster than varchrars in indexing and selecting.

Upvotes: 3

Walt W
Walt W

Reputation: 3349

For that, a varchar(1) would take up exactly as much space as a varchar(255) except if the enum ever reached double digits, a varchar(1) would break. A varchar field that is <= 255 max chars stores 1 byte for the length of the string, and then the string. So for a one-digit number, varchar(255) takes 2 bytes.

Why they didn't use one of the integer classes, however, is beyond me.

Upvotes: 1

Related Questions