How to fix the date in a MySQL db

[Newsscanner]

Hello,

In my MySQL database, one of the fields eople have to fill in is "DOB"
(Date of Birth).
I have now managed to begin inserting data into my DB via a form, the
data type for the DOB field is "DATE", but every time I add data and
then check my db, I see the DOB displayed as "0000-00-00".
Has anyone got any idea how I could put this right?

TIA,
Newsscanner.

[Aggro]

Newsscanner wrote:

I have now managed to begin inserting data into my DB via a form, the
data type for the DOB field is "DATE", but every time I add data and
then check my db, I see the DOB displayed as "0000-00-00".
Has anyone got any idea how I could put this right?

Can you provide the SQL query you are using to insert the data? Without
it, it is impossible to tell what you are doing wrong, but commong
mistakes are:

# Sometimes user forgets to add '' around the value
insert into table(DOB) values( 2005-08-16 ); # Wrong
insert into table(DOB) values( '2005-08-16' ); # Correct

# Sometimes the date is given in wrong format. Use yyyy-mm-dd format
insert into table(DOB) values( '8/16/2005' ); # Wrong
insert into table(DOB) values( '2005-08-16' ); # Correct

# Comming error also happens with the programming language
# and the value of the variable is empty
insert into table(DOB) values( '' ); # Wrong
insert into table(DOB) values( '2005-08-16' ); # Correct

[Newsscanner]

Aggro wrote:

Newsscanner wrote:

I have now managed to begin inserting data into my DB via a form, the
data type for the DOB field is "DATE", but every time I add data and
then check my db, I see the DOB displayed as "0000-00-00".
Has anyone got any idea how I could put this right?

Can you provide the SQL query you are using to insert the data? Without
it, it is impossible to tell what you are doing wrong, but commong
mistakes are:

# Sometimes user forgets to add '' around the value
insert into table(DOB) values( 2005-08-16 ); # Wrong
insert into table(DOB) values( '2005-08-16' ); # Correct

# Sometimes the date is given in wrong format. Use yyyy-mm-dd format
insert into table(DOB) values( '8/16/2005' ); # Wrong
insert into table(DOB) values( '2005-08-16' ); # Correct

# Comming error also happens with the programming language
# and the value of the variable is empty
insert into table(DOB) values( '' ); # Wrong
insert into table(DOB) values( '2005-08-16' ); # Correct

OK, thanks.
But since the input comes from the user, I thought it would display
whatever has been typed in.
My query looks like this:
"$DOB=$_POST['DOB'];
And further:
"$query = "INSERT into Names (Name,FirstNames,DOB,Sex)
VALUES ('$Name','$FirstNames','$DOB','$Sex')";"

So, is that where I do something wrong?

TIA,
Newsscanner.

[Aggro]

Newsscanner wrote:

"$query = "INSERT into Names (Name,FirstNames,DOB,Sex)
VALUES ('$Name','$FirstNames','$DOB','$Sex')";"

That is not the query. That is PHP code which generates the query.

echo $query;

And tell what it prints out.

[Bill Karwin]

Newsscanner wrote:

But since the input comes from the user, I thought it would display
whatever has been typed in.
My query looks like this:
"$DOB=$_POST['DOB'];
And further:
"$query = "INSERT into Names (Name,FirstNames,DOB,Sex)
VALUES ('$Name','$FirstNames','$DOB','$Sex')";"

That will work only if your DOB field is a CHAR or VARCHAR.
MySQL needs to know how to parse the DOB string into a real date value.
MySQL understands _one_ date input format: YYYY-MM-DD, with optional
HH::MM::SS.

MySQL also has a STR_TO_DATE() function, but again, each invocation of
that function understands one format, which you define in an argument to
the function. See
http://dev.mysql.com/doc/mysql/en/da...functions.html.

In one project, I had to allow users to type in an arbitrary string in a
Perl web application, so I used the Perl module Date::Manip, which has a
very flexible date-parsing feature (it even parses correctly strings
like "a week from next tuesday"). Then I'd verify that the date parsing
succeeded, or else return an error to the user and ask them to try
again. If it succeeds, then transform the date into YYYY-MM-DD and use
that for the SQL statement.

I'm guessing you're using PHP based on the use of $_POST['DOB'].
I'm not as familiar with PHP as I am with Perl, but I see in the docs
that there's a function strtotime() that does some pretty flexible
date-parsing. http://us3.php.net/strtotime

Also, it's very important that you validate all user data before using
them in your SQL statements. What if some malicious user posts the
following value for DOB and you interpolate it into your query above
without any validation:

2000-01-01', 'M'); DELETE from Names; // Mwa ha ha

Regards,
Bill K.

[Newsscanner]

Aggro wrote:

Newsscanner wrote:

"$query = "INSERT into Names (Name,FirstNames,DOB,Sex)
VALUES ('$Name','$FirstNames','$DOB','$Sex')";"

That is not the query. That is PHP code which generates the query.

echo $query;

And tell what it prints out.

