[SOLVED] bit of help needed

[leegreaves]

Im about to sort out my registration page for my website by customising a "registration" script I came across. It has about all that I need, apart from the changes that i need to make to it to work for my environment. There is one little problem that is niggling me though cos i know ill come across it if i do it wrong so want a pointer on how to get around it. My registration page has all the usual entries that you normally have to make on such a page; such as username, password (md5 style), email, etc etc. The problem I think ill be facing though, what do I do about entries such as date of birth and things. The Date of Birth (ie DoB) has 3 selections to it, obviously, dd mm yyyy in that form in 3 drop down boxes. How do i interpret that in my INSERT statement. MySQL is showing the date format as yyyy-mm-dd in my database and thats how ive set it at this moment but id like to change that so it shows dd-mm-yyyy in one field in the database. BUT, how would i insert 3 seperate fields (dd, mm, yyyy) in my registration page into one field on my database. Im hazarding a guess that this may not be possible but if it is i would like to know how.

[leegreaves]

ive just come up with a thought: would i be able to do the above the following way:

 

if my Date of Birth contains 3 variables ie day, month, year; entered in seperate drop down boxes - would this work:

 

$dob = mysql_real_escape_string (stripslashes ($_POST ['day' + 'month' + 'year']));

 

even though my database field for date of birth is one field on its own would i be able to combine the 3 seperate fields from date of birth on my registration page and then insert them into the database as one complete field?

[.josh]

String concatenation.

$dd = "12";
$mm = "10";
$yy = "2009";

$dateString = $yy . "-" . $mm . "-" . $dd;

 

edit: from above post, yes, you got it right, though with php it's . not +

[leegreaves]

String concatenation.

$dd = "12";
$mm = "10";
$yy = "2009";

$dateString = $yy . "-" . $mm . "-" . $dd;

 

edit: from above post, yes, you got it right, though with php it's . not +

 

How would i change the date format in my database as it is showing as yyyy-mm-dd but i would like to change it to dd-mm-yyyy but it doesnt show that option anywhere when i edit the field itself. Should i place a post in the mysql section to find that out?

[.josh]

You can't.  That's the standard.  If you really want to store it in your own format, you can use a different field type, like varchar, and store it as a string.  But then you are going to run into issues when you later on want to go back and do things like only select things between date ranges, etc...

 

My advice to you would be to keep it as-is in the db and you can reformat it when presenting it to the user.  The db has built-in functions to reformat it in your query when you are selecting the data.  Alternatively there are php functions such as date and strtotime to help you reformat it.  Alternatively, since it's simple yy-mm-dd you can just explode at the hyphen and concatenate it back together in your desired order.

[leegreaves]

ahhh right ok...so really all i can do is in my php code do it as in yyyy-mm-dd to match how it is in my database...well that seems to be the easy bit done...now the hard part writing the code and getting it to work. Many thanks for ur help.

[.josh]

well you can use dd-mm-yyyy in your php.  You can display it to the user like that just fine.  You just have to go through the extra step of converting it back and forth.

[leegreaves]

im gonna put the problem with date format on the back burner for the time being as it isnt of paramount importance. BUT, ive come to a bit of a mental block, you mention concetenation (or however its spelt i cant remember) How would i actually implement it as an instruction in mysql_real_escape_string?

This is happening cos you added bits about $datestring so im unsure what way ur looking at it...im guessing ur saying convert the 3 variables (day month and year) into one string then use the mysql_real_escape_string on the $datestring...but im not sure how to do that.

ill try and hazard a guess here:

 

$dd = "day";

$mm = "month";

$yyyy = "year";

$datestring = $yyyy . "-" . $mm . "-" .$dd;

$dob = mysqpl_real_escape_string(stripslashes($_POST['datestring']));

 

- remembering that in the $dd, $mm, $yyyy the day month and year are the "names" ive associated in my form

- crossing my fingers that, again, im getting close to being half correct!!!!

 

[.josh]

okay, assuming that your form elements are called 'day' 'month' and 'year' and you're submitting via POST method, it should look like this:

 

$dd = $_POST['day'];
$mm = $_POST['month'];
$yyyy = $_POST['year'];
$datestring = $yyyy . "-" . $mm . "-" .$dd;
$dob = mysql_real_escape_string(stripslashes($datestring));

[leegreaves]

where u say "form element" i assume u mean name as in the html bit: <select name="day" or "month" or "year" id="..." etc etc >

[.josh]

yes.

[leegreaves]

ok well ive just written it up...i just hope ive done a "fair" job. ive omitted the definitions for DB_HOST ...USER etc for obvious reasons but the correct info i already have in the finished product. im gonna upload a test page to my server and check it out...fingers crossed that it works!

 

<?php

define('DB_HOST', 'etc');

define('DB_USER', 'etc');

define('DB_PASSWORD', 'etc');

define('DB_DATABASE', 'etc');

 

//Array to store validation errors

$errmsg_arr = array();

 

//Validation error flag

$errflag = false;

 

//Connect to mysql server

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link) {

die('Failed to connect to server: ' . mysql_error());

}

 

//Select database

$db = mysql_select_db(DB_DATABASE);

if(!$db) {

die("Unable to select database");

}

 

//define variables for fields

$username = mysql_real_escape_string(stripslashes($_POST['username']));

$password = mysql_real_escape_string(stripslashes($_POST['password']));

$email = mysql_real_escape_string(stripslashes($_POST['email']));

$sex = mysql_real_escape_string(stripslashes($_POST['sex']));

$dd = $_POST['day'];

$mm = $_POST['month'];

$yyyy = $_POST['year'];

$datestring = $yyyy . "-" . $mm . "-" .$dd;

$dob = mysql_real_escape_string(stripslashes($datestring));

 

//check if form has been submitted

if (isset($POST['submit']));

 

//check that no fields have been left blank

if (!$_POST['username'] | !$_POST['password'] | !$_POST['email'] | !$_POST['day'] | !$_POST['month'] | !$_POST['year'] )

{

die('You have not completed the form');

}

 

//check username is not already being used

if (!get_magic_quotes_gpc()) {

$_POST['username'] = addslashes($_POST['username']);

}

$usercheck = $_POST['username'];

$check = mysql_query("SELECT username FROM members WHERE username = '$usercheck'")

or die(mysql_error());

$check2 = mysql_num_rows($check);

 

//if that username exists return an error

if ($check2 != 0) {

die('Sorry, the username ' .$_POST['username']. ' is already in use');

}

 

//encrypt password to md5 checksum and add slashes if required

$_POST['password'] = md5($_POST['password']);

if (!get_magic_quotes_gpc()) {

$_POST['password'] = addslashes($_POST['password']);

$_POST['username'] = addslashes($_POST['username']);

}

 

//now add information to database

$insert = "INSERT INTO members (username, email, password, sex, dob) VALUES ($username, $email, $password, $sex, $dob)";

$add_member = mysql_query($insert);

?>