asked on

How to script the PHP-MYSQL Date Picker Form Field to update the database?

I am trying to update a date in a database with a date calendar picker. Before trying the script, I was able to input 20100401 into the text box and it updated the db.

Not proficient in mysql, I am having a problem with the javascript within the form field, calling the name of the form which is a php variable.

I don't think this is right:
<a href="javascript:show_calendar('document.<?php echo $_SERVER['PHP_SELF']; ?>.appt_date', <?php echo $_SERVER['PHP_SELF']; ?>.appt_date.value);">

Pertinent part below with the full page codes, ts_picker.js, update_user_test.php and masterlist at the bottom:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
<head>
<script language="JavaScript" src="ts_picker.js">
</head>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<tr valign="baseline">
<td nowrap="nowrap" align="right">Appt. Date:</td>
<td><input type="Text" name="appt_date" value="">
<a href="javascript:show_calendar('document.<?php echo $_SERVER['PHP_SELF']; ?>.appt_date', <?php echo $_SERVER['PHP_SELF']; ?>.appt_date.value);">
<img src="cal.gif" width="16" height="16" border="0"></a>
</tr>
<td><input type="submit" value="Update Record" />
   <a href="masterleadlist_test.php">Cancel and return to Master Lead List</a></td>
</tr>
</table>
<input type="hidden" name="MM_update" value="form1" />
<input type="hidden" name="cust_id" value="<?php echo $row_getUser['cust_id']; ?>" />
</form>

HERE IS THE PERTINENT ts_picker.js FILE THAT I CHANGED THE FUNCTION OF THE PORTION OF THE DATE FORMAT COMMENTING OUT THE ORIGINAL DATE FORMAT:

function dt2dtstr (dt_datetime) {
return (new String (
dt_datetime.getFullYear()+"-"+(dt_datetime.getMonth()+1)+"-"+dt_datetime.getDate()+" "));
// dt_datetime.getDate()+"-"+(dt_datetime.getMonth()+1)+"-"+dt_datetime.getFullYear()+" "));
}

THE FULL ts_picker.js FILE
// Title: Timestamp picker
// Description: See the demo at url
// URL: http://us.geocities.com/tspicker/
// Script featured on: http://javascriptkit.com/script/script2/timestamp.shtml
// Version: 1.0
// Date: 12-05-2001 (mm-dd-yyyy)
// Author: Denis Gritcyuk <denis@softcomplex.com>; <tspicker@yahoo.com>
// Notes: Permission given to use this script in any kind of applications if
// header lines are left unchanged. Feel free to contact the author
// for feature requests and/or donations

