DataAssist Insert Record error...
Hi WA Gang:
I'm sure I'm doing something wrong here but it escapes me.
I've used DataAssist to create a series of pages that allow team members to request updates to lobby monitors in our offices. The form includes two text fields that collect start and stop date information. I've inserted a jQuery UI DatePicker widget to pass date information into those elements. By default the format is mm/dd/yyyy which needs to be reformatted to Unix timestamp for insertion into the database. So I added the following code to the Insert Record Server Behavior:
<?php echo ((isset($_POST["monitorGoLiveDate"]))?GetSQLValueString(date('Y-m-d', strtotime($_POST["monitorGoLiveDate"])), 'date'):""); ?>
and
<?php echo ((isset($_POST["monitorEndDate"]))?GetSQLValueString(date('Y-m-d', ($_POST["monitorEndDate"])), 'date'):""); ?>
to ensure that the dates are formatted when inserted into the database, but when I submit the form, the resulting error message appears:
Incorrect datetime value: ''2011-11-09'' for column 'monitorGoLiveDate' at row 1
Perhaps the formatted date is being passed as a string, or perhaps I need to include the hh:mm:ss formatting.
Here is the code on the page:
<?php
// WA Application Builder Insert
if (isset($_POST["Insert_x"])) // Trigger
{
$WA_connection = $ucpntx;
$WA_table = "lobbymonitor";
$WA_sessionName = "WADA_Insert_lobbymonitor";
$WA_redirectURL = "lobbyMonitor_Detail.php";
if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
$WA_keepQueryString = true;
$WA_indexField = "idlobbyMonitor";
$WA_fieldNamesStr = "monitorRequestor|monitorRequestorID|monitorRequestDate|monitorGoLiveDate|monitorEndDate|monitorText|monitorImage|monitorRequestApproval|monitorLocation|monitorLocationID";
$WA_fieldValuesStr = "".((isset($_POST["monitorRequestor"]))?$_POST["monitorRequestor"]:"") ."" . "|" . "".((isset($_POST["monitorRequestorID"]))?$_POST["monitorRequestorID"]:"") ."" . "|" . "".((isset($_POST["monitorRequestDate"]))?$_POST["monitorRequestDate"]:"") ."" . "|" . "".((isset($_POST["monitorGoLiveDate"]))?GetSQLValueString(date('Y-m-d', strtotime($_POST["monitorGoLiveDate"])), 'date'):"") ."" . "|" . "".((isset($_POST["monitorEndDate"]))?GetSQLValueString(date('Y-m-d', strtotime($_POST["monitorEndDate"])), 'date'):"") ."" . "|" . "".((isset($_POST["monitorText"]))?$_POST["monitorText"]:"") ."" . "|" . "".((isset($_POST["monitorImage"]))?$_POST["monitorImage"]:"") ."" . "|" . "".((isset($_POST["monitorRequestApproval"]))?$_POST["monitorRequestApproval"]:"") ."" . "|" . "".((isset($_POST["monitorLocation"]))?$_POST["monitorLocation"]:"") ."" . "|" . "".((isset($_POST["monitorLocationID"]))?$_POST["monitorLocationID"]:"") ."";
$WA_columnTypesStr = "',none,''|none,none,NULL|',none,NULL|',none,NULL|',none,NULL|',none,''|',none,''|',none,''|',none,''|none,none,NULL";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode("|", $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);
$WA_connectionDB = $database_ucpntx;
mysql_select_db($WA_connectionDB, $WA_connection);
if (!session_id()) session_start();
$insertParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WA_Sql = "INSERT INTO `" . $WA_table . "` (" . $insertParamsObj->WA_tableValues . ") VALUES (" . $insertParamsObj->WA_dbValues . ")";
$MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error());
$_SESSION[$WA_sessionName] = mysql_insert_id();
if ($WA_redirectURL != "") {
if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
$WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
}
header("Location: ".$WA_redirectURL);
}
}
?>
Thanks for your help!
enthusiastically,
mh
t