Solved

showing incorrect date

Posted on 2010-08-27
36
392 Views
Last Modified: 2012-05-10
hello
can someone tell me why if i look in myphpadmin, it shows that a null date field if no data is entered is 0000-00-00 but when i display the page it reads: 30-11-1999. how do i correct this? many thanks
0
Comment
Question by:peter_coop
  • 21
  • 11
  • 4
36 Comments
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543135
Have you added this field as a date field?
0
 

Author Comment

by:peter_coop
ID: 33543191
hi. yes
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543206
Are you sure you are looking at the same row in the database?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 17

Expert Comment

by:nanharbison
ID: 33543219
Can you post your query?
0
 

Author Comment

by:peter_coop
ID: 33543245
here is the query. note this is only with empty values being passed.
$query = 'INSERT INTO `act` (`service`, `activity`, `department`, `company`, `user`, `item`, `filebox`, `destroydate`, `new`) VALUES (\''.$service.'\', \''.$boxsupply.'\', \''.$deptname.'\', \''.$company.'\', \''.$user.'\', \''.strtoupper($v).'\', \''.strtoupper($val).'\', \''.$datepicker.'\', \''.$new.'\');';
                                mysql_query($query) or die('Error, query failed');

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33543284
i have done some grabs for you. thanks
myadmin-grab.png
actpage-value.png
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543285
And what do you get when you echo the date, I assume $datepicker is the date?
0
 

Author Comment

by:peter_coop
ID: 33543292
the correct value. either empty or with a date.
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543297
Oh wait, it is destroydate. I think what might be happening is that if you add a date formatted as DD-MM-YYYY  then MyPHP Admin doesn't know what to do with it because it wants YYYY-MM-DD
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543308
but when you echo it and it is the correct value, is it DD-MM-YYYY?
0
 

Author Comment

by:peter_coop
ID: 33543384
yyyy-mm-dd. this the php code i used:

$datepicker = date("Y-m-d", strtotime($_SESSION['datepick']));

thanks
0
 

Author Comment

by:peter_coop
ID: 33543404
sorry should have posted whole piece of code.

$datepicker='';
                        if(empty($_SESSION['datepick'])){
                        }
                        else
                        {
                        $datepicker = date("Y-m-d", strtotime($_SESSION['datepick']));
                        
                        }

as i say, this only happens on empty entries.
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543430
Where does the session date come from and how is it formed? Can you echo out the session date, I want to try your line of code here.,
0
 

Author Comment

by:peter_coop
ID: 33543472
this is before the piece of code i posted


this is the first page:

<input id="datepicker" name="datepicker" type="text" />

$datepicker='';
if(empty($_POST['datepicker'])){
}
else
{
$datepicker = $_POST['datepicker'];
$_SESSION['datepick'] = $datepicker;
}
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543548
So you are depending on people to enter the date as YYYY-MM-DD? If someone enters it incorrectly, it might mess up the date field in the database
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33543871
peter

I've partly answered this in your other question. But I say it again here.

If you INSERT an empty string ('') into a SQL date column, it will get stored in mySQL as 0000-00-00 00:00:00

If you then retrieve that value from the database and try to format it in PHP you will have problems - PHP doesn't recognise that as a date.

Somewhere in the code that you use to display your page, PHP is trying to format the mySQL date and struggling. You should really insert NULL into the database if you don't have a value.


0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33543901
So change your code to this

$datepicker='';
if(empty($_POST['datepicker']))
{
	$datepicker = NULL;
}
else
{
	$datepicker = $_POST['datepicker'];
	$_SESSION['datepick'] = $datepicker;
}

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33543935
@nanharbison
i am using jquery datepicker if they wish to use a date which appears on focus.

@ChrisStanyon
how would i then code that based upon my posted code? thanks
0
 

Author Comment

by:peter_coop
ID: 33543948
sorry nan. we must have crossposted. tring that now.
0
 

Author Comment

by:peter_coop
ID: 33543983
@nan

i have changed to your code exactly what you posted and when i echo the var i can see that it being entered as NULL but is still appearing as that date i posted earlier. thanks
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33544070
peter

In your code, look at where you retrieve the records from the database, and see how you are echoing the date. You need to check to see if it's NULL.





if (is_null($row['DateValue']))
{
echo "The date is NULL";
}
else
{
echo "The date is : " . date("Y M d", $row['DateValue']);
}

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33544221
this is driving me bonkers. i shall post the code start to finish because it is still displaing that date.

thanks
showing-incorrect-date-code.txt
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33544300
When retrieving the date from the database, check if it's NULL using is_null - if it is then don't try to format it with date - you will not get the right info back - just set it to NULL.

