...

View Full Version : Putting $date, $month and $year variable into one $dob field in a MySQL Database..



StevenPrice
12-21-2011, 08:54 PM
I have just had some wonderful help off a very helpful member regarding php and dob etc and got my calendar working and all this but the problem I have is that I can't work out how to make the $day $month and $year variables post into a date field "dob" in my MySQL Database... I am not sure if this is even possible


$dob=$_POST['dob'];

$day= substr($dob, 0, 2);
$month = substr($dob, 3, 2);
$year = substr($dob, 6, 4);

I also have this:


/ Add user info into the database table for the main site table
$sql = mysql_query("INSERT INTO myMembers (firstname, lastname, country, state, city, dob, email, password, sign_up_date, website, youtube)
VALUES('$firstname','$lastname','$country','$state','$city', '$dob', '$email1','$db_password', now(),'$website','$youtube')")
or die (mysql_error());

$id = mysql_insert_id();

To summarise I want the date entered on my site to be put into my field "dob" in my MySQL database when submit is clicked :)

PS Dont fill it in and click submit because the activation part isn't complete meaning nothing will work for you :p

Old Pedant
12-21-2011, 09:11 PM
$dob=$_POST['dob'];

$day= substr($dob, 0, 2);
$month = substr($dob, 3, 2);
$year = substr($dob, 6, 4);

// use THIS dob for the SQL insert:
$sqldob = $year . "-" . $month . "-" . $day;


