Most efficient way of inserting gender and birthday into database?

[humanexperience]

Hi this will be my second post on these forums. I have been using this websites forum posts to help me with my web development for a long time. Yesterday I decided to sign-up and so far the advice i have received has been on point.

Just want to say it's nice to be here and I look forward to contributing to this online community.

Anyway the reason I have posted is because i have spent a long time perfecting my websites registration but have left out the "birthday" and "gender" parts of my registration.

I want to implement this into my current database but I properly. What would be the best way to do this?

I've been all over google and some people suggest 3 different fields in database for birthday e.g. day month and year.

Wouldn't it be better to have one field e.g. birthday and have the users birthday inserted into that field as dd-mm-yyyy?

As for gender selection some people are saying enum should be used and some are saying char? What is the safest most efficient way used by most programmers?

I have copied and pasted a section of my homepage showing my form. I would appreciate some help properly inserting birthday and gender into my database. Rather then do it incorrectly then coming back later to correct.

<form action="index_v2.php" method="post">
<h1>Sign Up</h1>
<h2>connecting people</h2>
<table width="100%" border="0">
 <tr>
 <td>First Name</td>
 <td><input type="text" name="first_name" size="20" maxlength="20" value="<?php echo (isset($_POST['first_name']) ? $_POST['first_name'] : ''); ?>" /></td>
 </tr>
 <tr>
 <td>Last Name</td>
 <td><input type="text" name="last_name" size="20" maxlength="40" value="<?php echo (isset($_POST['last_name']) ? $_POST['last_name'] : ''); ?>" /></td>
 </tr>
 <tr>
 <td>Email</td>
 <td><input type="text" name="email" size="30" maxlength="80" value="<?php echo (isset($_POST['email']) ? $_POST['email'] : ''); ?>" /></td>
 </tr>
 <tr>
 <td>Password</td>
 <td><input type="password" name="password" size="20" maxlength="20" /></td>
 </tr>
 <tr>
 <td>Birthday</td>
 <td>
<?php
include("bdayselect/DateDDLGenerator.class.php");
$ddl = new DateDDLGenerator;
$ddl_name_Change = new DateDDLGenerator;
$ddl_name_Change->setToCurrentDay();

print $ddl_name_Change->genDayDDL("day");
print $ddl_name_Change->genMonthDDL("month");
print $ddl_name_Change->genYearDDL("year");

?>

</td>
<tr>
<td>I am</td>
<td><select name="sex" class="select" id="sex" >
 <option value="0">-Select-</option>
  <option value="1">Male</option>
  <option value="2">Female</option>
  </select>
  </td></tr>
 <tr>
 <td></td>
 <td><su><input type="submit" name="submit" value="Sign-Up" /></su></td> 
 </tr>
</td>
</tr>
</table>

  
  <input type="hidden" name="submitted" value="TRUE" /></p>
   

</form>

<?php
// This is the registration page for 

require_once ('config.inc.php');
$page_title = 'Register';