function show_calendar(str_target, str_datetime) {
      var arr_months = ["January", "February", "March", "April", "May", "June",
            "July", "August", "September", "October", "November", "December"];
      var week_days = ["Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"];
      var n_weekstart = 1; // day week starts from (normally 0 or 1)

      var dt_datetime = (str_datetime == null || str_datetime =="" ? new Date() : str2dt(str_datetime));
      var dt_prev_month = new Date(dt_datetime);
      dt_prev_month.setMonth(dt_datetime.getMonth()-1);
      var dt_next_month = new Date(dt_datetime);
      dt_next_month.setMonth(dt_datetime.getMonth()+1);
      var dt_firstday = new Date(dt_datetime);
      dt_firstday.setDate(1);
      dt_firstday.setDate(1-(7+dt_firstday.getDay()-n_weekstart)%7);
      var dt_lastday = new Date(dt_next_month);
      dt_lastday.setDate(0);

      // html generation (feel free to tune it for your particular application)
      // print calendar header
      var str_buffer = new String (
            "<html>\n"+
            "<head>\n"+
            "      <title>Calendar</title>\n"+
            "</head>\n"+
            "<body bgcolor=\"White\">\n"+
            "<table class=\"clsOTable\" cellspacing=\"0\" border=\"0\" width=\"100%\">\n"+
            "<tr><td bgcolor=\"#4682B4\">\n"+
            "<table cellspacing=\"1\" cellpadding=\"3\" border=\"0\" width=\"100%\">\n"+
            "<tr>\n      <td bgcolor=\"#4682B4\"><a href=\"javascript:window.opener.show_calendar('"+
            str_target+"', '"+ dt2dtstr(dt_prev_month)+"'+document.cal.time.value);\">"+
            "<img src=\"prev.gif\" width=\"16\" height=\"16\" border=\"0\""+
            " alt=\"previous month\"></a></td>\n"+
            "      <td bgcolor=\"#4682B4\" colspan=\"5\">"+
            "<font color=\"white\" face=\"tahoma, verdana\" size=\"2\">"
            +arr_months[dt_datetime.getMonth()]+" "+dt_datetime.getFullYear()+"</font></td>\n"+
            "      <td bgcolor=\"#4682B4\" align=\"right\"><a href=\"javascript:window.opener.show_calendar('"
            +str_target+"', '"+dt2dtstr(dt_next_month)+"'+document.cal.time.value);\">"+
            "<img src=\"next.gif\" width=\"16\" height=\"16\" border=\"0\""+
            " alt=\"next month\"></a></td>\n</tr>\n"
      );

      var dt_current_day = new Date(dt_firstday);
      // print weekdays titles
      str_buffer += "<tr>\n";
      for (var n=0; n<7; n++)
            str_buffer += "      <td bgcolor=\"#87CEFA\">"+
            "<font color=\"white\" face=\"tahoma, verdana\" size=\"2\">"+
            week_days[(n_weekstart+n)%7]+"</font></td>\n";
      // print calendar table
      str_buffer += "</tr>\n";
      while (dt_current_day.getMonth() == dt_datetime.getMonth() ||
            dt_current_day.getMonth() == dt_firstday.getMonth()) {
            // print row heder
            str_buffer += "<tr>\n";
            for (var n_current_wday=0; n_current_wday<7; n_current_wday++) {
                        if (dt_current_day.getDate() == dt_datetime.getDate() &&
                              dt_current_day.getMonth() == dt_datetime.getMonth())
                              // print current date
                              str_buffer += "      <td bgcolor=\"#FFB6C1\" align=\"right\">";
                        else if (dt_current_day.getDay() == 0 || dt_current_day.getDay() == 6)
                              // weekend days
                              str_buffer += "      <td bgcolor=\"#DBEAF5\" align=\"right\">";
                        else
                              // print working days of current month
                              str_buffer += "      <td bgcolor=\"white\" align=\"right\">";

                        if (dt_current_day.getMonth() == dt_datetime.getMonth())
                              // print days of current month
                              str_buffer += "<a href=\"javascript:window.opener."+str_target+
                              ".value='"+dt2dtstr(dt_current_day)+"'+document.cal.time.value; window.close();\">"+
                              "<font color=\"black\" face=\"tahoma, verdana\" size=\"2\">";
                        else
                              // print days of other months
                              str_buffer += "<a href=\"javascript:window.opener."+str_target+
                              ".value='"+dt2dtstr(dt_current_day)+"'+document.cal.time.value; window.close();\">"+
                              "<font color=\"gray\" face=\"tahoma, verdana\" size=\"2\">";
                        str_buffer += dt_current_day.getDate()+"</font></a></td>\n";
                        dt_current_day.setDate(dt_current_day.getDate()+1);
            }
            // print row footer
            str_buffer += "</tr>\n";
      }
      // print calendar footer
      str_buffer +=
            "<form name=\"cal\">\n<tr><td colspan=\"7\" bgcolor=\"#87CEFA\">"+
            "<font color=\"White\" face=\"tahoma, verdana\" size=\"2\">"+
            "Time: <input type=\"text\" name=\"time\" value=\""+dt2tmstr(dt_datetime)+
            "\" size=\"8\" maxlength=\"8\"></font></td></tr>\n</form>\n" +
            "</table>\n" +
            "</tr>\n</td>\n</table>\n" +
            "</body>\n" +
            "</html>\n";

      var vWinCal = window.open("", "Calendar",
            "width=200,height=250,status=no,resizable=yes,top=200,left=200");
      vWinCal.opener = self;
      var calc_doc = vWinCal.document;
      calc_doc.write (str_buffer);
      calc_doc.close();
}
// datetime parsing and formatting routimes. modify them if you wish other datetime format
function str2dt (str_datetime) {
      var re_date = /^(\d+)\-(\d+)\-(\d+)\s+(\d+)\:(\d+)\:(\d+)$/;
      if (!re_date.exec(str_datetime))
            return alert("Invalid Datetime format: "+ str_datetime);
      return (new Date (RegExp.$3, RegExp.$2-1, RegExp.$1, RegExp.$4, RegExp.$5, RegExp.$6));
}
function dt2dtstr (dt_datetime) {
      return (new String (
                  dt_datetime.getFullYear()+"-"+(dt_datetime.getMonth()+1)+"-"+dt_datetime.getDate()+" "));
                  // dt_datetime.getDate()+"-"+(dt_datetime.getMonth()+1)+"-"+dt_datetime.getFullYear()+" "));

}
function dt2tmstr (dt_datetime) {
      return (new String (
                  dt_datetime.getHours()+":"+dt_datetime.getMinutes()+":"+dt_datetime.getSeconds()));
}

