Clone or Add Record changes null date to "000-00-00"

[JohnKlassen]

Hi,

I saw a post with a similiar problem but I did not see a solution.

I have a table with a number of date fields in it. These fields are defined as null with a default of null. When I load the data into the table, I notice in phpyMyAdmin that the date fields that don't have a date in them are changed to "0000-00-00". I fixed the exisitng data by using SQL to change the "0000-00-00" to nulls. I thought that might fix the problem but then I noticed that when I add a record or clone a record in this table, that the dates that I don't fill in show as "0000-00-00" in phpMyAdmin.

I have a report where I am checking to see if the date is not null and then moving the date into a variable. As a result this report is moving invalid dates into the variable.

What do you suggest?

John

[massiws]

John,
from MySQL documentation (http://dev.mysql.com/doc/refman/5.0/en/datetime.html): Illegal DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

Instead of check a null value, can't you check <> '0000-00-00' ?
Can't you fill these data values with a default value?

img.jpg

Max

[JohnKlassen]

Max,

Thanks for the suggestion. If I use your suggestion I will have to rethink how I check things. Here is the scenario:

My client manages a bunch of apartments. When a tenant breaks a rule, my client sends them a notice and enters the date in my application. The tenant can receive up to 5 notices and therefore I store up to 5 dates, date1 through date5. The dates are filled in consecutively which means that date 3 cannot have a date in it unless dates 1 and 2 also have dates in them.

My client wants a report where they want to know how many days from today since the first notice was sent and how many days since the most recent notice was sent out. Calculating the number of days since the first notice was no problem. The challenge was to determine what is the most recent date in the date1 through date5 5 fields.

I set a variable called $recent_date to null and then looped through the 5 date fields starting with date5 and working down to date1. I checked if $recent_date was empty and if date5 was not empty, then I moved date5 to $recent_date. Here is some of my code:

Code: Select all

 $recent_date = null;
 
  if ((empty($recent_date)) AND 
     (!empty($violations_id->vio_date_entered_5)))  
  {
    $recent_date = $violations_id->vio_date_entered_5;
  }

This worked fine as long as the 'empty' dates were null. It was when I added a new record that I saw that some fields no longer had nulls but had '0000-00-00'. Since '0000-00-00' is not 'empty', I found that I was moving '0000-00-00' to $recent_date and my logic and calculations for number of days was off.

I am trying to figure out the best way to incorprate your suggestion without making the code too cumbersome. I suppose another option is to check date2 through date5 for '0000-00-00' and set it to null before I use my exisitng logic. (Date1 alwyas has a valid date).

As far as setting the date in the text field to a default value, is there a way to set them to null if not filled in?

Thanks,

John

[JohnKlassen]

Hi,

I resolved the problem by changing my logic. In addition to the 5 possible dates for notifications, I have another column in the table that has a 'notification number'. The notification number is equal to the number of the most recent date. For example, if the notification number is '3', the most recent date is the date in date3. This totally avoids the issue of nulls versus '0000-00-00'.

My code now looks like:

Code: Select all

    elseif ($violations_id->vio_violation_number == '2')
  {
    $recent_date = $violations_id->vio_date_entered_2;
    $first_date = $violations_id->vio_date_entered_1;

Thanks for your help.

John

[admin]

.