Date conversion

[nimish]

I have Month, Day and Year fields on my form. When I submit this form I
want to create a valid date from all three fields before inserting into
MYSQL table.
I tried following:
$dob=$Month."-".$Day."-".$Year;
$dob = strtotime($dob);
But it does not update my date field in the table.
I am novice in PHP, do you know where i am making a mistake.

[Chris Hope]

nimish wrote:

I have Month, Day and Year fields on my form. When I submit this form
I want to create a valid date from all three fields before inserting
into MYSQL table.
I tried following:
$dob=$Month."-".$Day."-".$Year;
$dob = strtotime($dob);
But it does not update my date field in the table.
I am novice in PHP, do you know where i am making a mistake.

A MySQL date field is in the format YYYY-MM-DD so you need to format it
like so:

$dob = "$Year-$Month-$Day";

and then not do any further conversion.

Note that you should really be using $_POST for values passed from a
form as register_globals is now off by default and you cannot rely on
eg $Month being posted from a form value named "Month".

You also cannot trust any data sent from a web form and need to validate
it first. This can be as simple as casting the value as an integer like
so:

$dob = (int)$_POST['Year'].'-'.(int)$_POST['Month'].(int)$_POST['Day'];

This doesn't actually ensure it is a valid date though; you can test it
with the checkdate() function: http://www.php.net/checkdate

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[nimish]

Thanks very much Chris.

I changed output in YYYY-MM-DD format as per your instructions, and
even check date using checkdate(). And it returns true. But still it
is not updating my table. I am writing SQL statement like this:
$query = "insert into tbl_master(dob) values($dob)";

Now what can be the wrong?
Nimish

Chris Hope wrote:

nimish wrote:

I have Month, Day and Year fields on my form. When I submit this form I want to create a valid date from all three fields before inserting into MYSQL table.
I tried following:
$dob=$Month."-".$Day."-".$Year;
$dob = strtotime($dob);
But it does not update my date field in the table.
I am novice in PHP, do you know where i am making a mistake.
A MySQL date field is in the format YYYY-MM-DD so you need to format

it like so:

$dob = "$Year-$Month-$Day";

and then not do any further conversion.

Note that you should really be using $_POST for values passed from a
form as register_globals is now off by default and you cannot rely on
eg $Month being posted from a form value named "Month".

You also cannot trust any data sent from a web form and need to validate it first. This can be as simple as casting the value as an integer like so:

$dob = (int)$_POST['Year'].'-'.(int)$_POST['Month'].(int)$_POST['Day'];
This doesn't actually ensure it is a valid date though; you can test it with the checkdate() function: http://www.php.net/checkdate

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[nimish]

Thanks very much Chris.
I changed output in YYYY-MM-DD format as per your instructions, and
even check date using checkdate(). And it returns true. But still it
is not updating my table. I am writing SQL statement like this:
$query = "insert into tbl_master(dob) values($dob)";
Now what can be the wrong?
Nimish

[Ken Robinson]

nimish wrote:

Thanks very much Chris.

I changed output in YYYY-MM-DD format as per your instructions, and
even check date using checkdate(). And it returns true. But still it
is not updating my table. I am writing SQL statement like this:
$query = "insert into tbl_master(dob) values($dob)";

Does the call to mysql_query() return any errors?

Try to do the query like this:

$query = "insert into tbl_master set dob='" . $dob . "'";
$result = @mysql_query($query);
if (!$result) echo "There's a problem with the
insert<br>".mysql_error().'<br>Query:'.$query."<br >\n";

Ken

[nimish]

Ken,
I am not getting any error in SQL execution. I get 0000-00-00 00:00:00
in this field every time, which is a default value. Actually query is
not getting fail, as I am seeing other fields getting populated.
Nimish

[Steve]

Print the query to see what is being sent to MYSQL:

....
$query = "insert into tbl_master(dob) values($dob)";
print "DEBUG: $query\n";
....

If the result looks like this:

DEBUG: insert into tbl_master(dob) values(2005-01-11)

the problem is the way you have formatted the date. Try this*:

....
$dob = "$Year-$Month-$Day";
$query = "insert into tbl_master(dob) values(\"$dob\")";
....

....or this*:

....
$dob = "$Year$Month$Day";
$query = "insert into tbl_master(dob) values($dob)";
....

(*example code only: naturally you will also take into account the
comments about not trusting user input)

---
Steve

[nimish]

Great!!
....
$dob = "$Year-$Month-$Day";
$query = "insert into tbl_master(dob) values(\"$dob\")";
This works.
Thanks a lot.

[Michael Fesser]

.oO(nimish)

$dob = "$Year-$Month-$Day";
$query = "insert into tbl_master(dob) values(\"$dob\")";

Should be

$query = "insert into tbl_master(dob) values('$dob')";

Strings are enclosed with single quotes in SQL. Using double quotes is a
proprietary MySQL feature.

Another question: Where do $Year, $Month and $Day come from? I hope (for
you and your db) you don't use user-submitted values directly without
validation in a database query. If these values are submitted by a form
access them with one of the arrays $_GET or $_POST (dependent on the
used submission method) and make sure they contain allowed values.

Micha