javascript validation on textboxt for a column and todays date before insert into sql

Question

I have an issue where I want it to check if a user submitted a data with a dept key like dept Outbound would be key O and today's date so that the next day the user would be allowed to enter the data again. I am not sure how this would be done.

Here is the create form

<head>
<meta content="en-us" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Create Form</title>
<script type="text/javascript" src="Javascript/jquery-1.11.1.min.js"></script>
<script type="text/javascript" src="Javascript/autocompleteAreaCode.js"></script>
<script type="text/javascript" src="Javascript/autocompletedeptkey.js"></script>
<script type="text/javascript" src="Javascript/validator.js"></script>
<link href="css/default.css" rel="stylesheet" type="text/css" >




</head>

<body style="background-image: url('images/background.jpg')">

<form  action = "insert_process.php" onsubmit="return validateForm()" name="form" id="form"  method ="post" class="style1" style="width: 1125px; height: 800px; margin-top: 30px; margin-left:0px;">

<table align="center" id="tfhover" class="tftable" border="1">
<br><br>
<tr>
<td colspan="7"><h1 align="center">Lighting Schedule Form</h1></td>
</tr>

<tr>
<th>Department</th><th style="width: 75px">Area Code</th><th style="width: 144px">Off Time</th><th>Grave Yard On Time</th><th>First Shift OnTime</th><th width="125px">Comments Or Date By</th><th>Initials</th></tr>

<tr>

    <!--First row accross on the table-->
<td><select name="Department" id="Department" >
            <option value ="">Please select ...</option>
            <option value ="Upstairs Hang East">Upstairs Hang East</option>
            <option value ="Upstairs Hang West">Upstairs Hang West</option>
            <option value ="RDR">RDR</option>
            <option value ="Hang Sortation">Hang Sortation</option>
            <option value ="RACK WAREHOUSE">RACK WAREHOUSE</option>
            <option value ="Hang To Flat">HANG TO FLAT</option>
            <option value ="RACK SHOES">RACK SHOES</option>
            <option value ="FLAT 2">FLAT 2</option>
            <option value ="FLAT 1">FLAT 1</option>
            <option value ="Claims">Claims</option>
            <option value ="INBOUND">INBOUND</option>
            <option value ="OUTBOUND">OUTBOUND</option>
            </select></td>
<td style="width: 75px"><input id="areaCode" name="areaCode" onkeydown="return false;" type="text"/></td>
<td style="width: 144px"><input class="offtime" id="offtime" name="offtime"  type="text" /></td>
<td><input id="gytime" name="gytime" type="text" /></td>
<td><input id="ftime" name="ftime" type="text" /></td>
<td><input id="comments" name="comments" type="text" style="width: 139px" /></td>
<td><input id="ini" name="ini" type="text" style="width: 68px" /></td>
</tr>
<input id="deptkey" name="deptkey" onkeydown="return false;" type="hidden"/>

    <!--end of first row-->

<tr>

    <!--First row accross on the table-->
<td><select name="Department2" id="Department2" >
            <option value ="">Please select ...</option>
            <option value ="Upstairs Hang East">Upstairs Hang East</option>
            <option value ="Upstairs Hang West">Upstairs Hang West</option>
            <option value ="RDR">RDR</option>
            <option value ="Hang Sortation">Hang Sortation</option>
            <option value ="RACK WAREHOUSE">RACK WAREHOUSE</option>
            <option value ="Hang To Flat">HANG TO FLAT</option>
            <option value ="RACK SHOES">RACK SHOES</option>
            <option value ="FLAT 2">FLAT 2</option>
            <option value ="FLAT 1">FLAT 1</option>
            <option value ="Claims">Claims</option>
            <option value ="INBOUND">INBOUND</option>
            <option value ="OUTBOUND">OUTBOUND</option>
            </select></td>