After doing that and entering some dummy data (also entering the date in
MySQL's own format YYYY-mm-DD), this is what I got:
"INSERT into Names (Name,FirstNames,DOB,Sex) VALUES
('Ok','gie','2001-01-01','Male')"

Thanks,
Newsscanner.

[Newsscanner]

Bill Karwin wrote:

Newsscanner wrote:

But since the input comes from the user, I thought it would display
whatever has been typed in.
My query looks like this:
"$DOB=$_POST['DOB'];
And further:
"$query = "INSERT into Names (Name,FirstNames,DOB,Sex)
VALUES ('$Name','$FirstNames','$DOB','$Sex')";"

That will work only if your DOB field is a CHAR or VARCHAR.
MySQL needs to know how to parse the DOB string into a real date value.
MySQL understands _one_ date input format: YYYY-MM-DD, with optional
HH::MM::SS.

MySQL also has a STR_TO_DATE() function, but again, each invocation of
that function understands one format, which you define in an argument to
the function. See
http://dev.mysql.com/doc/mysql/en/da...functions.html.

In one project, I had to allow users to type in an arbitrary string in a
Perl web application, so I used the Perl module Date::Manip, which has a
very flexible date-parsing feature (it even parses correctly strings
like "a week from next tuesday"). Then I'd verify that the date parsing
succeeded, or else return an error to the user and ask them to try
again. If it succeeds, then transform the date into YYYY-MM-DD and use
that for the SQL statement.

I'm guessing you're using PHP based on the use of $_POST['DOB'].
I'm not as familiar with PHP as I am with Perl, but I see in the docs
that there's a function strtotime() that does some pretty flexible
date-parsing. http://us3.php.net/strtotime

Also, it's very important that you validate all user data before using
them in your SQL statements. What if some malicious user posts the
following value for DOB and you interpolate it into your query above
without any validation:

2000-01-01', 'M'); DELETE from Names; // Mwa ha ha

Regards,
Bill K.

Thanks Bill.
Which also reminds me of another problem (I am a newbie, remember).
The first and primary field is to give everyone an ID number, it is
generated automatically.
Here is the setup:
ID tinyint(4) UNSIGNED No auto_increment
Change Drop Primary Index Unique Fulltext

What puzzles me is that, since I started entering dummy data for testing
purposes, and then erased them, my ID keeps incrementing, in other
words, it does not seem to be reset when the DB gets emptied.
Anything I can do about this?

Thx,
Newsscanner.

[Bill Karwin]

Newsscanner wrote:

What puzzles me is that, since I started entering dummy data for testing
purposes, and then erased them, my ID keeps incrementing, in other
words, it does not seem to be reset when the DB gets emptied.
Anything I can do about this?

Auto-increment values are supposed to be unique, but are not necessarily
contiguous, and are only monotonically increasing by coincidence of the
implementation.

Consider what would happen if you were inserting non-dummy data for some
number of weeks or months. Say there'd now be 75,000 records in that table.

Then one day you need to delete the record with primary key value 34,221
for some legitimate reason.

Should MySQL renumber the primary keys of all subsequent rows
34,222-75,000 to keep the primary key values contiguous? What if there
were millions of rows following the one you want to delete? What about
foreign key values in tables that reference this primary table? All
those rows that reference the thousands or millions of rows whose
primary key values are changing would also have to change.

If MySQL were to keep auto-generated primary key values contiguous, it
would almost certainly make a simple single-row DELETE operation take an
unreasonably long time.

Regards,
Bill K.

[Bill Karwin]

Newsscanner wrote:

After doing that and entering some dummy data (also entering the date in
MySQL's own format YYYY-mm-DD), this is what I got:
"INSERT into Names (Name,FirstNames,DOB,Sex) VALUES
('Ok','gie','2001-01-01','Male')"

Did this value insert correctly, or is it '0000-00-00' when you retrieve
it from the database?

What is the datatype of the Names.DOB field?

Can you also tell us what version of MySQL you are using? Some
date/time datatypes have changed behavior subtlely between versions.

Regards,
Bill K.

[Newsscanner]

Bill Karwin wrote:

Newsscanner wrote:

After doing that and entering some dummy data (also entering the date
in MySQL's own format YYYY-mm-DD), this is what I got:
"INSERT into Names (Name,FirstNames,DOB,Sex) VALUES
('Ok','gie','2001-01-01','Male')"

Did this value insert correctly, or is it '0000-00-00' when you retrieve
it from the database?

What is the datatype of the Names.DOB field?

Can you also tell us what version of MySQL you are using? Some
date/time datatypes have changed behavior subtlely between versions.

Regards,
Bill K.

It did insert correctly, but I literally inserted "2001-01-01".
The datatype is "DATE".
I have MySQL 4.1.13
Thanks.

[Bill Karwin]

Newsscanner wrote:

It did insert correctly, but I literally inserted "2001-01-01".
The datatype is "DATE".
I have MySQL 4.1.13
Thanks.

Okay, thanks for the detail.

What you have to do is to make sure the date string you interpolate in
your SQL statement is in the YYYY-MM-DD format. MySQL will not accept
any other format.

My previous posting was missing the explicit statement:
your PHP code must take the user's input, parse it into some date
representation -- for instance, using PHP's function strtotime() -- and
then format that PHP date object into a different date string -- PHP has
the date() function for that.

Example:

$DOB_timestamp = strtotime($_POST['DOB']);

if ($DOB_timestamp == -1 or $DOB_ts == FALSE) {
/* User input is not recognized as a date.
Give an error, ask user to reenter date,
and return to the input form. */
}

$DOB = date('Y-m-d', $DOB_timestamp);

Regards,
Bill K.