Solved

showing incorrect date

Posted on 2010-08-27
36
390 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
 
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 42

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 42

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 42

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 42

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now