Mysql table data problem?

Question

I'm new to mysql and was wondering how can I add the users birthdate in the following HTML format to the MYSQL table data listed below?

How would the structure look like for example email VARCHAR(80) NOT NULL,?

Here is the HTML code below.

    <li><label>Date of Birth: </label>
    <label for="month">Month: </label>
        <select name="month" id="month">
            <option value="January">January</option>
            <option value="February">February</option>
            <option value="March">March</option>
            <option value="April">April</option>
            <option value="May">May</option>
            <option value="June">June</option>
            <option value="July">July</option>
            <option value="August">August</option>
            <option value="September">September</option>
            <option value="October">October</option>
            <option value="November">November</option>
            <option value="December">December</option>  
        </select>
    <label for="day">Day: </label>
        <select id="day" name="day">
            <option value="0" selected="selected">Day</option>
            <option value="1">1</option>
            <option value="2">2</option>
            <option value="3">3</option>
            <option value="4">4</option>
            <option value="5">5</option>
            <option value="6">6</option>
            <option value="7">7</option>
            <option value="8">8</option>
            <option value="9">9</option>
            <option value="10">10</option>
            <option value="11">11</option>
            <option value="12">12</option>
            <option value="13">13</option>
            <option value="14">14</option>
            <option value="15">15</option>
            <option value="16">16</option>
            <option value="17">17</option>
            <option value="18">18</option>
            <option value="19">19</option>
            <option value="20">20</option>
            <option value="21">21</option>
            <option value="22">22</option>
            <option value="23">23</option>
            <option value="24">24</option>
            <option value="25">25</option>
            <option value="26">26</option>
            <option value="27">27</option>
            <option value="28">28</option>
            <option value="29">29</option>
            <option value="30">30</option>
            <option value="31">31</option>
        </select>                   
    <label for="year">Year: </label><input type="text" name="year" id="year" /></li>

Here is the MySQL table data.

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(80) NOT NULL,
pass CHAR(40) NOT NULL,
user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
active CHAR(32),
registration_date DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY (email),
INDEX login (email, pass)
);

Answer 1

Use a DATE type in a table

CREATE TABLE users (
    user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    email VARCHAR(80) NOT NULL,
    pass CHAR(40) NOT NULL,
    user_level TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
    active CHAR(32),
    registration_date DATETIME NOT NULL,

    birhtday DATE NOT NULL, 

    PRIMARY KEY (user_id),
    UNIQUE KEY (email),
    INDEX login (email, pass)
    );

You better modify your form, so that value of month options would be numbers, not full month names. Like this:

         <select name="month" id="month">
            <option value="1">January</option>
            <option value="2">February</option>
            <option value="3">March</option>
            <option value="4">April</option>
            <option value="5">May</option>
            <option value="6">June</option>
            <option value="7">July</option>
            <option value="8">August</option>
            <option value="9">September</option>
            <option value="10">October</option>
            <option value="11">November</option>
            <option value="12">December</option>  
        </select>

If you are using PHP, the code that would make a proper value for a DATE type:

<?php
    //$birthday will be something like '7-12-1985'
    $birthday = $_POST['year'].'-'.$_POST['month'].'-'.$_POST['day'];
?>

Answer 2

You can use the DATE datatype for the birthday.

If you want to recreate the table you can add:

birth_day DATE NOT NULL,

If its not possible to have a b'day for every user you can drop the NOT NULL clause.

If you want to alter the existing table you can do:

ALTER TABLE users
ADD COLUMN birth_day DATE

Answer 3

If you want to store a date, you can use the DATE datatype :

The DATE type is used when you need only a date value, without a time part.
MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.
The supported range is '1000-01-01' to '9999-12-31'.

Then, you'll insert/update or get dates using a 'YYYY-MM-DD' format.

This means your create table would look like this :

CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
active CHAR(32),
registration_date DATETIME NOT NULL,
birthdate DATE NOT NULL,
PRIMARY KEY (user_id),
...
);

And you'd use SQL queries such as (examples) :

select * 
from users 
where birthdate >= '1980-01-01' and birthdate <= '1999-12-31'

And/or :

insert into users (..., birthdate, ...) 
values (..., '1965-03-24', ...)

And/or :

update users
set birthdate = '1988-05-12'
where uid_id = 125

Answer 4

Use a DATETIME/DATE field to store this.