Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySql Error

Posted on 2011-03-03
14
Medium Priority
?
935 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 82

Expert Comment

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

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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