<td style="width: 75px"><input id="areaCode2" name="areaCode2" onkeydown="return false;" type="text"/></td>
<td style="width: 144px"><input class="offtime2" id="offtime2" name="offtime2"  type="text" /></td>
<td><input id="gytime2" name="gytime2" type="text" /></td>
<td><input id="ftime2" name="ftime2" type="text" /></td>
<td><input id="comments2" name="comments2" type="text" style="width: 139px" /></td>
<td><input id="ini2" name="ini2" type="text" style="width: 68px" /></td>
</tr>
<input id="deptkey2" name="deptkey2"  type="hidden"/>


    <!--end of first row-->

<tr>

    <!--First row accross on the table-->
<td><select name="Department3" id="Department3" >
            <option value ="">Please select ...</option>
            <option value ="Upstairs Hang East">Upstairs Hang East</option>
            <option value ="Upstairs Hang West">Upstairs Hang West</option>
            <option value ="RDR">RDR</option>
            <option value ="Hang Sortation">Hang Sortation</option>
            <option value ="RACK WAREHOUSE">RACK WAREHOUSE</option>
            <option value ="Hang To Flat">HANG TO FLAT</option>
            <option value ="RACK SHOES">RACK SHOES</option>
            <option value ="FLAT 2">FLAT 2</option>
            <option value ="FLAT 1">FLAT 1</option>
            <option value ="Claims">Claims</option>
            <option value ="INBOUND">INBOUND</option>
            <option value ="OUTBOUND">OUTBOUND</option>
            </select></td>
<td style="width: 75px"><input id="areaCode3" name="areaCode3" onkeydown="return false;" type="text"/></td>
<td style="width: 144px"><input class="offtime3" id="offtime3" name="offtime3"  type="text" /></td>
<td><input id="gytime3" name="gytime3" type="text" /></td>
<td><input id="ftime3" name="ftime3" type="text" /></td>
<td><input id="comments3" name="comments3" type="text" style="width: 139px" /></td>
<td><input id="ini3" name="ini3" type="text" style="width: 68px" /></td>
</tr>
<input id="deptkey3" name="deptkey3" onkeydown="return false;" type="hidden"/>

    <!--end of first row-->

<tr>

    <!--First row accross on the table-->
<td><select name="Department4" id="Department4" >
            <option value ="">Please select ...</option>
            <option value ="Upstairs Hang East">Upstairs Hang East</option>
            <option value ="Upstairs Hang West">Upstairs Hang West</option>
            <option value ="RDR">RDR</option>
            <option value ="Hang Sortation">Hang Sortation</option>
            <option value ="RACK WAREHOUSE">RACK WAREHOUSE</option>
            <option value ="Hang To Flat">HANG TO FLAT</option>
            <option value ="RACK SHOES">RACK SHOES</option>
            <option value ="FLAT 2">FLAT 2</option>
            <option value ="FLAT 1">FLAT 1</option>
            <option value ="Claims">Claims</option>
            <option value ="INBOUND">INBOUND</option>
            <option value ="OUTBOUND">OUTBOUND</option>
            </select></td>
<td style="width: 75px"><input id="areaCode4" name="areaCode4" onkeydown="return false;" type="text"/></td>
<td style="width: 144px"><input class="offtime4" id="offtime4" name="offtime4"  type="text" /></td>
<td><input id="gytime4" name="gytime4" type="text" /></td>
<td><input id="ftime4" name="ftime4" type="text" /></td>
<td><input id="comments4" name="comments4" type="text" style="width: 139px" /></td>
<td><input id="ini4" name="ini4" type="text" style="width: 68px" /></td>
</tr>
<td><input id="deptkey4" name="deptkey4"  type="text"/></td>


    <!--end of first row-->










    <!--start of the weekend section-->
<tr>
<td colspan="7"><h1 align="center">Weekend Scheduling Request</h1></td>
</tr>

<tr>
<th>Department</th><th style="width: 75px">Saturday On Time</th><th style="width: 144px">Saturday Off Time</th><th>Comments or Date By</th><th>Signature</th></tr>

