showing incorrect date

Have you added this field as a date field?

hi. yes

Are you sure you are looking at the same row in the database?

Can you post your query?

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');

Select allOpen in new window

i have done some grabs for you. thanks
myadmin-grab.png
actpage-value.png

And what do you get when you echo the date, I assume $datepicker is the date?

the correct value. either empty or with a date.

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

but when you echo it and it is the correct value, is it DD-MM-YYYY?

yyyy-mm-dd. this the php code i used:

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

thanks

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.

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.,

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;
}

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

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.

So change your code to this

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

Select allOpen in new window

@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

sorry nan. we must have crossposted. tring that now.

@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

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']);
}

Select allOpen in new window

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

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']);

Select allOpen in new window

chris

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

Parse error: syntax error, unexpected ';' thanks

You need more parentheses

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

Select allOpen in new window

sam error nan.

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

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

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

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

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

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");

oh well off to bed now. catch this tomorrow :-)

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

thanks once again