CODEWITHSUNDEEP
phpmysql
Tim
Tim

Reputation: 113

Representing text with number MySQL

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 =)

Upvotes: 1

Views: 412

Answers (4)

AJ.
AJ.

Reputation: 28174

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.

Upvotes: 0

Alix Axel
Alix Axel

Reputation: 154553

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

Upvotes: 2

MindStalker
MindStalker

Reputation: 14864

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

Upvotes: 0

danben
danben

Reputation: 83250

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.

Upvotes: 0

Related Questions