• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

showing incorrect date

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
peter_coop
Asked:
peter_coop
  • 21
  • 11
  • 4
2 Solutions
 
nanharbisonCommented:
Have you added this field as a date field?
0
 
peter_coopAuthor Commented:
hi. yes
0
 
nanharbisonCommented:
Are you sure you are looking at the same row in the database?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nanharbisonCommented:
Can you post your query?
0
 
peter_coopAuthor Commented:
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
 
peter_coopAuthor Commented:
i have done some grabs for you. thanks
myadmin-grab.png
actpage-value.png
0
 
nanharbisonCommented:
And what do you get when you echo the date, I assume $datepicker is the date?
0
 
peter_coopAuthor Commented:
the correct value. either empty or with a date.
0
 
nanharbisonCommented:
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
 
nanharbisonCommented:
but when you echo it and it is the correct value, is it DD-MM-YYYY?
0
 
peter_coopAuthor Commented:
yyyy-mm-dd. this the php code i used:

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

thanks
0
 
peter_coopAuthor Commented:
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
 
nanharbisonCommented:
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
 
peter_coopAuthor Commented:
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
 
nanharbisonCommented:
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
 
Chris StanyonCommented:
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
 
nanharbisonCommented:
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
 
peter_coopAuthor Commented:
@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
 
peter_coopAuthor Commented:
sorry nan. we must have crossposted. tring that now.
0
 
peter_coopAuthor Commented:
@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
 
Chris StanyonCommented:
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
 
peter_coopAuthor Commented:
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
 
Chris StanyonCommented:
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
 
peter_coopAuthor Commented:
chris

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

Parse error: syntax error, unexpected ';' thanks
0
 
nanharbisonCommented:
You need more parentheses
$destroydate=((is_null($row['destroydate']) ? NULL : date("D M Y", $row['destroydate'])); 

Open in new window

0
 
peter_coopAuthor Commented:
sam error nan.

$destroydate=((is_null($row['destroydate']) ? NULL : date("D M Y", $row['destroydate']));
0
 
nanharbisonCommented:
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
 
peter_coopAuthor Commented:
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
 
peter_coopAuthor Commented:
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
 
peter_coopAuthor Commented:
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
 
Chris StanyonCommented:
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
 
peter_coopAuthor Commented:
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
 
peter_coopAuthor Commented:
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
 
peter_coopAuthor Commented:
oh well off to bed now. catch this tomorrow :-)
0
 
peter_coopAuthor Commented:
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
 
peter_coopAuthor Commented:
thanks once again
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 21
  • 11
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now