<?php echo $Gender?>

Replace varchar with int

[Gruessle]

I use following trick in my Forms and would like to speed up my db

<select name="Gender" size="1" title="Gender">
<option value="<?php echo $Gender?>"><?php echo $Gender?></option>
<option value="Female">Female</option>
<option value="Male">Male</option>
</select>

I like to use numbers, this way the db will be smaller and faster I believe, but how do I display the results?
See below:

<select name="Gender" size="1" title="Gender">
<option value="<?php echo $Gender?>"><?php echo $Gender?></option>
<option value="1">Female</option>
<option value="2">Male</option>
</select>

I am sure there must be another trick for that isn't there?

[Timotheos]

Welcome to WebmasterWorld Gruessle,

That's a good idea. I'd treat it as a boolean value either 0 (female) or 1 (male). That way when you want to display it all you need is this...

if ($gender) 
  echo "Male"; 
else 
  echo "Female"; 

Using the tenary operator [us3.php.net] you can simplify it down to this...

echo ($gender) ? "Male" : "Female";

Tim

[hakre]

I like to use numbers, this way the db will be smaller and faster I believe, but how do I display the results?

there are many ways how you can write the mapping of number to string in your code. since the number can only have 2 values and you're outputting from your database maybe multiple times, i would suggest you using an array. this is maybe faster then an if-block:

$GenderCaps = array(1 => "male", 2 => "female");
echo($GenderCaps[$GenderVal]);

you only need to define the array once.

[Nutter]

What about using an enum data type? I think these are stored as a number in the database, so you'll get the benefit you're after, but when you open it it actually outputs 'Male' or 'Female'.

I typically use a char(1) field with either M or F. That way I don't have to remember what 1 or 0 is.

- Ryan

[Gruessle]

I have a lot more objects like all US states, countries etc etc. and most of them have more then 2 or 3 options.

I do understand Timotheos and hakre solution to understand Nutter I will have to find out more about enum data types.

It seams to me that you all think that this is the only object I have. Is there another solution if I have more options?

[Nutter]

Enum will work for a lot of different options, state etc... I don't know exactly how many options it has, but I've never had an issue of going over the limit (and I've used it for states). That data type will allow you to enter a list of values that are valid for that column. So, the value can be 'TX', 'AZ', 'AK', 'MO', etc... for states. When it's actually stored, it goes in as a number, but the SQL server takes care of the conversion. Similar idea to you having an array 1=> 'TX', 2=> 'AZ', etc... and then you storing the key value, but easier.

This may not be what you're looking for, but you might also look at the set data type. Similar to enum in that you enter a list of valid values, but the set type allows you to pick more than one.

hakre's method will work for states and stuff with more than two options.

- Ryan

[hakre]

my method was intended for output only. if you store into the database fixed strings (2 or more options as talked here), take the enum field type [dev.mysql.com]. this is best practise.