Date insert into database

[fife]

Hi

I have started my error checking for my form.  I am understand that the date must be inserted in the format yyyy/mm/dd but on my form i need it to be inserted in the format dd/mm/yyyy.  I have wrote some code but have only been doing php about a week properly so I can't see what my error is.  Whenever I try to insert the date, the form its self does not error but if i check the database the date is simply 0000/00/00.  Can anyone help?

 

if(isset($_POST['submit'])) {
     $first_name = mysql_real_escape_string($_POST['first_name']);
  $last_name = mysql_real_escape_string($_POST['last_name']);
   $DOB = mysql_real_escape_string($_POST['DOB']);
  $sex = mysql_real_escape_string($_POST['sex']);
 $email = mysql_real_escape_string($_POST['email']);
  $username = mysql_real_escape_string($_POST['username']);
   $password = mysql_real_escape_string($_POST['password']); 
   $agree = mysql_real_escape_string($_POST['agreed']);
    $creation_date = mysql_real_escape_string($_POST['creation_date']);
	$user_type = mysql_real_escape_string($_POST['member_type']);
	 $access_level = mysql_real_escape_string($_POST['access_level']);
	  $validation = mysql_real_escape_string($_POST['validation_id']);
	  	  $club_user = mysql_real_escape_string($_POST['user_type']);
		  
$date_check = "/^([0-9]{2})/([0-9]{2})/([0-9]{4})$/";
if($first_name == "") {
		$message = "Please enter a first name";
		$success = 0;
	}
	else if($last_name == "") {
		$message = "Please enter a surname";
		$success = 0;
	}

	else if($DOB =="") {
		$message = "Please enter your date of birth.";
		$success = 0;
	}
	else if(!(preg_match("/^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$/", $DOB))) {
		$message = "Please enter your birthday in the format dd/mm/yyyy";
		$success = 0;
		}
	else if($email =="") {
		$message = "Please enter a correct email.";
		$success = 0;
	}
	else if($username =="") {
		$message = "Please enter a username.";
		$success = 0;
	}
	else if($password =="") {
		$message = "Please enter a password greater than 6 characters long.";
		$success = 0;
	}


else {


$DOB = $explode[2]."-".$explode[1]."-".$explode[0];
		  	
		  $password_md5 = md5($password);
$insert_member= "INSERT INTO Members (`first_name`,`last_name`,`DOB`,`sex`,`email`,`username`,`password`,`agree`,`creation_date`,`usertype`,`access_level`,`validationID`) 
VALUES 
('".$first_name."','".$last_name."','".$DOB."','".$sex."','".$email."','".$username."','".$password_md5."','".$agree."','".$creation_date."','".$user_type."','".$access_level."', '".$validation."')";

$insert_member_now= mysql_query($insert_member) or die(mysql_error());

$url = "thankyou.php?name=".$_POST['username'];
	header('Location: '.$url);

[Pikachu2000]

No, you don't need it inserted in dd-mm-yyyy format at all. You need to insert it in the correct format for the database, yyyy-mm-dd, then when you retrieve it, change the format for display using the DATE_FORMAT() MySQL function in the query.

[fife]

sorry no what i mean is people insert thier date as dd/mm/yyyy and i need it changed to yyyy/mm/dd so the database will read it correctly

[Psycho]

OK, the problem is this line here:

$DOB = $explode[2]."-".$explode[1]."-".$explode[0];

You never "exploded" the value.

 

However, there are other "problems" as well:

 

First off you should always trim() the user input unless there is a specific reason not to. Second, for each validation you have a "$success = 0;" if it fails. That is unnecessar as you could check if $message has a value or not. But, that raises another issue in that on the first error validation does not continue. So, if there are three errors the user only gets a message about the first error. Then upon resubmission would get a message about the second error. It's more user friendly to do all validations.

 

Also, you should do a trim of the values before you do the validations. Then, only after the validations have passed would you use mysql_real_escape_string(). Because mysql_real_escape_string() could modify the values in such a way that validation might pass or fail when it shouldn't. And, you would not use mysql_real_escape_string() on a password if you are going to hash it. The act of hashing it will make it db safe.

 

Is creation_date supposed to be the current date? If so, you could just use NOW() in the db query instead of having an extra field to work with.

 

If validation fails you should display the form with a message for the errors and the form shoudl be repopulated with the user's values. Can't tell if you are doing that or not.

 

I could go on, but I've invested enough time on this post. Here is some modified code

 

if(isset($_POST['submit']))
{
    //Process data for validation
    $first_name    = trim($_POST['first_name']);
    $last_name     = trim($_POST['last_name']);
    $DOB           = trim($_POST['DOB']);
    $sex           = trim($_POST['sex']);
    $email         = trim($_POST['email']);
    $username      = trim($_POST['username']);
    $password      = trim($_POST['password']); 
    $agree         = trim($_POST['agreed']);
    $creation_date = trim($_POST['creation_date']);
    $user_type     = trim($_POST['member_type']);
    $access_level  = trim($_POST['access_level']);
    $validation    = trim($_POST['validation_id']);
    $club_user     = trim($_POST['user_type']);

    //Perform validations
    $errors = array();
    if(empty($first_name))
    {
        $errors[] = "Please enter a first name";
    }

    if(empty($last_name))
    {
        $errors[] = "Please enter a surname";
    }

    if(empty($DOB))
    {
        $errors[] = "Please enter your date of birth.";
    }
    else if(!(preg_match("/^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$/", $DOB)))
    {
        $errors[] = "Please enter your birthday in the format dd/mm/yyyy";
    }
   
    if(empty($email))
    {
        $errors[] = "Please enter a correct email.";
    }
   
    if(empty($username))
    {
        $errors[] = "Please enter a username.";
    }
   
    if(strlen($password)<6)
    {
        $errors[] = "Please enter a password greater than 6 characters long.";
    }
   
   //Check if there were errors
    if(count($erros)===0)
    {
        //Prepare data for db insertion
        $first_name    = mysql_real_escape_string($first_name);
        $last_name     = mysql_real_escape_string($last_name);
        $DOB           = mysql_real_escape_string($DOB);
        $sex           = mysql_real_escape_string($sex);
        $email         = mysql_real_escape_string($email);
        $username      = mysql_real_escape_string($username);
        $password      = md5($password); 
        $agree         = mysql_real_escape_string($agree);
        $creation_date = mysql_real_escape_string($creation_date);
        $user_type     = mysql_real_escape_string($user_type);
        $access_level  = mysql_real_escape_string($access_level);
        $validation    = mysql_real_escape_string($validation);
        $club_user     = mysql_real_escape_string($club_user);
    
        $date_parts = explode('-', $DOB);
        $DOB = "{$date_parts[2]}-{$date_parts[1]}-{$date_parts[0]}";
    
        $query = "INSERT INTO Members
                      (`first_name`, `last_name`, `DOB`, `sex`, `email`, `username`, `password`,
                       `agree`, `creation_date`, `usertype`, `access_level`, `validationID`) 
                  VALUES
                    ('{$first_name}', '{$last_name}', '{$DOB}', '{$sex}', '{$email}', '{$username}', '{$password_md5}',
                     '{$agree}', '{$creation_date}', '{$user_type}', '{$access_level}', '{$validation}')";
        $result= mysql_query($query) or die(mysql_error());
        $url = "thankyou.php?name={$username}";
        header("Location: {$url}");
        exit();
    }
    else
    {
        //Validation failed. Create COMPLETE error message using the array $errors
    }
}
    
//Validation failed or the form was not submitted, display form

[TOA]

sorry no what i mean is people insert thier date as dd/mm/yyyy and i need it changed to yyyy/mm/dd so the database will read it correctly

 

Why can't you just have them input it in the format you need-- --what's going on that it HAS to be input in that format?  Then you don't have to mess with all the extraneous stuff...just a thought

[Psycho]

sorry no what i mean is people insert thier date as dd/mm/yyyy and i need it changed to yyyy/mm/dd so the database will read it correctly

 

Why can't you just have them input it in the format you need-- --what's going on that it HAS to be input in that format?  Then you don't have to mess with all the extraneous stuff...just a thought

 

Because people talk in a different language than users. It is the job of the programmer to bridge the gap in making a systematic, tightly structured schema into a user friendly interface. For example, if I store a users permissions in a bitwise value I wouldn't give the user a single field to enter '001101' for the permissions. I would give them a selection of six checkboxes and create the binary value on the back end. In most regions people use dates in the format mm-dd-yyyy or dd-mm-yyyy, whereas computers use yyyy-mm-dd

[TOA]

Because people talk in a different language than users. It is the job of the programmer to bridge the gap in making a systematic, tightly structured schema into a user friendly interface. For example, if I store a users permissions in a bitwise value I wouldn't give the user a single field to enter '001101' for the permissions. I would give them a selection of six checkboxes and create the binary value on the back end. In most regions people use dates in the format mm-dd-yyyy or dd-mm-yyyy, whereas computers use yyyy-mm-dd

 

I understand that, and do it in mine too, but my point is still the same...for example: when you sign up to facebook, they have you enter your birthday as YYYY/MM/DD and thats a major sign-in site now. If you inform the user, there's no reason they should screw it up.

 

Just defending my point 

[Psycho]

I understand that, and do it in mine too, but my point is still the same...for example: when you sign up to facebook, they have you enter your birthday as YYYY/MM/DD and thats a major sign-in site now. If you inform the user, there's no reason they should screw it up.

 

Just defending my point 

 

I hear you and agree that for a site such as facebook, it probably makes sense. Because the typical formats are mm-dd-yyyy and dd-mm-yyyy, if a site such as facebook tried to use one of those formats there would be a lot of people entering the wrong date format by accident. The format yyyy-mm-dd, will prevent users from entering the date in their "natural" format by accident.

 

However, I would guess the majority of websites are not used internationally and it would make sense to have the user enter the date according to the "normal" format. Plus, the user of a datepicker will help reduce errors as well.