How to prevent duplicate record inserts with PDO

Question

I want to try to setup something where if user already submitted a dept key and current date it will stop the record from being inserted. It will give out an error saying duplicate record entered try again.

Here is part of my insert page but it is not working

$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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)WHERE NOT EXISTS 
(SELECT * FROM tbl_lighting where deptkey = $deptkey AND Requestdate= CAST(GETDATE() AS DATE)");

$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();

Here is my submit form

<form action = "insert_process.php" onsubmit="return validateForm()" name="form" id="form" method ="post" class="style1">

<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>
            </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"></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-->

Answer 1

The essence of the problem is stopping a duplicate record and I'll explain why unique keys and exceptions using PDO are the correct way.

Databases maintain relations and store data. One of the jobs of the database is handling concurrency and storing data. Preventing duplicate records in the database is handled by creating unique keys, hence disallowing such records appearing at all in the data model. Databases handle multiple users connecting at the same time, who in turn insert some data.

What I see people doing is selecting a record, then checking whether it exists and if 0 rows are returned - they proceed with the insert. This is bad approach and not only does it hurt performance, it can often fail in preventing duplicate records. Reason for this is because there's a slight delay between PHP (or any language) and MySQL server (or any other database server). By the time you've selected the data and done the check, a small amount of time has passed - let's say 1 millisecond. During that time, another user might have connected and gone through the same process (select, check if 0 rows, if 0 insert). Since the two users are doing the same thing at the same time, both would get the information back about the result - because of the tiny delay between PHP process and MySQL. In turn, you can end up with duplicate records. You also spent time selecting something and sending it through socket connection.

It's much quicker to just let the database handle all the work. Since insert cannot occur due to unique constraint, it's much easier for you to just insert and let the insert fail (it's much less "expensive" than selecting and checking in PHP). Database(s) also take care of concurrency or "multiple users at the same time". This means your database knows the state of its data, and it's best if you let it continue doing so. You also end up with less code.

Your algorithm should be this:

• Obtain input
• Prepare the statement
• Perform the insert
• If exception rises, there was a duplicate record (you can check the code returned by your db to be sure it's because of duplicate key or something else)

What Fred said in his comment is the correct, and the only correct way of really preventing the duplicate insert. Also, since you are using prepared statements, you need not specify any variables in your statement. Just bind everything and let the PDO take care of sanitizing user input.

Answer 2

Maybe you have to escape you're php variable $deptkey like this, and uppercase the WHERE conditionl like this :

$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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)WHERE NOT EXISTS 
(SELECT * FROM tbl_lighting WHERE deptkey = '".$deptkey."' AND Requestdate= CAST(GETDATE() AS DATE)");