Solved

MySql Error

Posted on 2011-03-03
14
919 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now