CODEWITHSUNDEEP
phpmysqlsqldatabaseduplicates
OddOneOut
OddOneOut

Reputation: 3

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

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

Upvotes: 0

Views: 627

Answers (1)

Parfait
Parfait

Reputation: 107687

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

Upvotes: 1

Related Questions