Blank Fields

[jeeves245]

Another quick question

 

I have a set up a basic database management system for my website where I can update database entires via a form. A few of my fields for each row do not require data (i.e. the membership expiry date is blank for a member who has not yet paid.)

When I edit a row (that has blank fields) and submit the changes the fields such as the date have been replaced with "0000-00-00".

 

Is there any way I can stop this from happening? I need blank fields to be left blank, other wise my "WHERE ExpiryDate<CURRENT_DATE" condition gets messed up.

 

Cheers for any info.

[trq]

Set there default to NULL.

[jeeves245]

Yeah I already tried that.. Didn't change anything.

 

Is there a way to say "WHERE ExpiryDate<CURRENT_DATE NOT 0000-00-00"?

[fenway]

Yeah I already tried that.. Didn't change anything.

Of course not... did you update all of the existing values?

[jeeves245]

Yeah I already tried that.. Didn't change anything.

Of course not... did you update all of the existing values?

 

Yes. When I hit submit, the 0000-00-00 values just come right back :-\

[fenway]

"Submit" isn't mysql.  You're populating the field with blanks, not nulls from your script.

[jeeves245]

Sorry i'm a bit lost.

 

So how do I get rid of the '0000-00-00's?

[fenway]

Did you issue an UPDATE statement after you altered the table?

[jeeves245]

Excuse my complete newbness when it comes to MySQL, but how would I go about doing that?

[fenway]

You can do this from the CLI or from phpMyAdmin... post your current table structure.

[jeeves245]

Name text No

Email text No

Phone text No

Address text No

ForumUsername text No

Paid text Yes NULL

Dispatched text Yes NULL

MembershipNumber int(6) Yes NULL

Expire date Yes NULL

[fenway]

UPDATE yourTableName SET expire = NULL WHERE expire = '0000-00-00'

[jeeves245]

That worked perfectly! Cheers!! 

[jeeves245]

Hmm ok that update query worked.. But when I update row info using my DBMS it still fills in the blank date field with "0000-00-00"..

 

anymore ideas?

[tippy_102]

What does your update statement look like?

[jeeves245]

What does your update statement look like?

 

UPDATE yourTableName SET expire = NULL WHERE expire = '0000-00-00'

 

I used that to get rid of the '0000-00-00's that were already there. But whenever I update a row they return.

[tippy_102]

Can we see the UPDATE or INSERT statement that processes your form?

[jeeves245]

Can we see the UPDATE or INSERT statement that processes your form?

 

Umm i'll just post the body of it:

 

if ($submit) {

  // here if no ID then adding else we're editing

  if ($id) {

    $sql = "UPDATE FBI_Applications SET Name='$Name',Email='$Email',Phone='$Phone',Address='$Address' ,ForumUsername='$ForumUsername' ,Paid='$Paid' ,Dispatched='$Dispatched' ,MembershipNumber='$MembershipNumber' ,Expire='$Expire' WHERE id=$id";

  } else {

    $sql = "INSERT INTO FBI_Applications (Name,Email,Phone,Address,ForumUsername,Paid,Dispatched,MembershipNumber,Expire) VALUES ('$Name','$Email','$Phone','$Address','$ForumUsername','$Paid','$Dispatched','$MembershipNumber','$Expire')";

  }

  // run SQL against the DB

  $result = mysql_query($sql);

  echo "Record updated!<p>";

} elseif ($delete) {

// delete a record

    $sql = "DELETE FROM FBI_Applications WHERE id=$id";	

    $result = mysql_query($sql);

    echo "$sql - Record deleted!<p>";

} else {

  // this part happens if we don't press submit

  if (!$id) {

    // print the list if there is not editing

    $result = mysql_query("SELECT * FROM FBI_Applications",$db);

    while ($myrow = mysql_fetch_array($result)) {

      printf("<a href=\"%s?id=%s\">%s</a> \n", $PHP_SELF, $myrow["id"], $myrow["ForumUsername"]);

  printf("<a href=\"%s?id=%s&delete=yes\">(DELETE)</a><br>", $PHP_SELF, $myrow["id"]);

    }

  }



  ?>

  <P>

  <a href="<?php echo $PHP_SELF?>">Add Record</a>

  <P>

  <form method="post" action="<?php echo $PHP_SELF?>">

  <?php



  if ($id) {

    // editing so select a record

    $sql = "SELECT * FROM FBI_Applications WHERE id=$id";

    $result = mysql_query($sql);

    $myrow = mysql_fetch_array($result);

    $id = $myrow["id"];

    $Name = $myrow["Name"];

    $Email = $myrow["Email"];

    $Phone = $myrow["Phone"];

    $Address = $myrow["Address"];

    $ForumUsername = $myrow["ForumUsername"];

    $Paid = $myrow["Paid"];

    $Dispatched = $myrow["Dispatched"];

$MembershipNumber = $myrow["MembershipNumber"];

$Expire = $myrow["Expire"];

[fenway]

Not exactly what I asked for... :-(

 

What determined the value of $Expired?  You're quoting it, so you'll never manage to get NULL in there.