THE update_user_test.php PAGE>>>>>>>>>>>>>>>>>>>

<?php require_once('Connections/connAdmin.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$updateSQL = sprintf("UPDATE MasterLeadList SET inq_date=%s, cust_name=%s, vehicle=%s, source=%s, bdc=%s, salesperson=%s, appt_date=%s, appt_status=%s, task_date=%s, cust_notes=%s, cust_status=%s WHERE cust_id=%s",
GetSQLValueString($_POST['inq_date'], "date"),
GetSQLValueString($_POST['cust_name'], "text"),
GetSQLValueString($_POST['vehicle'], "text"),
GetSQLValueString($_POST['source'], "text"),
GetSQLValueString($_POST['bdc'], "text"),
GetSQLValueString($_POST['salesperson'], "text"),
GetSQLValueString($_POST['appt_date'], "date"),
GetSQLValueString($_POST['appt_status'], "text"),
GetSQLValueString($_POST['task_date'], "date"),
GetSQLValueString($_POST['cust_notes'], "text"),
GetSQLValueString($_POST['cust_status'], "text"),
GetSQLValueString($_POST['cust_id'], "int"));

mysql_select_db($database_connAdmin, $connAdmin);
$Result1 = mysql_query($updateSQL, $connAdmin) or die(mysql_error());

$updateGoTo = "masterleadlist_test.php";
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}

$colname_getUser = "-1";
if (isset($_GET['cust_id'])) {
$colname_getUser = $_GET['cust_id'];
}
mysql_select_db($database_connAdmin, $connAdmin);
$query_getUser = sprintf("SELECT cust_id, inq_date, cust_name, vehicle, source, bdc, salesperson, appt_date, appt_status, task_date, cust_notes, cust_status FROM MasterLeadList WHERE cust_id = %s", GetSQLValueString($colname_getUser, "int"));
$getUser = mysql_query($query_getUser, $connAdmin) or die(mysql_error());
$row_getUser = mysql_fetch_assoc($getUser);
$totalRows_getUser = mysql_num_rows($getUser);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Update Lead Record</title>
<script language="JavaScript" src="ts_picker.js">
</script>
</head>

<body>
<h3 align="center">Update Lead Record</h3>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td width="103" align="right" nowrap="nowrap">Inquiry Date:</td>
<td width="589"><input type="text" name="inq_date" value="<?php echo htmlentities($row_getUser['inq_date'], ENT_COMPAT, 'utf-8'); ?>" size="32" />
  Date as Year-Month-Day. ie. 2010-01-31</td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Customer Name:</td>
<td><input type="text" name="cust_name" value="<?php echo htmlentities($row_getUser['cust_name'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Vehicle:</td>
<td><input type="text" name="vehicle" value="<?php echo htmlentities($row_getUser['vehicle'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Source:</td>
<td><input type="text" name="source" value="<?php echo htmlentities($row_getUser['source'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">BDC Rep:</td>
<td><input type="text" name="bdc" value="<?php echo htmlentities($row_getUser['bdc'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Salesperson:</td>
<td><input type="text" name="salesperson" value="<?php echo htmlentities($row_getUser['salesperson'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Appt. Date:</td>
<td><input type="Text" name="appt_date" value="">
                  <a href="javascript:show_calendar('document.<?php echo $_SERVER['PHP_SELF']; ?>.appt_date', <?php echo $_SERVER['PHP_SELF']; ?>.appt_date.value);">
<img src="cal.gif" width="16" height="16" border="0"></a>
</tr>

<tr valign="baseline">
<td nowrap="nowrap" align="right">Appt. Status:</td>
<td><input type="text" name="appt_status" value="<?php echo htmlentities($row_getUser['appt_status'], ENT_COMPAT, 'utf-8'); ?>" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Task Date:</td>
<td><input type="text" name="task_date" value="<?php echo htmlentities($row_getUser['task_date'], ENT_COMPAT, 'utf-8'); ?>" size="32" />
  Date as Year-Month-Day. ie. 2010-01-31</td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Customer Notes:</td>