if (isset($_POST['submitted'])) { // Handle the form.

require_once (MYSQL);

// Trim all the incoming data:
$trimmed = array_map('trim', $_POST);

// Assume invalid values:
$fn = $ln = $e = $p = FALSE;

// Check for a first name:
if (preg_match ('/^[A-Z \'.-]{2,20}$/i', $trimmed['first_name'])) {
$fn = mysqli_real_escape_string ($dbc, $trimmed['first_name']);
} else {
echo '<p class="error">Please enter your first name!</p>';
}

// Check for a last name:
if (preg_match ('/^[A-Z \'.-]{2,40}$/i', $trimmed['last_name'])) {
$ln = mysqli_real_escape_string ($dbc, $trimmed['last_name']);
} else {
echo '<p class="error">Please enter your last name!</p>';
}

// Check for an email address:
if (preg_match ('/^[\w.-]+@[\w.-]+\.[A-Za-z]{2,6}$/', $trimmed['email'])) {
$e = mysqli_real_escape_string ($dbc, $trimmed['email']);
} else {
echo '<p class="error">Please enter a valid email address!</p>';
}


// Check for a password and match against the confirmed password:
if (preg_match ('/^\w{4,20}$/', $trimmed['password'])) {
$p = mysqli_real_escape_string ($dbc, $trimmed['password']);
} else {
echo '<p class="error">Please enter a valid password!</p>';
}

if ($fn && $ln && $e && $p) { // If everything's OK...

// Make sure the email address is available:
//$q = "SELECT user_id FROM users WHERE email='$e'"; <--original of below
 $q = "SELECT `user_id` FROM `users` WHERE `email` ='".$e."' LIMIT 1"; 
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_num_rows($r) == 0) { // Available.

// Create the activation code:
$a = md5(uniqid(rand(), true));

// Add the user to the database:
$q = "INSERT INTO `users` (`email`, `pass`, `first_name`, `last_name`, `active`, `registration_date`) VALUES ('".$e."', SHA1('".$p."'), '".$fn."', '".$ln."', '".$a."', NOW() )";
/* $q = "INSERT INTO users (email, pass, first_name, last_name, active, registration_date) VALUES ('$e', SHA1('$p'), '$fn', '$ln', '$a', NOW() )"; */
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

// Send the email:
$body = "Thank you for registering at <YOUR SITE NAME>. To activate your account, please click on this link:\n\n";
$body .= BASE_URL . 'activate.php?x=' . urlencode($e) . "&y=$a";
mail($trimmed['email'], 'Registration Confirmation', $body, 'From: [e-mail]');

// Finish the page:
echo '<h3>Thank you for registering! A confirmation email has been sent to your address. Please click on the link in that email in order to activate your account.</h3>';
//include ('includes/footer.html'); // Include the HTML footer.
exit(); // Stop the page.

} else { // If it did not run OK.
echo '<p class="error">You could not be registered due to a system error. We apologize for any inconvenience.</p>';
}

} else { // The email address is not available.
echo '<p class="error">That email address has already been registered. If you have forgotten your password, use the link at right to have your password sent to you.</p>';
}

} else { // If one of the data tests failed.
/*echo '<p class="error">Please re-enter your passwords and try again.</p>'; */
} 

mysqli_close($dbc);

} 
// End of the main Submit conditional.

?> 

[LifeinAsia]

Wouldn't it be better to have one field e.g. birthday and have the users birthday inserted into that field as dd-mm-yyyy?

I would do one field and make it a DATETIME field. Making it a VARCHAR field is just asking for problems down the road. When you add to the form, you just have to convert the month, date, and year input by the user into a date value. I don't know PHP, so I don't know the specific conversion functions available.

Gender is going to be 2 possibilities (well, 3 if you want to add "trans-gender"). So you could use either a numeric or character field to represent it. (You could even use a bit field, although not all DBs allow NULL values for bit fields.) I would probably go for numeric.

[rocknbil]

To add to the above,

some people suggest 3 different fields in database for birthday e.g. day month and year.

This is very bad advice, see LIA's recommendation above. mySQL has powerful date math and sorting functions when you use the supported date formats, and this would break nearly all of them.

Select * from table order by birthday desc;

See below for another example.

....have the users birthday inserted into that field as dd-mm-yyyy?

The most common supported date types are YYYY-MM-DD, YYYY-MM-DD HH:MM:SS (datetime,) or unix timestamp, but for the date format you want, you'd just use the date_format() [dev.mysql.com] command when you select them:

select date_format(now(),"%m/%d/%Y %r");
--> 04/28/2010 10:15:50 AM

As for gender selection some people are saying enum should be used and some are saying char?

Definitely not char. Numeric fields will always result in faster lookups. Enum works, it's in the air whether it's faster or not, but look at this scenario.

$genders = Array ('Not Specified','Male','Female');

