473,549 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to fix the date in a MySQL db

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.
Aug 16 '05 #1
10 2338
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
Aug 16 '05 #2
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=$_POS T['DOB'];
And further:
"$query = "INSERT into Names (Name,FirstName s,DOB,Sex)
VALUES ('$Name','$Firs tNames','$DOB', '$Sex')";"

So, is that where I do something wrong?

TIA,
Newsscanner.
Aug 16 '05 #3
Newsscanner wrote:
"$query = "INSERT into Names (Name,FirstName s,DOB,Sex)
VALUES ('$Name','$Firs tNames','$DOB', '$Sex')";"


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

echo $query;

And tell what it prints out.
Aug 16 '05 #4
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=$_POS T['DOB'];
And further:
"$query = "INSERT into Names (Name,FirstName s,DOB,Sex)
VALUES ('$Name','$Firs tNames','$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.
Aug 16 '05 #5
Aggro wrote:
Newsscanner wrote:
"$query = "INSERT into Names (Name,FirstName s,DOB,Sex)
VALUES ('$Name','$Firs tNames','$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,FirstName s,DOB,Sex) VALUES
('Ok','gie','20 01-01-01','Male')"

Thanks,
Newsscanner.
Aug 16 '05 #6
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=$_POS T['DOB'];
And further:
"$query = "INSERT into Names (Name,FirstName s,DOB,Sex)
VALUES ('$Name','$Firs tNames','$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.
Aug 16 '05 #7
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.
Aug 16 '05 #8
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,FirstName s,DOB,Sex) VALUES
('Ok','gie','20 01-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.
Aug 16 '05 #9
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,FirstName s,DOB,Sex) VALUES
('Ok','gie','20 01-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.
Aug 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3180
by: Yulia Yegenov | last post by:
I have a query that looks like this: (I insert the date created with the php date function) $status = "Active"; //(I cannot use the mysql timestamp function for other reason). $curdate = date("m/j/Y H:i"); $owner = "not assigned"; @ $db_connect = mysql_connect("localhost", "$db_username", "$db_password");
5
3716
by: Dominique Javet | last post by:
Hello, I'm new to php and mysql and I use Dreamweaver MX 2004, so sorry for this "newbie" question... I've found no answer in the forum ... I've a date problem with my formular. In my mysql DB my filed "date" in table "experience" is like this: Y-m-d (2002-07-23). My fied`date` is date, NOT NULL with no default entry My form read well...
0
1828
by: Jack Lauman | last post by:
I have a table with a column "date" that contains dates in SQL format, i.e. 2003-08-10 I need to query the table and return all the rows from the current date thru the next 6 days. (i.e. today 2003-08-10 thru Saturday 2003-08-16). I have tried the following query which returns all of the desired rows except those for the current date. ...
4
26340
by: MyOracle | last post by:
Hi everybody, I just curious about change date(0000-00-00) to date (00-00-0000) in mysql.Can anyone tell me about that. Thanks. izmanhaidi.
2
17382
by: Paul | last post by:
I am trying to insert a date into a mysql table but everytime I attempt it the date field is always empty. The field is of Date type and the date in code is in European format dd/mm/yyyy. Does the date have to be formatted in any particualr way from vb.net.
3
3431
by: dave | last post by:
I need to compute an expiration date based on the number of hours, days, or months purchased. The expiration date needs to be expressed in minutes something like '1260481600'. How can I get the current date and time expressed in minutes? Once I have this number I can add the number of minutes purchased to the current date/time to get the...
6
3927
by: Geoff Cox | last post by:
Hello, at the moment I can add the combined date and time into MySQL using php $dt1 = date("Y-m-d H:i:s"); is it possible to add the date and time separately? I thought it might be
10
4005
by: WebCM | last post by:
There is a function: http://paste.ubuntu.com/21865 It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: datetime. If date is the same as today, the function returns "Today". There is one problem. This function does not recognize time zones. How to adjust date to user's time zone? Is converting to timestamp() and then to format...
3
3528
by: janetopps | last post by:
I have a news website, with asp pages, which was on Access, and i upgraded to MySQL, i used Bullzip to transfer the data. It had about 1000 pages, which im now able to pull up on the public side. Im sorting out a few glitches though. Since i upgraded from ms access database to MySQL, i have added about 4 articles to test the new setup. I note...
0
7526
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7457
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7965
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7817
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6051
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3487
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1949
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1063
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
771
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.