When entering the date into the database, you are not entering it as NULL - you are entering it as "NULL" - which will default to 0000-00-00

"NULL" = 4 character string
NULL = Value Not Set or Unknown

INSERT INTO table 'date' VALUES ("NULL"); is not the same as
INSERT INTO table 'date' VALUES (NULL);






$date=(is_null($row['date']) ? NULL : date("D M Y", $row['date']);

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33544361
chris

$destroydate=(is_null($row['destroydate']) ? NULL : date("D M Y", $row['destroydate']);

Parse error: syntax error, unexpected ';' thanks
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 33544579
You need more parentheses
$destroydate=((is_null($row['destroydate']) ? NULL : date("D M Y", $row['destroydate'])); 

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33544939
sam error nan.

$destroydate=((is_null($row['destroydate']) ? NULL : date("D M Y", $row['destroydate']));
0
 
LVL 17

Accepted Solution

by:
nanharbison earned 125 total points
ID: 33545219
forgot one parenthesis! Sorry, this is the stuff that always slows me down.
$destroydate=(is_null($row['destroydate'])) ? NULL : date("D M Y", $row['destroydate']); 

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33545676
still the same showing that friggin date :-) i have posted all my code and db grabs, where the hell am i going wrong? thanks
0
 

Author Comment

by:peter_coop
ID: 33545713
this is getting weireder. in the db the value is: 0000-00-00, in the page that is displaying the data it is showing 30-11-1999 and if i echo $destroydate from the page that makes the call to the db it shows Thu Jan 1970. weird
0
 

Author Comment

by:peter_coop
ID: 33545733
i ran the code that chris posted earlier:

if (is_null($row['destroydate']))
{
echo "The date is NULL";
}
else
{
echo "The date is : " . date("Y M d", $row['destroydate']);
}

and it returned: The date is : 1970 Jan 01
0
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 125 total points
ID: 33546115
peter

The problem you're having is all to do with the fact that you are setting your date column to "0000-00-00" by not inserting NULL.

The code you've posted above is working exactly as it should. Your database column isn't NULL - it's 0000-00-00.

If you insert anything other than a valid date or NULL your database record will contain 0000-00-00. Once you've inserted that into the database there is very little you can do. The code I've posted will only work once you know you are inserting NULL into the database. According to your code, even if $datepicker is empty you are inserting an empty string into the date field - THIS IS NOT NULL!

If the date in the Database is "0000-00-00" then you are always going to have problems trying to format it using PHP.

When you insert your record into the table MAKE SURE YOU INSERT NULL and not "NULL" - note the quotes. If you've already inserted records that contain 0000-00-00 then delete them and start again!!!!

Not really sure how to make it clearer without writing your code for you :)






//This will put 0000-00-00 into your table and your code won't work
INSERT INTO table ('date') VALUES (''); 

//This will put 0000-00-00 into your table, and your code won't work
INSERT INTO table ('date') VALUES ('NULL'); 

//This will put NULL into your table - THIS IS WHAT YOU WANT!
INSERT INTO table ('date') VALUES (NULL); 

Open in new window

0
 

Author Comment

by:peter_coop
ID: 33546723
i understand what you are syaing chris, but what i cannot grasp is all the way along the value is being reported as NULL when i do var_dump. ie, string(1) "9" NULL . if i start from the beginning, i have this input:

that is correct

i then have this to process that field:

$datepicker='';
if(empty($_POST['datepicker'])){

$datepicker = NULL;
}
else
{
$datepicker = $_POST['datepicker'];
$_SESSION['datepick'] = $datepicker;
} string(0) "" <!--- is this correct looking at this i think not. how to correct this from code above.

and this code also inserts into the db which after contains 0000-00-00. i understand what you are saying chris but am at a loss where to correct it. thanks
0
 

Author Comment

by:peter_coop
ID: 33546813
the only other thing i can see that may cause it is this line of code which is in the action area which displays the page:

$destroydate = date('d-m-Y', strtotime(mysql_result($act,$i,"destroydate")));

if i remove the date it just shows 0000-00-00 which i thought was the default for a date field?

$destroydate = mysql_result($act,$i,"destroydate");
0
 

Author Comment

by:peter_coop
ID: 33546909
oh well off to bed now. catch this tomorrow :-)
0
 

Author Comment

by:peter_coop
ID: 33548748
ok guys i'm gonna close this thread and find another way to do this. many thanks for all your hard work and patience and it it's ok i shall split the points between ChrisStanyon & nanharbison
0
 

Author Closing Comment

by:peter_coop
ID: 33548752
thanks once again
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question