id|name|gender|other_fields
1|Bob|1|Blah
1|Sue|2|Bleah
1|Anonymous|0|Silent

So let's say someone complains "hey I'm transgendered, what about me?" :-) All you need to do is

$genders = Array ('Not Specified','Male','Female','Transgendered');

And position 3 becomes that representation.

Or change the values of the array:

$genders = Array ('Not Specified','Apollo','Earth Goddess');

The point is, the meanings can change without making any changes to your database.

Go with tinyint(1), maybe tinyint(2) if you ever think there may be a need.

[LifeinAsia]

Agree. If you do go with a CHAR field for gender, I would just made it length of 1.

[londrum]

i would still have 3 inputs in the form for the birthday - those drop down ones for the days, months and year - to make sure they choose the correct values, but just combine them into one when you put it in the database.

[tangor]

londrum beat me to it... drop/select for month day year reduces entry error. Never assume the user can do what you ask! A check box M[] F[] is all you need for gender (0,1)

Insert all data into database, and at the same time convert the date parts into a dateformat entry and insert in a separate field.

[humanexperience]

I have a drop down list for birthday, it's generated from the PHP file i included in the birthday section of the form. What I would like to figure out is how to correctly insert that data into my database.

I have created a birthday field in my database with type "DATE" might change it to "DATETIME".

1. What should I be typing in the birthday section of my form to correctly pass this data into the birthday section of my database? I'm quite stuck at the moment..

2. For gender I have created a "sex" field with length/value '0','1','2'... Wouldn't it be better to use 'm','f' rather than have numbers for gender stored in my database?

3. I would appreciate if somebody could type out a way for me to insert this data into my database. From the posted code above you can see how my form looks and the form below you can see how my birthday dropdown code looks.

<?php
class DateDDLGenerator{

