asked on

MySql Error

When I enter the data into my form then submit it, it is supposed to be inserted into my MySql database but I get the following error:

"Unable to save data to database: Incorrect date value: '3-31-2001' for column 'sent_date' at row 1"

The sent_date field is the first date field the user types in. The TYPE for this field in the MySql database is set to DATE. Any ideas what is going on? I think the problem is in my javascript but can't find the problem...

<script type="text/javascript">
var valid;

function dcheck(form) {
var a = form.assgn_date.value;
var s = form.sent_date.value;
var i = form.interv_date.value;
var dr = form.due_rev.value
var su = form.due_suspo
var clk = form.due_clerk
var att = form.due_attny
var jdg = form.due_judge
var assn  = new Date(a);
var sent = new Date(s);
var intv = new Date(i);
var due_rev = new Date(dr);
var due_suspo = new Date(su);
var due_clerk = new Date(clk);
var due_attny = new Date(att);
var due_judge = new Date(jdg);


if (isNaN(sent)) {
sent = new Date(assn.getFullYear(),assn.getMonth(),assn.getDate()-7);
}
if (isNaN(due_rev)) {
due_rev = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-42);
}
if (isNaN(due_suspo)) {
due_suspo = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-40);
}
if (isNaN(due_clerk)) {
due_clerk = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-38);
}
if (isNaN(due_attny)) {
due_attny = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-36);
}
if (isNaN(due_judge)) {
due_judge = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-7);
}

form.sent_date.value = (sent.getMonth()+1) + "-" + sent.getDate() + "-" + sent.getFullYear();
form.due_rev.value = (due_rev.getMonth()+1) + "-" + due_rev.getDate() + "-" + due_rev.getFullYear();
form.due_suspo.value = (due_suspo.getMonth()+1) + "-" + due_suspo.getDate() + "-" + due_suspo.getFullYear();
form.due_clerk.value = (due_clerk.getMonth()+1) + "-" + due_clerk.getDate() + "-" + due_clerk.getFullYear();
form.due_attny.value = (due_attny.getMonth()+1) + "-" + due_attny.getDate() + "-" + due_attny.getFullYear();
form.due_judge.value = (due_judge.getMonth()+1) + "-" + due_judge.getDate() + "-" + due_judge.getFullYear();
return true;
}





</script>

Open in new window

leakim971

Format it like that : YYYY-MM-DD

Guy Hengel [angelIII / a3]

for MySQL, the string for date values has to be in this form:

YYYY-MM-DD

wantabe2

ASKER

Thanks, I think I'm one step closer now with your help! I changed the code a little to what I have attached & I am getting this error:

<script type="text/javascript">
var valid;

function dcheck(form) {
var a = form.assgn_date.value;
var s = form.sent_date.value;
var i = form.interv_date.value;
var dr = form.due_rev.value
var su = form.due_suspo
var clk = form.due_clerk
var att = form.due_attny
var jdg = form.due_judge
var assn  = new Date(a);
var sent = new Date(s);
var intv = new Date(i);
var due_rev = new Date(dr);
var due_suspo = new Date(su);
var due_clerk = new Date(clk);
var due_attny = new Date(att);
var due_judge = new Date(jdg);


if (isNaN(sent)) {
sent = new Date(assn.getFullYear(),assn.getMonth(),assn.getDate()-7);
}
if (isNaN(due_rev)) {
due_rev = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-42);
}
if (isNaN(due_suspo)) {
due_suspo = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-40);
}
if (isNaN(due_clerk)) {
due_clerk = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-38);
}
if (isNaN(due_attny)) {
due_attny = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-36);
}
if (isNaN(due_judge)) {
due_judge = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-7);
}

form.sent_date.value = (sent.getFullYear()+1) + "-" + sent.getMonth() + "-" + sent.getDate();
form.due_rev.value = (due_rev.getFullYear()+1) + "-" + due_rev.getMonth() + "-" + due_rev.getDate();
form.due_suspo.value = (due_suspo.getFullYear()+1) + "-" + due_suspo.getMonth() + "-" + due_suspo.getDate();
form.due_clerk.value = (due_clerk.getFullYear()+1) + "-" + due_clerk.getMonth() + "-" + due_clerk.getDate();
form.due_attny.value = (due_attny.getFullYear()+1) + "-" + due_attny.getMonth() + "-" + due_attny.getDate();
form.due_judge.value = (due_judge.getFullYear()+1) + "-" + due_judge.getMonth() + "-" + due_judge.getDate();
return true;
}


</script>

Open in new window

ccc.JPG

leakim971

You may use :

function d2(v) { return (v<10)?("0"+v):v; }

Open in new window

So for example line 42 :

form.sent_date.value = (sent.getFullYear()+1) + "-" + d2(sent.getMonth()) + "-" + d2(sent.getDate());

Open in new window

John Claes

2002-1-19 is not in the format YYYY-MM-DD
that is in the format YYYY-M-D

