if/else statement to prevent duplicates/overlapping in database PDO

Question

I'm currently working on building a local database using XAMPP and using PDO. I got my database working as intended except for my if/else loop which i'm building to prevent duplicates from being posted into my database. I'm brand new to coding in PDO and i've just recently started coding again in general, so bear with me if i have some seriously weird looking code.. Just to clarify, i'm not looking for a simple "here take this code" as i'm really interested in learning how this works.

$conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);



$dupe = $conn->prepare("SELECT starttime FROM missions GROUP BY starttime HAVING COUNT(starttime) > 1");  //Query för att kolla efter dubbletter i databasen. Denna fungerar i PHPmyadmin/SQL
$dupe->execute();  // Exekvera SQL query för dubbletter

// If/else loop som ser till att det inte kan postas några dubbletter. Om den märker av att det finns en liknande post i DB så körs ej execute.

if($duperesult = $dupe->fetchAll()) {


    $sql = "INSERT INTO missions (startdate, starttime, endtime, description)  
    VALUES ('$startdate' , '$starttime' , '$endtime' , '$description')";  //Inmatning av information.

    $conn->exec($sql);      // Exekvering av SQL Query. 
}
else  {

    echo "Detta är en dubbelbokning!";

}

Duplicates in database

Please put your code directly in your question, not in images. Also, be specific about what's not working. Are you getting errors? If so, what are they? If not, what _is_ happening. What are you expecting to happen? What debugging have you already done? — Patrick Q, Mar 16 '18 at 17:57
Sorry, i have actually never posted on a forum about coding before. But what i want it to do is - Send the query to my database and look at the specified column to see if there are any duplicates. If there are duplicates i want it to jump to my else statement and display the error message. As of right now the conditions in my if/else statement always seems to return as true and therefor it allows duplicates. — OddOneOut, Mar 16 '18 at 18:01
Don't try to prevent duplicates solely by querying to see if the value already exists -- that method breaks down quickly as you add concurrent users. Use UNIQUE constraints on your database instead. — Alex Howansky, Mar 16 '18 at 18:01
@AlexHowansky Can i put the Unique constraint on all of my columns or is it a one column thing? — OddOneOut, Mar 16 '18 at 18:04
You can have multiple unique constraints, and you can use multiple columns in a constraint if needed. — Don't Panic, Mar 16 '18 at 18:05
Depends what you want to be unique. See [this](http://www.mysqltutorial.org/mysql-unique-constraint/) for some more details. — Alex Howansky, Mar 16 '18 at 18:06
I'm reading up on Unique Constraints as we speak, but will this alone make sure that duplicates won't be posted? — OddOneOut, Mar 16 '18 at 18:07
What exactly is your definition of a "duplicate" here? If you're trying to prevent events from having overlapping times, then unique keys won't work. — Patrick Q, Mar 16 '18 at 18:12
I'm going try to explain this as good as my english will allow, in my database people are going to be able to record upcoming work orders, but what i want to prevent, for example that Nina has a cleaning job between 10:00 and 11:00 and that another job gets posted within the same timeframe. Does that make any sense? — OddOneOut, Mar 16 '18 at 18:29
So just to clarify, in that case you just mentioned, you would want to prevent a new job with a start time of 10:30 and an end time of 11:15, correct? — Patrick Q, Mar 16 '18 at 18:34
I'm a little hesitant to mark this as a dupe, but there are a number of posts on this topic already. What you're looking to prevent aren't "duplicates", but conflicts/overlaps. Take a look at [this](https://stackoverflow.com/questions/11098541), [this](https://stackoverflow.com/questions/9205118), [this](https://stackoverflow.com/questions/6571538), and other results from [this search](https://www.google.com/search?q=stackoverflow+php+mysql+prevent+overlapping+times&oq=stackoverflow+php+mysql+prevent+overlapping+times&aqs=chrome..69i57j69i64.13654j0j7&sourceid=chrome&ie=UTF-8) — Patrick Q, Mar 16 '18 at 19:00
I will read the material, thanks alot for the help Patrick and everyone else :) I will update if i find a solution to my problem. — OddOneOut, Mar 16 '18 at 19:38
**WARNING**: When using PDO you should be using [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) with placeholder values and supply any user data as separate arguments. In this code you have potentially severe [SQL injection bugs](http://bobby-tables.com/). Never use string interpolation or concatenation and instead use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) and never put `$_POST`, `$_GET` or any user data directly in your query. Refer to [PHP The Right Way](http://www.phptherightway.com/) for general guidance and advice. — tadman, Mar 16 '18 at 20:41

Parfait · Accepted Answer · 2018-03-18T22:17:19.077

Reading from comments, you are not trying to prevent only duplicates but any overlapping time segments. Therefore, consider adjusting your static SQL that checks against PHP variables with parameterized queries for starttime and endtime on same startdate:

# CHECKS WITHIN INTERVAL, ENDPOINT OVERLAP, OR COVERS ENTIRE INTERVAL
$checkSQL = "SELECT COUNT(*) As RecordCount
             FROM missions 
             WHERE ((:st_param BETWEEN starttime AND endtime) OR 
                    (:et_param BETWEEN starttime AND endtime))
               AND (startdate = :st_date) 
             HAVING COUNT(*) > 1";

$dupe = $conn->prepare($checkSQL);    
$dupe->execute(array(':st_param' => $starttime, ':et_param' => $endtime, 
                     ':st_date' => $startdate));

$apnSQL = "INSERT INTO missions (startdate, starttime, endtime, description)
           VALUES (? , ? , ?, ?)";

if ($dupe->fetchColumn() > 0) {
     echo "Detta är en dubbelbokning!";
} else  {
     $res = $conn->prepare($apnSQL);

     $res->bindParam(1, $startdate, PDO::PARAM_STR);
     $res->bindParam(2, $starttime, PDO::PARAM_STR);
     $res->bindParam(3, $endtime, PDO::PARAM_STR);
     $res->bindParam(4, $description, PDO::PARAM_STR);

     $res->execute();
}

Rextester DEMO

Yes, well i've been trying to work around with my database and have removed posts in it manually through php admin. But the principle is the same even though the time has changed, i can't get it to stop overlaps. — OddOneOut, Mar 18 '18 at 21:41
Thank you very much @Parfait It now works fine and stops all overlaps. I cannot thank you enough. However i just noticed that it will say it is a overlap/duplicate if the time is the same but at a different date. — OddOneOut, Mar 18 '18 at 22:12
That did it! I've marked the answer and again, thanks alot Parfait! — OddOneOut, Mar 18 '18 at 22:21

if/else statement to prevent duplicates/overlapping in database PDO

1 Answers1