<td><input type="text" name="cust_notes" value="<?php echo htmlentities($row_getUser['cust_notes'], ENT_COMPAT, 'utf-8'); ?>" size="80" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right">Customer Status:</td>
<td><input type="text" name="cust_status" value="<?php echo htmlentities($row_getUser['cust_status'], ENT_COMPAT, 'utf-8'); ?>" size="32" />
 sld (sold), act (active), f/u (follow-up), dea (deactivate)</td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Update Record" />
   <a href="masterleadlist_test.php">Cancel and return to Master Lead List</a></td>
</tr>
</table>
<input type="hidden" name="MM_update" value="form1" />
<input type="hidden" name="cust_id" value="<?php echo $row_getUser['cust_id']; ?>" />
</form>
<p> </p>
</body>
</html>
<?php
mysql_free_result($getUser);
?>

THE masterlist_test.php PAGE:
<?php require_once('Connections/connAdmin.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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;
}
}

mysql_select_db($database_connAdmin, $connAdmin);
$query_MasterLeadList = "SELECT cust_id, inq_date, cust_name, vehicle, source, bdc, salesperson, appt_date, appt_status, task_date, cust_notes, cust_status FROM MasterLeadList ORDER BY inq_date DESC";
$MasterLeadList = mysql_query($query_MasterLeadList, $connAdmin) or die(mysql_error());
$row_MasterLeadList = mysql_fetch_assoc($MasterLeadList);
$totalRows_MasterLeadList = mysql_num_rows($MasterLeadList);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Master Lead List</title>
<link href="cssStyleSheet.css" rel="stylesheet" type="text/css" />
<script src="sorttable.js"></script>
</head>

<body>
<div align="center"><strong style="font-family: Arial, Helvetica, sans-serif; font-size: large;">Master Lead List - Carl Black of Orlando - <span style="font-size: small">Click on any column to sort that column's data. </span></strong> </div>
<table class="sortable" width="600" border="0" cellpadding="1" cellspacing="2" id="arialFont">
<tr>
<th scope="col">Inquiry<br />
Date</th>
<th scope="col">Customer<br />
Name</th>
<th scope="col">Vehicle<br />
Inquiry</th>
<th scope="col">Lead<br />
Source</th>
<th scope="col">BDC<br />
Rep</th>
<th scope="col">Sales<br />
Person</th>
<th scope="col">Appt<br />
Date</th>
<th scope="col">Appt<br />
Status</th>
<th scope="col">Task<br />
Date</th>
<th scope="col">Cust.<br />
Notes</th>
<th scope="col">Cust.<br />
Status</th>
</tr>

<?php do { ?>

<tr>
<td nowrap="nowrap"><div align="center"><?php echo $row_MasterLeadList['inq_date']; ?></div></td>
<td nowrap="nowrap"><div align="left"><a href="update_user_test.php?cust_id=<?php echo $row_MasterLeadList['cust_id']; ?>"><?php echo $row_MasterLeadList['cust_name']; ?></a></div></td>
<td nowrap="nowrap"><div align="left"><?php echo $row_MasterLeadList['vehicle']; ?></div></td>
<td nowrap="nowrap"><div align="left"><?php echo $row_MasterLeadList['source']; ?></div></td>
<td nowrap="nowrap"><div align="left"><?php echo $row_MasterLeadList['bdc']; ?></div></td>
<td nowrap="nowrap"><div align="left"><?php echo $row_MasterLeadList['salesperson']; ?></div></td>
<td nowrap="nowrap"><div align="center"><?php echo $row_MasterLeadList['appt_date']; ?></div></td>
<td nowrap="nowrap"><div align="center"><?php echo $row_MasterLeadList['appt_status']; ?></div></td>
<td nowrap="nowrap"><div align="center"><?php echo $row_MasterLeadList['task_date']; ?></div></td>
<td nowrap="nowrap"><div align="left"><?php echo $row_MasterLeadList['cust_notes']; ?></div></td>
<td nowrap="nowrap"><div align="center"><?php echo $row_MasterLeadList['cust_status']; ?></div></td>
</tr>
<?php } while ($row_MasterLeadList = mysql_fetch_assoc($MasterLeadList)); ?>
</table>
<p> </p>
</body>
</html>
<?php
mysql_free_result($MasterLeadList);
?>

ASKER CERTIFIED SOLUTION

scottflodin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

javajamminman

ASKER

thanks scottflodin, can't believe it. surfed the internet and many an example showed different. works absolutely perfect!

scottflodin

Fantastic! I'm glad I could help :-)