<tr>
<td><select name="sat_ob_department" id="sat_ob_department" >

            <option value ="">Please select ...</option>
            <option value ="Upstairs Hang East">Upstairs Hang East</option>
            <option value ="Upstairs Hang West">Upstairs Hang West</option>
            <option value ="RDR">RDR</option>
            <option value ="Hang Sortation">Hang Sortation</option>
            <option value ="RACK WAREHOUSE">RACK WAREHOUSE</option>
            <option value ="Hang To Flat">HANG TO FLAT</option>
            <option value ="RACK SHOES">RACK SHOES</option>
            <option value ="FLAT 2">FLAT 2</option>
            <option value ="FLAT 1">FLAT 1</option>
            <option value ="Claims">Claims</option>
            <option value ="INBOUND">INBOUND</option>
            <option value ="OUTBOUND">OUTBOUND</option>
            </select></td>

<td style="width: 75px"><input id="sat_ob_onTime" name="sat_ob_onTime" type="text" /></td>
<td style="width: 144px"><input class="sat_ob_offTime" id="sat_ob_offTime" name="time"  type="text" /></td>
<td><input id="ob_comments" name="ob_comments" type="text" /></td>
<td><input id="ob_signature" name="ob_signature" type="text" /></td>

</tr>


<tr>
<td><select name="sat_ib_department" id="sat_ib_department" >

            <option value ="">Please select ...</option>
            <option value ="Upstairs Hang East">Upstairs Hang East</option>
            <option value ="Upstairs Hang West">Upstairs Hang West</option>
            <option value ="RDR">RDR</option>
            <option value ="Hang Sortation">Hang Sortation</option>
            <option value ="RACK WAREHOUSE">RACK WAREHOUSE</option>
            <option value ="Hang To Flat">HANG TO FLAT</option>
            <option value ="RACK SHOES">RACK SHOES</option>
            <option value ="FLAT 2">FLAT 2</option>
            <option value ="FLAT 1">FLAT 1</option>
            <option value ="Claims">Claims</option>
            <option value ="INBOUND">INBOUND</option>
            <option value ="OUTBOUND">OUTBOUND</option>
            </select></td>

<td style="width: 75px"><input id="sat_ib_onTime" name="sat_ib_onTime" type="text" /></td>
<td style="width: 144px"><input class="sat_ib_offTime" id="sat_ib_offTime" name="time"  type="text" /></td>
<td><input id="ib_comments" name="ib_comments" type="text" /></td>
<td><input id="ib_signature" name="ib_signature" type="text" /></td>

</tr>


</table>

<div><br><p align="center"><input type="submit" value= "Submit Request "></p></div> 

<input type="hidden" value ="<?php echo date("m/d/Y",time())?>"name="Requestdate" id="Requestdate">



</form>

</body>
</html>

Insert_Process.php

<?php

$serverName = "localhost";

$db= new PDO( "sqlsrv:server=$serverName ; Database=lightingschedule", "test", "test");
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );



$stmt= $db->prepare("INSERT INTO tbl_lighting(Department, 
    areaCode, offtime, gytime, ftime, ini, 
    sat_ob_department, sat_ib_department,
    sat_ob_onTime,sat_ib_onTime,
    sat_ob_offTime, sat_ib_offTime,
    ob_signature,ib_signature, 
    deptkey, comments,ib_comments,ob_comments,Requestdate

 ) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

$stmt->bindParam(1, $_POST["Department"]);
$stmt->bindParam(2, $_POST["areaCode"]);
$stmt->bindParam(3, $_POST["offtime"]);
$stmt->bindParam(4, $_POST["gytime"]);
$stmt->bindParam(5, $_POST["ftime"]);
$stmt->bindParam(6, $_POST["ini"]);
$stmt->bindParam(7, $_POST["sat_ob_department"]);
$stmt->bindParam(8, $_POST["sat_ib_department"]);
$stmt->bindParam(9, $_POST["sat_ob_onTime"]);
$stmt->bindParam(10, $_POST["sat_ib_onTime"]);
$stmt->bindParam(11, $_POST["sat_ob_offTime"]);
$stmt->bindParam(12, $_POST["sat_ib_offTime"]);
$stmt->bindParam(13, $_POST["ob_signature"]);
$stmt->bindParam(14, $_POST["ib_signature"]);
$stmt->bindParam(15, $_POST["deptkey"]);
$stmt->bindParam(16, $_POST["comments"]);
$stmt->bindParam(17, $_POST["ib_comments"]);
$stmt->bindParam(18, $_POST["ob_comments"]);
$stmt->bindParam(19, $_POST["Requestdate"]);




