DATE field not updating

[Mandakins]

When I add a new record or try to edit a record, my DATE field seems to be updating, but when I pull the data from that field, it's the default 0000-00-00.

I don't get any errors on the insert or update statements (unless I try to feed it unformatted garbage), and the date is there, so why isn't it updating when all the other fields are?

<?php

$MyDate = Date("Y-d-m");

include("../admin_area/connections.inc");

$thesql = "insert into recipes (SubmitterName, SubmitterEmail, RecipeTitle, Ingredients, Instructions, PrepTime, CookTime, Servings, Categories, TheDate, Approval) values ('$SubmitterName', '$SubmitterEmail', '$RecipeTitle', '$Ingredients', '$Instructions', '$Prep $ptime', '$Cook $ctime', '$Servings', '$Categories_list', $MyDate, 'x')";

echo "<p>$thesql</p>";

$sqlresult = mysql_query($thesql,$connection) or die("Could not connect.");

?>

The resulting sql string looks something like this:

insert into recipes (SubmitterName, SubmitterEmail, RecipeTitle, Ingredients, Instructions, PrepTime, CookTime, Servings, Categories, TheDate, Approval) values ('Amanda Huber', '[e-mail]', 'My new recipe submitted on June 23rd', '1 cup this 2 cups that 3 tsp. stuff', 'instructions go here.', '20 minutes', '35 minutes', '4', 'snacks', 2005-23-06 , 'x')

So. No errors, the date is there, but when I pull the record, it's still the default 0000-00-00.

Any idea what's going on? Please tell me I've just got a variable named wrong or something.

[liquorvicar]

i believe you need to input the date in Y-M-D format. your date wouldn't be a valid date in this format and would default to 0000-00-00.

[Aurasia]

You also might try rebuilding your query into something easier to troubleshoot:

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$insertSQL = sprintf("INSERT INTO recipies(SubmitterName, SubmitterEmail, RecipeTitle, Ingredients, Instructions, PrepTime, CookTime, Servings, Categories, TheDate, Approval) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($SubmitterName, "text"),
                       GetSQLValueString($SubmitterEmail, "text"),
                       GetSQLValueString($RecipeTitle, "text"),
                       GetSQLValueString($Ingredients, "text"),
                       GetSQLValueString($Instructions, "text"),
                       GetSQLValueString($Prep $ptime, "text"),
                       GetSQLValueString($Cooktime, "text"),
                       GetSQLValueString($Servings, "text"),
                       GetSQLValueString($Categories_list, "text"),
                       GetSQLValueString($MyDate, "date"),
                       GetSQLValueString($x, "text"));

Ok - maybe that isn't actually clearer, but I've grown used to dreamweaver's many uses. The function above processes the inputs. I'm not sure if that helps, but I hope it does.

• Aurasia

[Mandakins]

Well, entering the date as 2005-06-23 doesn't work either. It still defaults to nothing.

I've also tried something like 2005-6-6 and that doesn't throw any errors, but also defaults to nothing.

If I give it something like "fooblahblah" then it will give me an error. So... why won't it take my date? ANd furthermore, why does it pretend to take it? I don't get it.

[leatherback]

Hi,

If you always need to insert the date of that moment in, you could just set the field's default to now()

J

[Sxooter]

Do me a favor if you will, print out the query after it's generated, and then cut and paste it into a message here.

[Mandakins]

This is exactly what it generates:

insert into recipes (SubmitterName, SubmitterEmail, RecipeTitle, Ingredients, Instructions, PrepTime, CookTime, Servings, Categories, TheDate, Approval) values ('Amanda Huber', '[e-mail]', 'My new recipe submitted on June 23rd', '1 cup this 2 cups that 3 tsp. stuff', 'instructions go here.', '20 minutes', '35 minutes', '4', 'snacks', 2005-23-06 , 'x')

I need to be able to update the date as well, and that is not working either. Same problem.

[Sxooter]

Your date needs single ticks around it.

[Mandakins]

already tried that. no difference.
In fact, I took them out to see if it would help.

[Mandakins]

Wait a minute.... !!!

It was a combination of two things:

no ticks around the date and
entering the date in the wrong format.

So... '2005-06-22' worked.

Problem solved!!!

Thanks everyone!!!!

[Sxooter]

Did you try inserting a correctly formatted date at the same time?

2005-23-06 is not a proper date.

Try inserting '2005-06-23'

[Sxooter]

Jinx! haha. Glad you found it.