Jump to content


Photo

How do I input the date automatically into a mysql db


  • Please log in to reply
4 replies to this topic

#1 dragin99

dragin99
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 14 May 2006 - 08:40 PM

I have created a simple customer information form and now I want to add a date field to automatically be inserted into the associated mysql table.
I thought I could do this within the mysql db but the DATE type only displays the 0000-00-00 format.
So I created a hidden field with a value of now() but that doesn't work either.
I know this a simple function but for the life of me I can't see what I am missing.
Can someone help...all I am trying to do is get a date stamp for the day the person filled out the form.
Thanks!

PS didn't see anything for this specifically in the archives.


#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 May 2006 - 08:58 PM

You don't need a hidden field in the code. You do need a database field to accept the date information.

The date right now (on your server) is:
$rightnow = date("Y-m-d");

We'll need to see your query if that isn't working for you.
Legend has it that reading the manual never killed anyone.
My site

#3 dragin99

dragin99
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 14 May 2006 - 09:41 PM

Andyb - thanks for responding!
I don't know why I can't get my head around this but how does the field get updated by just adding what you responded with.
the name of the field in the db is date ....so I added your line at the bottom of the php code section. here is the php and associated html form:

<?php require_once('Connections/phils_db.php'); ?>
<?php
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;
}

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

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "signup_form")) {
$insertSQL = sprintf("INSERT INTO signup_form (fname, lname, address, suite, city, `state`, zip, email, phone, fax) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($HTTP_POST_VARS['fname'], "text"),
GetSQLValueString($HTTP_POST_VARS['lname'], "text"),
GetSQLValueString($HTTP_POST_VARS['address'], "text"),
GetSQLValueString($HTTP_POST_VARS['suite'], "text"),
GetSQLValueString($HTTP_POST_VARS['city'], "text"),
GetSQLValueString($HTTP_POST_VARS['state'], "text"),
GetSQLValueString($HTTP_POST_VARS['zip'], "text"),
GetSQLValueString($HTTP_POST_VARS['email'], "text"),
GetSQLValueString($HTTP_POST_VARS['phone'], "text"),
GetSQLValueString($HTTP_POST_VARS['fax'], "text"));

mysql_select_db($database_phils_db, $phils_db);
$Result1 = mysql_query($insertSQL, $phils_db) or die(mysql_error());

$insertGoTo = "thankyou/thankyou.php";
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $HTTP_SERVER_VARS['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
$date =date("Y-m-d");
?>
<html>
<head>
<title>Phil Hale's how to Flip Properties</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) { //reloads the window if Nav4 resized
if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);

function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_validateForm() { //v4.0
var i,p,q,nm,test,num,min,max,errors='',args=MM_validateForm.arguments;
for (i=0; i<(args.length-2); i+=3) { test=args[i+2]; val=MM_findObj(args[i]);
if (val) { nm=val.name; if ((val=val.value)!="") {
if (test.indexOf('isEmail')!=-1) { p=val.indexOf('@');
if (p<1 || p==(val.length-1)) errors+='- '+nm+' must contain an e-mail address.\n';
} else if (test!='R') { num = parseFloat(val);
if (isNaN(val)) errors+='- '+nm+' must contain a number.\n';
if (test.indexOf('inRange') != -1) { p=test.indexOf(':');
min=test.substring(8,p); max=test.substring(p+1);
if (num<min || max<num) errors+='- '+nm+' must contain a number between '+min+' and '+max+'.\n';
} } } else if (test.charAt(0) == 'R') errors += '- '+nm+' is required.\n'; }
} if (errors) alert('The following error(s) occurred:\n'+errors);
document.MM_returnValue = (errors == '');
}
//-->
</script>
</head>

<body>
<div id="Layer1" style="position:absolute; left:119px; top:12px; width:777; height:263; z-index:1">
<div align="center"> <img src="philhale_home.gif" width="777" height="263"></div>
</div>
<div id="Layer3" style="position:absolute; left:321px; top:331px; width:50; height:600; z-index:3">
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macr...rsion=6,0,29,0" width="50" height="600">
<param name="movie" value="bottom_left.swf">
<param name="quality" value="high">
<embed src="bottom_left.swf" quality="high" pluginspage="http://www.macromedi...getflashplayer" type="application/x-shockwave-flash" width="50" height="600"></embed></object>
</div>
<div id="Layer4" style="position:absolute; left:321px; top:280px; width:577; height:50; z-index:4">
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macr...rsion=6,0,29,0" width="577" height="50">
<param name="movie" value="Bottom_right.swf">
<param name="quality" value="high">
<embed src="Bottom_right.swf" quality="high" pluginspage="http://www.macromedi...getflashplayer" type="application/x-shockwave-flash" width="577" height="50"></embed></object>
</div>
<div id="Layer5" style="position:absolute; left:371px; top:331px; width:527; height:600; z-index:5">
<img name="bottom_right" src="bottom_right.gif" width="527" height="600" border="0" usemap="#m_bottom_right" alt="">
<map name="m_bottom_right">
<area shape="rect" coords="205,572,303,587" href="/privacy/privacy.htm" title="To find out about our privacy policy click here!" alt="To find out about our privacy policy click here!" >
</map>
</div>
<div id="Layer2" style="position:absolute; left:119px; top:280px; width:200px; height:112; z-index:6"><img src="left_column/signup_banner_lft.gif" width="200" height="112"></div>
<div id="Layer6" style="position:absolute; left:119; top:393px; width:200px; height:539px; z-index:7; background-image: url(left_column/signup_bottom_lft.gif); layer-background-image: url(left_column/signup_bottom_lft.gif); border: 1px none #000000;">
<form action="<?php echo $editFormAction; ?>" method="POST" name="signup_form" id="signup_form" onSubmit="MM_validateForm('fname','','R');MM_validateForm('lname','','R');MM_validateForm('email','','RisEmail');return document.MM_returnValue">

<table width="80%" border="0" align="center">
<tr>
<td><font face="Microsoft Sans Serif"><em><strong>First Name <font color="#990000">*</font></strong></em></font></td>
</tr>
<tr>
<td> <input name="fname" type="text" id="fname" size="25"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>Last Name <font color="#990000">*</font></em></strong></font></td>
</tr>
<tr>
<td> <input name="lname" type="text" id="lname" size="25"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>Address</em></strong></font></td>
</tr>
<tr>
<td> <input name="address" type="text" id="address" size="25"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>Apt/Suite</em></strong></font></td>
</tr>
<tr>
<td> <input name="suite" type="text" id="suite" size="10"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>City</em></strong></font></td>
</tr>
<tr>
<td> <input name="city" type="text" id="city" size="25"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>State</em></strong></font></td>
</tr>
<tr>
<td><select name="state" id="state">
<option value="AL">AL</option>
<option value="AK">AK</option>
<option value="AZ">AZ</option>
<option value="AR">AR</option>
<option value="CA">CA</option>
<option value="CO">CO</option>
<option value="DE">DE</option>
<option value="DC">DC</option>
<option value="FL">FL</option>
<option value="GA">GA</option>
<option value="HI">HI</option>
<option value="ID">ID</option>
<option value="IL" selected>IL</option>
<option value="IN">IN</option>
<option value="IA">IA</option>
<option value="KS">KS</option>
<option value="KY">KY</option>
<option value="LA">LA</option>
<option value="ME">ME</option>
<option value="MD">MD</option>
<option value="MA">MA</option>
<option value="MI">MI</option>
<option value="MN">MN</option>
<option value="MS">MS</option>
<option value="MO">MO</option>
<option value="MT">MT</option>
<option value="NE">NE</option>
<option value="NV">NV</option>
<option value="NH">NH</option>
<option value="NJ">NJ</option>
<option value="NM">NM</option>
<option value="NY">NY</option>
<option value="NC">NC</option>
<option value="ND">ND</option>
<option value="OH">OH</option>
<option value="OK">OK</option>
<option value="OR">OR</option>
<option value="PA">PA</option>
<option value="RI">RI</option>
<option value="SC">SC</option>
<option value="SD">SD</option>
<option value="TN">TN</option>
<option value="TX">TX</option>
<option value="UT">UT</option>
<option value="VT">VT</option>
<option value="VA">VA</option>
<option value="WA">WA</option>
<option value="WV">WV</option>
<option value="WI">WI</option>
<option value="WY">WY</option>
</select>
</td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>Zipcode</em></strong></font></td>
</tr>
<tr>
<td> <input name="zip" type="text" id="zip" size="25"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>Email Address <font color="#990000">*</font></em></strong></font></td>
</tr>
<tr>
<td> <input name="email" type="text" id="email" size="25" maxlength="40"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><strong><em>Phone</em></strong></font></td>
</tr>
<tr>
<td> <input name="phone" type="text" id="phone" size="25"></td>
</tr>
<tr>
<td><font face="Microsoft Sans Serif"><em><strong>Fax</strong></em></font></td>
</tr>
<tr>
<td> <input name="fax" type="text" id="fax" size="25"></td>
</tr>
<tr>
<td> <div align="center">
<input type="submit" name="Submit" value="Submit Form">
</div></td>
</tr>
</table>

<input type="hidden" name="MM_insert" value="signup_form">
</form>
</div>
</body>
</html>

#4 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 14 May 2006 - 10:18 PM

$insertSQL = sprintf("INSERT INTO signup_form (fname, lname, address, suite, city, `state`, zip, email, phone, fax) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)";

Ahead of that calculate the date, then modify your insertSQL statement to add date and its value.
Legend has it that reading the manual never killed anyone.
My site

#5 dragin99

dragin99
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 14 May 2006 - 10:47 PM

Not to belay a point but by calculate the date do you mean insert this statement:
$date =date("Y-m-d");

And is would its value then be a "%s"

Sorry but the value is eluding me!

Thanks for your help!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users