$stmt->execute();



if ( $_POST['Department2']=!'' )
{

    $stmt= $db->prepare("INSERT INTO tbl_lighting(Department, 
    areaCode, offtime, gytime, ftime, ini, 
    sat_ob_department, sat_ib_department,
    sat_ob_onTime,sat_ib_onTime,
    sat_ob_offTime, sat_ib_offTime,
    ob_signature,ib_signature, 
    deptkey, comments,ib_comments,ob_comments,Requestdate

 ) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

$stmt->bindParam(1, $_POST["Department2"]);
$stmt->bindParam(2, $_POST["areaCode2"]);
$stmt->bindParam(3, $_POST["offtime2"]);
$stmt->bindParam(4, $_POST["gytime2"]);
$stmt->bindParam(5, $_POST["ftime2"]);
$stmt->bindParam(6, $_POST["ini2"]);
$stmt->bindParam(7, $_POST["sat_ob_department"]);
$stmt->bindParam(8, $_POST["sat_ib_department"]);
$stmt->bindParam(9, $_POST["sat_ob_onTime"]);
$stmt->bindParam(10, $_POST["sat_ib_onTime"]);
$stmt->bindParam(11, $_POST["sat_ob_offTime"]);
$stmt->bindParam(12, $_POST["sat_ib_offTime"]);
$stmt->bindParam(13, $_POST["ob_signature"]);
$stmt->bindParam(14, $_POST["ib_signature"]);
$stmt->bindParam(15, $_POST["deptkey2"]);
$stmt->bindParam(16, $_POST["comments2"]);
$stmt->bindParam(17, $_POST["ib_comments"]);
$stmt->bindParam(18, $_POST["ob_comments"]);
$stmt->bindParam(19, $_POST["Requestdate"]);

$stmt->execute();
}





if ( $_POST['Department3']=!'' )
{

    $stmt= $db->prepare("INSERT INTO tbl_lighting(Department, 
    areaCode, offtime, gytime, ftime, ini, 
    sat_ob_department, sat_ib_department,
    sat_ob_onTime,sat_ib_onTime,
    sat_ob_offTime, sat_ib_offTime,
    ob_signature,ib_signature, 
    deptkey, comments,ib_comments,ob_comments,Requestdate

 ) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

$stmt->bindParam(1, $_POST["Department3"]);
$stmt->bindParam(2, $_POST["areaCode3"]);
$stmt->bindParam(3, $_POST["offtime3"]);
$stmt->bindParam(4, $_POST["gytime3"]);
$stmt->bindParam(5, $_POST["ftime3"]);
$stmt->bindParam(6, $_POST["ini3"]);
$stmt->bindParam(7, $_POST["sat_ob_department"]);
$stmt->bindParam(8, $_POST["sat_ib_department"]);
$stmt->bindParam(9, $_POST["sat_ob_onTime"]);
$stmt->bindParam(10, $_POST["sat_ib_onTime"]);
$stmt->bindParam(11, $_POST["sat_ob_offTime"]);
$stmt->bindParam(12, $_POST["sat_ib_offTime"]);
$stmt->bindParam(13, $_POST["ob_signature"]);
$stmt->bindParam(14, $_POST["ib_signature"]);
$stmt->bindParam(15, $_POST["deptkey3"]);
$stmt->bindParam(16, $_POST["comments3"]);
$stmt->bindParam(17, $_POST["ib_comments"]);
$stmt->bindParam(18, $_POST["ob_comments"]);
$stmt->bindParam(19, $_POST["Requestdate"]);

$stmt->execute();
}