the getMonth returns a 1 instead of 01
and the getDate returns als 1 instead of 01

wantabe2

ASKER

I just changed my code to what leakim971 recomended & I get this error now...I also put the code in again to show where I inserted the new function... it looks like it is inthe correct YYY-MM-DD format now but it still does not insert into the database. thanks again for your help on this!

**as you can see i'm new to programming** :)

<script type="text/javascript">
var valid;

function d2(v) { return (v<10)?("0"+v):v; }


function dcheck(form) {
var a = form.assgn_date.value;
var s = form.sent_date.value;
var i = form.interv_date.value;
var dr = form.due_rev.value
var su = form.due_suspo
var clk = form.due_clerk
var att = form.due_attny
var jdg = form.due_judge
var assn  = new Date(a);
var sent = new Date(s);
var intv = new Date(i);
var due_rev = new Date(dr);
var due_suspo = new Date(su);
var due_clerk = new Date(clk);
var due_attny = new Date(att);
var due_judge = new Date(jdg);


if (isNaN(sent)) {
sent = new Date(assn.getFullYear(),assn.getMonth(),assn.getDate()-7);
}
if (isNaN(due_rev)) {
due_rev = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-42);
}
if (isNaN(due_suspo)) {
due_suspo = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-40);
}
if (isNaN(due_clerk)) {
due_clerk = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-38);
}
if (isNaN(due_attny)) {
due_attny = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-36);
}
if (isNaN(due_judge)) {
due_judge = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-7);
}

form.sent_date.value = (sent.getFullYear()+1) + "-" + d2(sent.getMonth()) + "-" + d2(sent.getDate());
form.due_rev.value = (due_rev.getFullYear()+1) + "-" + d2(due_rev.getMonth()) + "-" + d2(due_rev.getDate());
form.due_suspo.value = (due_suspo.getFullYear()+1) + "-" + d2(due_suspo.getMonth()) + "-" + d2(due_suspo.getDate());
form.due_clerk.value = (due_clerk.getFullYear()+1) + "-" + d2(due_clerk.getMonth()) + "-" + d2(due_clerk.getDate());
form.due_attny.value = (due_attny.getFullYear()+1) + "-" + d2(due_attny.getMonth()) + "-" + d2(due_attny.getDate());
form.due_judge.value = (due_judge.getFullYear()+1) + "-" + d2(due_judge.getMonth()) + "-" + d2(due_judge.getDate());
return true;
}


</script>

Open in new window

qqq.JPG

leakim971

could you post your schema table please ?

wantabe2

ASKER

Yes, I can change the type for the date fields to text in the mysql table & it will insert the date. If I change it to date i get those errors. I didn't know if you needed to know that or not. I've attached the table.
mysqltable.JPG

wantabe2

ASKER

Here is my full code as well

<?php
#	Page: add.php
##########################

#	Report all errors
##########################
ini_set('display_errors',1);
error_reporting(E_ALL);

if( isset($_POST) && !empty($_POST) )
{
     #	db connection settings
     ##########################
     
     #	server IP
     $host	= "localhost";
     #	sql user
     $user	= "username";
     #	sql pw
     $pw	= "mypassword";
     #	sql database
     $db	= "psrflow";
     
     
     #	connect to db server
     ##########################
     $conn = mysql_connect( $host, $user, $pw )
     or die( "Error! Unable to connect to database server: <br/>" . mysql_error() );
     #	connect to db
     ##########################
     $rs = mysql_select_db( $db, $conn )
     or die( "Error! Unable to connect to database:  <br/>" . mysql_error() );
     
     
     #	get submitted values & escape the text string
	foreach($_POST as $key=>$value)
	{
		${$key}=mysql_real_escape_string($value);
	}

     $strSQL = "INSERT INTO psrinfo
     	( offender_fname, offender_lname, location, docket, officer, status, sent_date, assgn_date, interv_date, due_rev, due_suspo, due_clerk, due_attny, due_judge )
     	VALUES
     	( '" . $offender_fname . "', '" . $offender_lname . "', '" . $location . "', '" .$docket . "', '" .$officer ."', '" .$status ."', '".$sent_date ."', '".$assgn_date ."', '".$interv_date ."', '".$due_rev ."', '".$due_suspo ."', '".$due_clerk ."', '".$due_attny ."', '".$due_judge ."')";
     
     #	execute db insert
     ##########################
     if (!mysql_query( $strSQL, $conn )){
     	echo( "Unable to save data to database: <br/>" . mysql_error() . "<br/>" . $strSQL . "</span><br/>" );
     }
     else{
     	header( "Location: index.html" );
		exit;
     }
}
?>
<html>

<head>
<title> Update PSR Database </title>



<script type="text/javascript">
var valid;

function d2(v) { return (v<10)?("0"+v):v; }


function dcheck(form) {
var a = form.assgn_date.value;
var s = form.sent_date.value;
var i = form.interv_date.value;
var dr = form.due_rev.value
var su = form.due_suspo
var clk = form.due_clerk
var att = form.due_attny
var jdg = form.due_judge
var assn  = new Date(a);
var sent = new Date(s);
var intv = new Date(i);
var due_rev = new Date(dr);
var due_suspo = new Date(su);
var due_clerk = new Date(clk);
var due_attny = new Date(att);
var due_judge = new Date(jdg);


if (isNaN(sent)) {
sent = new Date(assn.getFullYear(),assn.getMonth(),assn.getDate()-7);
}
if (isNaN(due_rev)) {
due_rev = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-42);
}
if (isNaN(due_suspo)) {
due_suspo = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-40);
}
if (isNaN(due_clerk)) {
due_clerk = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-38);
}
if (isNaN(due_attny)) {
due_attny = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-36);
}
if (isNaN(due_judge)) {
due_judge = new Date(sent.getFullYear(),sent.getMonth(),sent.getDate()-7);
}

form.sent_date.value = (sent.getFullYear()+1) + "-" + d2(sent.getMonth()) + "-" + d2(sent.getDate());
form.due_rev.value = (due_rev.getFullYear()+1) + "-" + d2(due_rev.getMonth()) + "-" + d2(due_rev.getDate());
form.due_suspo.value = (due_suspo.getFullYear()+1) + "-" + d2(due_suspo.getMonth()) + "-" + d2(due_suspo.getDate());
form.due_clerk.value = (due_clerk.getFullYear()+1) + "-" + d2(due_clerk.getMonth()) + "-" + d2(due_clerk.getDate());
form.due_attny.value = (due_attny.getFullYear()+1) + "-" + d2(due_attny.getMonth()) + "-" + d2(due_attny.getDate());
form.due_judge.value = (due_judge.getFullYear()+1) + "-" + d2(due_judge.getMonth()) + "-" + d2(due_judge.getDate());
return true;
}


</script>



</head>
<body>
<form method="post" action="add.php">

<b>Docket No:</b> <br />
<input type="text" name="docket" size="30" /><br />

<b>First Name:</b> <br />
<input type="text" name="offender_fname" size="30" /><br />

<b>Last Name:</b> <br />
<input type="text" name="offender_lname" size="30" /><br />

<b>Status:</b> <br />
<input type="text" name="status" size="30" /><br />

<b>Location: </b><br />
<select name="location" /> <br />
<option value=""></option> 
<option value="OfficeA">OfficeA</option> 
</select>
</br>

<b>Officer:<br> </b> 
<select name="officer" /><br />
<option value=""></option> 
<option value="employee1">employee1</option>
</select> 
</br> 

<b>Sentence Date:</b> <br />
<input type="text" name="sent_date" size="30" /><br />

<b>Assign Date:</b> <br />
<input type="text" name="assgn_date" size="30" /><br />

<b>Interview Date:</b> <br />
<input type="text" name="interv_date" size="30" /><br />

<b>Due to Reviewer:</b> <br />
<input type="text" name="due_rev" size="30" /><br />

<b>Due to SUSPO:</b> <br />
<input type="text" name="due_suspo" size="30" /><br />

<b>Due to Clerk:</b> <br />
<input type="text" name="due_clerk" size="30" /><br />

<b>Due to Attorney:</b> <br />
<input type="text" name="due_attny" size="30" /><br />

<b>Due to Judge:</b> <br />
<input type="text" name="due_judge" size="30" /><br />

<br>

<input type="button" value="Calculate" onclick="return dcheck(this.form);">	
<input type="submit" value="Submit" />


</form>
</body>
</html>

Open in new window

leakim971

lol 2002/02/31

month end with 28, max is 29 :))

leakim971

<< month end with 28, max is 29 >>
The last day of the february month ends with 28 or 29

to fix empty date, you may use something like :

     $strSQL = "INSERT INTO psrinfo
     	( offender_fname, offender_lname, location, docket, officer, status, sent_date, assgn_date, interv_date, due_rev, due_suspo, due_clerk, due_attny, due_judge )
     	VALUES
     	( '" . $offender_fname . "', '" . $offender_lname . "', '" . $location . "', '" .$docket . "', '" .$officer ."', '" .$status ."', " . (strlen($assgn_date)>0)?"'$assgn_date'":"null" . ", '".$interv_date ."', '".$due_rev ."', '".$due_suspo ."', '".$due_clerk ."', '".$due_attny ."', '".$due_judge ."')";

Open in new window

wantabe2

ASKER

OMG! The code is smarter than I am! That goes to show you how much I know about programming. Is there any way to put some type of code in this existing code to use only calendar days? I did another test & it errors out on 2005-06-31 also. I'm having issues with those javascript calculations.

ASKER CERTIFIED SOLUTION

leakim971

membership

This solution is only available to members.

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

Start Free Trial

wantabe2

ASKER

Thanks! I'll read the link provided.