Page 1 of 1

conditionally format date from POST

Posted: Thu May 06, 2010 10:51 am
by phpBever
I've got a form that sends a field 'newDate' to POST. I need to format the new date for MySQL and have a function to do that. My problem is getting the function to do nothing if there is nothing sent (now I'm getting 0000-00-00 in the field when the 'newDate' field is blank).

I think my problem is not knowing how newDate comes through if nothing has been entered. I've tried using if(!is_null( )) and !="" etc inside the function

Code: Select all

$newDate = $_POST['newDate'];
function formatMySQLDate($date)
{
	if(!is_null($date))
	{
	   $day = substr($date,3,2);
     $mon = substr($date,0,2);
     $year = substr($date,6);
     $newdate= $year.'-'.$mon.'-'.$day;
     return $newdate;
  }
  else {return $date;}
}
$newDate = formatMySQLDate($newDate);
and also as a condition to apply the function

Code: Select all

$newDate = $_POST['newDate'];
function formatMySQLDate($date)
{
   $day = substr($date,3,2);
     $mon = substr($date,0,2);
     $year = substr($date,6);
     $newdate= $year.'-'.$mon.'-'.$day;
     return $newdate;
}
if ($newDate !=""){
  $newDate = formatMySQLDate($newDate);
}
Nothing seems to work. I keep getting the 0000-00-00 submitted to MySQL.

Can anyone help on this?

Thanks

Re: conditionally format date from POST

Posted: Thu May 06, 2010 11:13 am
by AbraCadaver
That is the default that MySQL is inserting when you don't supply a value. What do you want it to do?

Re: conditionally format date from POST

Posted: Thu May 06, 2010 11:35 am
by phpBever
Yeah--I think I just figured that out myself also. What I want is that nothing be written to the field if there are no changes. The query includes other fields which might have changes.

Re: conditionally format date from POST

Posted: Thu May 06, 2010 11:54 am
by AbraCadaver
phpBever wrote:Yeah--I think I just figured that out myself also. What I want is that nothing be written to the field if there are no changes. The query includes other fields which might have changes.
OK, here is the problem: if you supply a field name in an UPDATE then it will insert something, either the value you supply or the default. Consider these two scenarios:

You have one row in your table and the values are:
[text]
something the_date
test 2010-01-01[/text]

This will insert 0000-00-00 into the_date because you specified the field but supplied an empty value:

Code: Select all

$value = '';
$query = "UPDATE table_name SET something = 'some value', the_date = '$value'";
This will not change the_date because you didn't specify the field the_date:

Code: Select all

$query = "UPDATE table_name SET something = 'some value';

Re: conditionally format date from POST

Posted: Thu May 06, 2010 1:52 pm
by phpBever
OK, thanks.

So I'm thinking I need to figure out a way to generate my query strings such that they only include POST values where

Code: Select all

strlen(post-value) != 0
?

There doesn't seem to be anything being written to the char/varchar fields in the same situation. So another solution might be just to make the date fields into CHAR fields since I probably won't need to be doing any date calculations with them anyway.

Thanks again.

Re: conditionally format date from POST

Posted: Thu May 06, 2010 2:02 pm
by AbraCadaver
phpBever wrote:OK, thanks.

So I'm thinking I need to figure out a way to generate my query strings such that they only include POST values where

Code: Select all

strlen(post-value) != 0
?

There doesn't seem to be anything being written to the char/varchar fields in the same situation. So another solution might be just to make the date fields into CHAR fields since I probably won't need to be doing any date calculations with them anyway.

Thanks again.
The reason is that many times the default is NULL which is really an empty value.

There are several ways to do what you want but really I think that this should be in your form validation code. When you display the form to update the data don't you display the current values? If so then they should be submitted and so the date would update to the same date that was in the form. If they submit an empty date and it isn't required, then whats wrong with 0000-00-00?

Re: conditionally format date from POST

Posted: Thu May 06, 2010 2:19 pm
by phpBever
I've got double sets of fields--the original values and the corrected values. We need to keep the original values, so we don't just overwrite them with the corrected values. And then we do things depending on whether there is anything in certain corrected value fields. That's why I want that field to stay empty if there is no correction. So rather than try to generate ad hoc query strings, I'll just change those date fields to char.

Thanks for the insights.