if ( $_POST['Department4']=!'' )
{

    $stmt= $db->prepare("INSERT INTO tbl_lighting(Department, 
    areaCode, offtime, gytime, ftime, ini, 
    sat_ob_department, sat_ib_department,
    sat_ob_onTime,sat_ib_onTime,
    sat_ob_offTime, sat_ib_offTime,
    ob_signature,ib_signature, 
    deptkey, comments,ib_comments,ob_comments,Requestdate

 ) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

$stmt->bindParam(1, $_POST["Department4"]);
$stmt->bindParam(2, $_POST["areaCode4"]);
$stmt->bindParam(3, $_POST["offtime4"]);
$stmt->bindParam(4, $_POST["gytime4"]);
$stmt->bindParam(5, $_POST["ftime4"]);
$stmt->bindParam(6, $_POST["ini4"]);
$stmt->bindParam(7, $_POST["sat_ob_department"]);
$stmt->bindParam(8, $_POST["sat_ib_department"]);
$stmt->bindParam(9, $_POST["sat_ob_onTime"]);
$stmt->bindParam(10, $_POST["sat_ib_onTime"]);
$stmt->bindParam(11, $_POST["sat_ob_offTime"]);
$stmt->bindParam(12, $_POST["sat_ib_offTime"]);
$stmt->bindParam(13, $_POST["ob_signature"]);
$stmt->bindParam(14, $_POST["ib_signature"]);
$stmt->bindParam(15, $_POST["deptkey4"]);
$stmt->bindParam(16, $_POST["comments4"]);
$stmt->bindParam(17, $_POST["ib_comments"]);
$stmt->bindParam(18, $_POST["ob_comments"]);
$stmt->bindParam(19, $_POST["Requestdate"]);

$stmt->execute();
}
?>











<h1 align="center">Successfully Entered!</h1>

Just a heads up if you're validating your input fields with (clientside) JavaScript. People can disable Javascript and then skip your validation since JavaScript is no longer active. — Bono, Feb 27 '15 at 16:57
I need it so when a user submits a a dept with dept key say O that there not able to do it again but for that same day — Veronica, Feb 27 '15 at 17:23

score 0 · Accepted Answer · edited Feb 27 '15 at 19:25

0

Here is what your HTML looks like when I run it:

enter image description here

Now, are you trying to store the data the users submit? If so, you will need what is called a "backend server", or a program that will save data even when the user closes their browser. You have a lot of work ahead of you: I suggest by starting with tutorials on MySQL with PHP.

Update: to test if a date/department combination exists already, try something like