MySQL wants to always see dates in the form 'yyyy-mm-dd' (it's okay to only give one digit for month or day, also, as in '2012-1-3' for January 3rd).

StevenPrice
12-21-2011, 09:14 PM
Date Format isn't an issue luckily :)

So I remove the mysql_query("INSERT INTO myMembers (dob) and value counterpart
and just put this bit of code
the
$sqldob = $year . "-" . $month . "-" . $day;

or wait.. I add that bit of code you have given me and leave the rest don't I?

Old Pedant
12-21-2011, 09:53 PM
Add that one line, as shown, and then change your INSERT just a tiny bit:


$sql = mysql_query("INSERT INTO myMembers (firstname, lastname, country, state, city, dob, email, password, sign_up_date, website, youtube)
VALUES('$firstname','$lastname','$country','$state','$city', '$sqldob', '$email1','$db_password', now(),'$website','$youtube')")
or die (mysql_error());

Just change $dob to $sqldob in the INSERT.

There is nothing magic about the name $sqldob. You could have use $zyzzyg or $fizzle or $FRAP both places, instead, if you wished. I just named it that to emphasize that it was indeed a DOB value intended for use with SQL.

StevenPrice
12-21-2011, 10:01 PM
This hasn't worked? I am still left with dob=0000-00-00 after I register..
Is it that dob needs to be called sqldob in my database or not?
Thanks for your help so far :)

mvmacd
12-21-2011, 10:43 PM
This hasn't worked? I am still left with dob=0000-00-00 after I register..
Is it that dob needs to be called sqldob in my database or not?
Thanks for your help so far :)

StephenPrice, if you are going to end up with the string 'YYYY-MM-DD' in your PHP, I would go back to the calendar and change the layout.

This is what the example is on the website ('http://www.rainforestnet.com/datetimepicker/datetimepicker-demo1.htm'):



onclick="javascript:NewCssCal ('dob','yyyyMMdd')"


That way it will already be in yyyy-mm-dd format. Then you don't need substr to parse the format from mm-dd-yyyy to yyyy-mm-dd, and can do this:



$sqldb = $_POST['dob'];



Also, it looks like they have a better 'arrow' way of changing months/years. All you have to do is add 'arrow' to the javascript function:



onclick="javascript:NewCssCal ('dob','mmddyyyy','arrow')"



Also, Old_Pendant is just saying you have to have the variable named the same thing both places. [When you assign it, and when you place it into the database].

StevenPrice
12-21-2011, 11:23 PM
This has still not worked :( Is there any way of me putting a date of birth field into my registration form and then sending it to my database?

Old Pedant
12-21-2011, 11:27 PM
Time to learn how to DEBUG DEBUG DEBUG.



$dob=$_POST['dob'];
echo "<hr/>DEBUG dob starts as " . $dob . "<br/>\n";

$day= substr($dob, 0, 2);
$month = substr($dob, 3, 2);
$year = substr($dob, 6, 4);

// use THIS dob for the SQL insert:
$sqldob = $year . "-" . $month . "-" . $day;
echo "DEBUG sqldob is then " . $sqldob . "<br/>\n";

$sqltext="INSERT INTO myMembers (firstname, lastname, country, state, city, dob, email, password, sign_up_date, website, youtube)"
. " VALUES('$firstname','$lastname','$country','$state','$city', '$sqldob', '$email1','$db_password', now(),'$website','$youtube')";
echo "DEBUG sqltext is " . $sqltext . "<hr/>\n";

$sql = mysql_query( $sqltext ) or die (mysql_error());


Show us what those 3 DEBUG lines show you.

Old Pedant
12-21-2011, 11:28 PM
Is there any way of me putting a date of birth field into my registration form and then sending it to my database?
What do you think we are trying to do????

That's exactly what you are attempting.

StevenPrice
12-21-2011, 11:32 PM
I meant other sorry :p

StevenPrice
12-21-2011, 11:35 PM
and can I do this tomorrow as I have to go now. Will you be on tomorrow at any point Old Pedan?
I thank you for all your help so far but I will continue tomorrow :)

Old Pedant
12-21-2011, 11:50 PM
I'm usually here around this time of day. See you then.

StevenPrice
12-22-2011, 09:14 PM
Hi Old Pedant, it says your online so let's start again and try and make this thing work!
Time to debug! So I just go into my register.php code and do what you say yeah?

Just to clarify what should my MySQL table field say.. Just dob or sql dob or what?

StevenPrice
12-22-2011, 09:25 PM
It is just dob isn't it :p and how do I show you the values? FTP it up onto the server?

Old Pedant
12-22-2011, 10:20 PM
Just copy/paste the output you see in the browser to here.

For those 3 DEBUG lines.

And yes, the field name in the DB does not matter, so long as you use the same name in the query. Note that the PHP variable name ($sqldob in my example) DOES NOT MATTER. You could use $flzie11 and MySQL wouldn't care.

StevenPrice
12-22-2011, 11:12 PM
DEBUG dob starts as 03-15-1995
DEBUG sqldob is then 1995-15-03
DEBUG sqltext is INSERT INTO myMembers (firstname, lastname, country, state, city, dob, email, password, sign_up_date, website, youtube) VALUES('Test','Test2','United Kingdom','Test3','Test4', '1995-15-03', 'ramblesocial@hotmail.com','[md5 pass: removed for security purposes]', now(),'','')

This puzzles me... it is putting it in or trying to however leaves 0000-00-00 :L :(

StevenPrice
12-22-2011, 11:14 PM
What about default dob being blank..
Then trying it

EDIT: This didn't work

Old Pedant
12-22-2011, 11:31 PM
LOL! You don't see it???

1995-15-03

That would be the 15th MONTH of 1995!!!

It's swapped.

It should be 1995-03-15 !!!

MySQL says "there is no such date" and so rejects it and gives you the zero date!

When you posted this code:

$day= substr($dob, 0, 2);
$month = substr($dob, 3, 2);
$year = substr($dob, 6, 4);
I just *ASSUMED* that the dates coming in from your <form> were in the format DD-MM-YYYY.

BUT THEY AREN'T. They are in the format MM-DD-YYYY.

SO just swap $day and $month:


$month = substr($dob, 0, 2);
$day = substr($dob, 3, 2);
$year = substr($dob, 6, 4);

Leave all the rest of the code alone.

*NOW* do you see why it is so important to learn to DEBUG DEBUG DEBUG!??

StevenPrice
12-22-2011, 11:51 PM
HAHAHA.. I am blind I tell you :p oh dear oh dear... :p
I have learnt a lesson though haven't I.. let me just check all of this

EDIT: IT WORKS :D :D FINALLY :D
Thankyou so Much for all your help :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum