Solved

MySql Error

Posted on 2011-03-03
14
925 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:wantabe2
14 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 35026962
Format it like that : YYYY-MM-DD
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35026968
for MySQL, the string for date values has to be in this form:

YYYY-MM-DD

0
 
LVL 15

Author Comment

by:wantabe2
ID: 35027170
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
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 82

Expert Comment

by:leakim971
ID: 35027246
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

0
 
LVL 10

Expert Comment

by:John Claes
ID: 35027259
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

0
 
LVL 15

Author Comment

by:wantabe2
ID: 35027453
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
0
 
LVL 82

Expert Comment

by:leakim971
ID: 35027475
could you post your schema table please ?
0
 
LVL 15

Author Comment

by:wantabe2
ID: 35027534
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
0
 
LVL 15

Author Comment

by:wantabe2
ID: 35027605
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

0
 
LVL 82

Expert Comment

by:leakim971
ID: 35027709
lol 2002/02/31

month end with 28, max is 29 :))
0
 
LVL 82

Expert Comment

by:leakim971
ID: 35027803
<< 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

0
 
LVL 15

Author Comment

by:wantabe2
ID: 35027808
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.
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 35027887
If you have time to read a bit of doc : http://www.ama3.com/anytime/
Go on the instructions section, it's very easy to use, don't be afraid.
0
 
LVL 15

Author Closing Comment

by:wantabe2
ID: 35027947
Thanks! I'll read the link provided.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Complex MySQL Query 2 19
innerHTML 7 20
SQL Count issue 24 16
How to reload page and grab querystring 2 11
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question