 var $intDay;
var $intMonth;
var $intYear;
var $bolSetToCurrentDay;

function DateDDLGenerator(){
$this->bolSetToday = false;
$this->intDay = date("d");
$this->intMonth = date("m");
 $this->intYear = date("Y");
}

function setToCurrentDay(){
$this->bolSetToCurrentDay = true;
}

#Generate Year range
function genYearDDL($selName = 'Year', $yearCount = 110, $year = ''){
/*
Check if the year passed in is the same as current year.
If the year got is not given or same as current year, the list 
will select the current year by default. Otherwise, $yearSelect
will be set to what user entered.
*/
$yearSelect = $year == '' ? date("Y") : $year;

/*
$yearCount: it is the length of your drop down list, i.e. how many 
years do you want to show. It is 50 by default, which shows 50 years
from now.
*/

$str = "<select name='$selName'>\n";
for($i = $yearSelect; $i >= ($yearSelect - $yearCount); $i--){
if($this->bolSetToCurrentDay == true){
$selected = $this->intYear == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>$i</option>\n";
}
$str .= "</select>\n";
print $str;
}

#Generate month range from 1 to 12
function genMonthDDL($selName = 'Month', $date_format = 'short'){
$shortM = array(1 => "Jan", "Feb", "Mar",
 "Apr", "May", "Jun",
 "Jul", "Aug", "Sep",
 "Oct", "Nov", "Dec");

$longM = array(1 => "January", "February", "March",
 "April" , "May" , "June" ,
 "July" , "Aug" , "September",
 "October", "November", "December");

$str = "<select name='$selName'>\n";
if($date_format == 'short'){
for($i = 1; $i <= 12; $i++){
if($this->bolSetToCurrentDay == true){
$selected = $this->intMonth == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>".$shortM[$i]."</option>\n";
}
}elseif($date_format == 'long'){
for($i = 1; $i <= 12; $i++){
if($this->bolSetToCurrentDay == true){
$selected = $this->intMonth == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>".$longM[$i]."</option>\n";
}
}
$str .= "</select>\n";

print $str;
}

#Generate day range from 1 to max days of relevant month
function genDayDDL($selName = 'Day'){
$str = "<select name='$selName'>\n";

//Thanks to Peter K on this improvement and now this method support leap year
if ($this->intMonth == 2) {// February ?
$leap_day = 0;

if ($this->intYear >= 4 && $this->intYear % 4 == 0) {// Leap year ?
if ($this->intYear >= 1800 && $this->intYear % 100 == 0) {// No accurate leap centuries before that
if (($this->intYear / 100) % 4 == 0)
$leap_day = 1;
} else
$leap_day = 1;
}

$max_days = 28 + $leap_day;
} else if ($this->intMonth == 4 || $this->intMonth == 6 ||
 $this->intMonth == 9 || $this->intMonth == 11)
$max_days = 30;
else
$max_days = 31;

for($i = 1; $i <= $max_days; $i++){
if($this->bolSetToCurrentDay == true){
$selected = $this->intDay == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>$i</option>\n";
}
$str .= "</select>\n";
print $str;
}
}
?>

[humanexperience]

Created 2 new fields in my users table.

1. birthday with type"date"
2. sex with type enum('0','1','2')

Adding below to my current code and modified the insert string.

I check my database and everything inserts fine except for sex and birthday. I understand why birthday hasn't gone in to the database properly.. i have got around to making that work yet but when it comes to gender I'm not sure what I'm doing wrong here.

// Check gender is selected:
if (preg_match ('/^\w{4,20}$/', $trimmed['sex'])) {
$s = mysqli_real_escape_string ($dbc, $trimmed['sex']);
} else {
echo '<p class="error">choose sex!</p>';
}



// Add the user to the database:
$q = "INSERT INTO `users` (`email`, `pass`, `first_name`, `last_name`, `sex`, `active`, `registration_date`) VALUES ('".$e."', SHA1('".$p."'), '".$fn."', '".$ln."', '".$a."', '".$s."', NOW() )";
/* $q = "INSERT INTO users (email, pass, first_name, last_name, active, registration_date) VALUES ('$e', SHA1('$p'), '$fn', '$ln', '$a', NOW() )"; */
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

[humanexperience]

This worked for me slightly.
Gender inserts corrects 1 for male 2 for female.
Registration date no longer shows correct instead shows as 0000-00-00
birthday shows as todays date only.

I guess it's more trouble shooting and experimenting until i fix this. I really do hope i'm doing this correctly.

$q = "INSERT INTO `users` (`email`, `pass`, `first_name`, `last_name`, `active`, `registration_date`, `sex`, `birthday`) VALUES ('".$e."', SHA1('".$p."'), '".$fn."', '".$ln."', '".$a."', '".$ddl."', '".$_POST['sex']."', NOW() )";

[humanexperience]

ok changed above string to:

$q = "INSERT INTO `users` (`email`, `pass`, `first_name`, `last_name`, `active`, `registration_date`, `sex`, `birthday`) VALUES ('".$e."', SHA1('".$p."'), '".$fn."', '".$ln."', '".$a."', NOW(), '".$_POST['sex']."','".$_POST['$ddl']."' )";

and everything inserts correctly except for birthday which now inserts as 0000-00-00

Getting there. I will try again

[humanexperience]

changed $ddl to $ddl_name_Change birthday still inserts 0000-00-00

Hmm....

Any ideas?

[rocknbil]

and everything inserts correctly except for birthday which now inserts as 0000-00-00

Echo your statement. see if it's YYYY-MM-DD.

$_POST['$ddl']

Two things. FIRST variables won't interpolate with single quotes (and you knew that. :-) )

$_POST[$ddl]

Or even unnecessarily, $_POST["$ddl"]

Second, I usually have three drop down lists, and do this (but CLEANSE THEM FIRST)

$birthday = $_POST['bday_year'] . '-' . $_POST['bday_month'] . '-' . $_POST['bday_day'];

$query = "insert into table (birthday) values ('" . $birthday . "')";

[humanexperience]

Ok finally it works..
It may work but is it this the correct method?

Please critique thanks..

If all is good all I need to do now is get the months to display the right amount of days e.g. feb 28 or 29 days etc.

$str = $_POST['year'] . "-" . $_POST['month'] . "-" . $_POST['day'];

$q = "INSERT INTO `users` (`email`, `pass`, `first_name`, `last_name`, `active`, `registration_date`, `birthday`, `sex`) VALUES ('".$e."', SHA1('".$p."'), '".$fn."', '".$ln."', '".$a."', NOW(), '".$str."', '".$_POST['sex']."')";

$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

Date Code

<?php
class DateDDLGenerator{

 var $intDay;
var $intMonth;
var $intYear;
var $bolSetToCurrentDay;

function DateDDLGenerator(){
$this->bolSetToday = false;
$this->intDay = date("d");
$this->intMonth = date("m");
  $this->intYear = date("Y");
}

function setToCurrentDay(){
$this->bolSetToCurrentDay = true;
}

#Generate Year range
function genYearDDL($selName = 'Year', $yearCount = 110, $year = ''){
/*
Check if the year passed in is the same as current year.
If the year got is not given or same as current year, the list 
will select the current year by default. Otherwise, $yearSelect
will be set to what user entered.
*/
$yearSelect = $year == '' ? date("Y") : $year;

/*
$yearCount: it is the length of your drop down list, i.e. how many 
years do you want to show. It is 50 by default, which shows 50 years
from now.
*/

$str = "<select name='$selName'>\n";
for($i = $yearSelect; $i >= ($yearSelect - $yearCount); $i--){
if($this->bolSetToCurrentDay == true){
$selected = $this->intYear == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>$i</option>\n";
}
$str .= "</select>\n";
print $str;
}

#Generate month range from 1 to 12
function genMonthDDL($selName = 'Month', $date_format = 'short'){
$shortM = array(1 => "Jan", "Feb", "Mar",
 "Apr", "May", "Jun",
 "Jul", "Aug", "Sep",
 "Oct", "Nov", "Dec");

$longM = array(1 => "January", "February", "March",
 "April" , "May" , "June" ,
 "July" , "Aug" , "September",
 "October", "November", "December");

$str = "<select name='$selName'>\n";
if($date_format == 'short'){
for($i = 1; $i <= 12; $i++){
if($this->bolSetToCurrentDay == true){
$selected = $this->intMonth == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>".$shortM[$i]."</option>\n";
}
}elseif($date_format == 'long'){
for($i = 1; $i <= 12; $i++){
if($this->bolSetToCurrentDay == true){
$selected = $this->intMonth == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>".$longM[$i]."</option>\n";
}
}
$str .= "</select>\n";

print $str;
}

#Generate day range from 1 to max days of relevant month
function genDayDDL($selName = 'Day'){
$str = "<select name='$selName'>\n";
//on this improvement and now this method support leap year
if ($this->intMonth == 2) {// February ?
$leap_day = 0;

if ($this->intYear >= 4 && $this->intYear % 4 == 0) {// Leap year ?
if ($this->intYear >= 1800 && $this->intYear % 100 == 0) {// No accurate leap centuries before that
if (($this->intYear / 100) % 4 == 0)
$leap_day = 1;
} else
$leap_day = 1;
}

$max_days = 28 + $leap_day;
} else if ($this->intMonth == 4 || $this->intMonth == 6 ||
 $this->intMonth == 9 || $this->intMonth == 11)
$max_days = 30;
else
$max_days = 31;

for($i = 1; $i <= $max_days; $i++){
if($this->bolSetToCurrentDay == true){
$selected = $this->intDay == $i ? 'selected="selected"' : '';
}
$str .= "\t<option value='$i' $selected>$i</option>\n";
}
$str .= "</select>\n";
print $str;
}
}
?>