Representing text with number MySQL

Question

Hello,

I am building a survey, in which participants use a html form to enter details.

For example:

What gender are you?    Male | Female 

The Male/Female option can be selected using a radio button.

However, I would like to store the answer in my database as an INT value, which is 0 for male and 1 for female. How do I write a query which represents that?

I have a rough idea which looks like this, but doesn't seem to work:

$__g = Array('male' => '0', 'female' => '1');

And also, how do I insert the value into the database? I have this, but am unsure if it works (since I can't get the representing part correct)

$sql = "INSERT INTO `onlinesurvey` 
(gender) VALUES ('".$__g[$_POST['gender']]."');

Many thanks for your help =)

Answer 1

You can just use the value attribute.

<select id="gender" name="gender">
  <option value="0">male</option>
  <option value="1">female</option>
</select>

Although, MySQL has better data types than int for this. I would recommend a boolean type.

Answer 2

How do I write a query which represents that?

Why convert the string to an int? I think what you want to do here is use an enum column type to represent the range of possible values...

And also, how do I insert the value into the database?

If you use an enum, you would just insert the string literal value as the column value.

---

EDITED:

Here is some information on how the enum type works: http://dev.mysql.com/doc/refman/5.0/en/enum.html

So your query would look like this:

$sql = "INSERT INTO `onlinesurvey` (gender) VALUES ($_POST['gender']);

---

EDITED:

I haven't seen your HTML, but make sure the select list looks something like this:

<select name="gender">
<option value="male">Male</option>
<option value="female">Female</option>
</select>

The critical piece of this is getting the option value attributes to be equal to the enum values.

Answer 3

What danben said.

You should also make sure to store all these associates in the DB, mainly for record keeping, lookup info about the enum field.

One more thing, INT is overkill use TINYINT

Answer 4

MySQL ENUM data type is the best choice for this kind of stuff.

CREATE TABLE survey (
    gender ENUM('Male', 'Female', 'Hemaphrodite')
);

INSERT INTO survey VALUES ('Female'); // will get index 1
INSERT INTO survey VALUES ('Hemaphrodite'); // will get index 2
INSERT INTO survey VALUES ('Male'); // will get index 0