|
Home > Archive > MySQL Server Forum > August 2005 > How to fix the date in a MySQL db
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to fix the date in a MySQL db
|
|
| Newsscanner 2005-08-16, 1:24 pm |
| 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.
| |
|
| 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 2005-08-16, 1:24 pm |
| Aggro wrote:
> Newsscanner wrote:
>
>
>
> 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','$FirstName
s','$DOB','$Sex')";"
So, is that where I do something wrong?
TIA,
Newsscanner.
| |
|
| Newsscanner wrote:
> "$query = "INSERT into Names (Name,FirstNames,DOB
,Sex)
> VALUES ('$Name','$FirstName
s','$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 2005-08-16, 1:24 pm |
| 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','$FirstName
s','$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/d...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 2005-08-16, 8:23 pm |
| Aggro wrote:
> Newsscanner wrote:
>
>
>
> 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 2005-08-16, 8:23 pm |
| Bill Karwin wrote:
> Newsscanner wrote:
>
>
>
> 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/d...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 2005-08-16, 8:23 pm |
| 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 2005-08-16, 8:23 pm |
| 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 2005-08-17, 3:23 am |
| Bill Karwin wrote:
> Newsscanner wrote:
>
>
>
> 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 2005-08-17, 1:23 pm |
| 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.
|
|
|
|
|