$test_sql = "SELECT count(*) FROM `tbl_lighting` WHERE Department = '$_POST["Department"]' AND DATE(Requestdate) = DATE(NOW())"; 
$result = $db->prepare($test_sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn(); 
if($number_of_rows > 0){
    echo "department already entered today";
    return; //or die() or whatever
}

If that proof of concept works, I would immediately bindParam instead of using the _POST call.

I tried this cause I need the deptkey not department but the deptkey is in red and when I hit insert it goes blank

$stmt = "SELECT count(*) FROM `tbl_lighting` WHERE deptkey = '$_POST["deptkey"]' AND DATE(Requestdate) = DATE(NOW())"; 
$result = $db->prepare($stmt); 
$result->execute(); 
$number_of_rows = $result->fetchColumn(); 
if($number_of_rows > 0){
    echo "department already entered today";
    return; //or die() or whatever
}
else{

$stmt= $db->prepare("INSERT INTO tbl_lighting(Department, 
    areaCode, offtime, gytime, ftime, ini, 
    sat_ob_department, sat_ib_department,
    sat_ob_onTime,sat_ib_onTime,
    sat_ob_offTime, sat_ib_offTime,
    ob_signature,ib_signature, 
    deptkey, comments,ib_comments,ob_comments,Requestdate

 ) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

$stmt->bindParam(1, $_POST["Department"]);
$stmt->bindParam(2, $_POST["areaCode"]);
$stmt->bindParam(3, $_POST["offtime"]);
$stmt->bindParam(4, $_POST["gytime"]);
$stmt->bindParam(5, $_POST["ftime"]);
$stmt->bindParam(6, $_POST["ini"]);
$stmt->bindParam(7, $_POST["sat_ob_department"]);
$stmt->bindParam(8, $_POST["sat_ib_department"]);
$stmt->bindParam(9, $_POST["sat_ob_onTime"]);
$stmt->bindParam(10, $_POST["sat_ib_onTime"]);
$stmt->bindParam(11, $_POST["sat_ob_offTime"]);
$stmt->bindParam(12, $_POST["sat_ib_offTime"]);
$stmt->bindParam(13, $_POST["ob_signature"]);
$stmt->bindParam(14, $_POST["ib_signature"]);
$stmt->bindParam(15, $_POST["deptkey"]);
$stmt->bindParam(16, $_POST["comments"]);
$stmt->bindParam(17, $_POST["ib_comments"]);
$stmt->bindParam(18, $_POST["ob_comments"]);
$stmt->bindParam(19, $_POST["Requestdate"]);




$stmt->execute();

}

edited Feb 27 '15 at 19:25

Veronica

541
5
8
20

answered Feb 27 '15 at 17:07

user1717828

7,122
8
34
59

I have a php insert page I can post – Veronica Feb 27 '15 at 17:20
I use php with pdo and mssql not Mysql – Veronica Feb 27 '15 at 17:24
The form works I am able to subit the data just need it so that it does not submit twice by key and date. I know javascript can be disable but no one here would know how to do that so doesn't matter – Veronica Feb 27 '15 at 17:25
@Veronica, Ah, without the server code I wasn't sure how far you were :-) You can [enforce unique](http://stackoverflow.com/questions/15800250/add-unique-constraint-to-combination-of-two-columns#15800279) date/department combinations in your database. Try something like `ALTER TABLE tbl_lighting ADD CONSTRAINT once_daily UNIQUE(Department, Requestdate);` Unfortunately, I don't know about mssql specifically. – user1717828 Feb 27 '15 at 17:39
what about doing it with javascript for now until I can figure it out that way – Veronica Feb 27 '15 at 17:40
It is not working with ALTER TABLE tbl_lighting ADD CONSTRAINT once_daily UNIQUE(Department, Requestdate); I just wanted a simple javascript validation I am not worried about it getting disabled. That's why I only posted the html code. – Veronica Feb 27 '15 at 17:44
If it can't be done then I will have to figure another way. I need to launch this app and can't because of this issue. – Veronica Feb 27 '15 at 17:45
95% completed and I am stuck on this – Veronica Feb 27 '15 at 17:45
@Veronica, I'm afraid I don' know of a way to enforce the unique date/department constraint using just client-side Javascript. You must somehow access the DB (via PHP) to determine whether an entry has been submitted today. Good luck! – user1717828 Feb 27 '15 at 18:22
I am still trying to see if I can do it php side – Veronica Feb 27 '15 at 18:38
@Veronica, I updated the answer with some code that might work. Try it out and let me know if it works. Don't forget to properly bindParam after you've got it working! – user1717828 Feb 27 '15 at 18:49
I added how I think it should go but it goes to blank page this maybe going on the right track though – Veronica Feb 27 '15 at 19:22
@Veronica, OK we are getting into REALLY bad code right now, but without seeing your entire codebase I don't think I can rework it: trying changing `deptkey = '$_POST["deptkey"]' AND` to `deptkey = '$_POST[deptkey]' AND` in the first line. Again, this is bad but it might work until you get a real working solution worked into the DB. – user1717828 Feb 27 '15 at 19:31
ya this not going to work I will have to figure it out the right way I know it is getting into bad practice – Veronica Feb 27 '15 at 19:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/71903/discussion-between-veronica-and-user1717828). – Veronica Feb 27 '15 at 19:37

javascript validation on textboxt for a column and todays date before insert into sql

1 Answers1