I want to make a condition that determines whether the appointment is available with a particular doctor or not. If it is not available, the condition will execute (return true);
this is My code I tried to make a condition but it didn't work.

     if($_POST){  
   $patient_obj->docId =$_POST['selectDr'];
   $patient_obj->appStart =$_POST['appStart'];
   $patient_obj->appEnd =$_POST['appEnd'];
   $patient_obj->patientId =$_POST['patientId'];

   $startDateInput= $patient_obj->appStart;
   $startDateInput=strtotime($startDateInput);
   $startDateInput= date('Y-m-d H:i:s', $startDateInput);

   $endDateInput= $patient_obj->appEnd;
   $endDateInput=strtotime($endDateInput);
   $endDateInput= date('Y-m-d H:i:s', $endDateInput);
   

   $sql = "SELECT AppStart,AppEnd,docID FROM appointments WHERE docID=?";
   $stmt = $conn->prepare($sql);
   $stmt->bindParam(1,$patient_obj->docid);
   $stmt->execute();
   $data=$stmt->fetch();
    extract($data);
    $startDateInDb=$AppStart;
    $startDateInDb=strtotime($startDateInDb);
    $startDateInDb= date('Y-m-d H:i:s', $startDateInDb);
    $endDateInDb=$AppEnd;
    $endDateInDb=strtotime($endDateInDb);
    $endDateInDb= date('Y-m-d H:i:s', $endDateInDb);
  if(($startDateInput>=$startDateInDb && $startDateInput <=$endDateInDb) || ($endDateInput >=$startDateInDb && $endDateInput <= $endDateInDb) || ($startDateInput <= $startDateInDb && $endDateInput >=$endDateInDb)) {
       echo'
       <script>
       alert("change the appointment")
       </script>';
  } 
  else{
   $patient_obj->addAppointment();
}

CodePudding user response：

Note: fetch() only returns the 1st record of your query (WHERE docID=?).Hence please add conditions to your query to determine whether there exists any records in the database which overlaps with the newly submitted appointment data.

Hence, Please change the block:

$sql = "SELECT AppStart,AppEnd,docID FROM appointments WHERE docID=?";
   $stmt = $conn->prepare($sql);
   $stmt->bindParam(1,$patient_obj->docid);
   $stmt->execute();
   $data=$stmt->fetch();
    extract($data);
    $dateStart=$AppStart;
    $dateStart=strtotime($dateStart);
    $dateStart= date('Y-m-d H:i:s', $dateStart);
    $dateEnd=$AppEnd;
    $dateEnd=strtotime($dateEnd);
    $dateEnd= date('Y-m-d H:i:s', $dateEnd);
   
   if (($startDateInput>=$dateStart && $startDateInput<=$dateEnd) && ($endtDateInput>=$dateEnd && $endtDateInput>=$dateStart)) {
      echo'<script> alert("the appointment is already taken");</script>';
    exit();
   }
   else{
     $patient_obj->addAppointment();
  }

to

   $sql = "SELECT COUNT(*) as total_rows FROM appointments WHERE docID=? and ((AppStart >= ? && AppStart <=?) || (AppEnd >= ? && AppEnd <=?) || (AppStart <= ? && AppEnd >=?))";

   $stmt = $conn->prepare($sql);
   $stmt->bindParam(1,$patient_obj->docid);
   $stmt->bindParam(2,$startDateInput);
   $stmt->bindParam(3,$endDateInput);
   $stmt->bindParam(4,$startDateInput);
   $stmt->bindParam(5,$endDateInput);
   $stmt->bindParam(6,$startDateInput);
   $stmt->bindParam(7,$endDateInput);

   $stmt->execute();

   $row = $stmt->fetch(PDO::FETCH_ASSOC);
   $total_rows = $row['total_rows'];

   if ($total_rows!=0) {
      echo'<script> alert("the appointment is already taken");</script>';
      exit();
   }
   else{
      $patient_obj->addAppointment();
  }

To further demonstrate, I have made a testing link , with code as follows:

<?php
ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);

if ($_POST) {
$servername="localhost";
$db="xxxxxx";
$username="xxxxxx";
$password="xxxxx";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}

$docid=$_POST["docid"];
$startDateInput=$_POST["startDateInput"];
$endDateInput=$_POST["endDateInput"];



$sql = "SELECT COUNT(*) as total_rows FROM appointments WHERE docID=? and ((AppStart >= ? && AppStart <=?) || (AppEnd >= ? && AppEnd <=?) || (AppStart <= ? && AppEnd >=?))";

   $stmt = $conn->prepare($sql);
/*
   $stmt->bindParam(1,$patient_obj->docid);
   $stmt->bindParam(2,$startDateInput);
   $stmt->bindParam(3,$endtDateInput);
   $stmt->bindParam(4,$startDateInput);
   $stmt->bindParam(5,$endtDateInput);
   $stmt->bindParam(6,$startDateInput);
   $stmt->bindParam(7,$endtDateInput);
*/


   $stmt->bindParam(1,$docid);
   $stmt->bindParam(2,$startDateInput);
   $stmt->bindParam(3,$endDateInput);
   $stmt->bindParam(4,$startDateInput);
   $stmt->bindParam(5,$endDateInput);
   $stmt->bindParam(6,$startDateInput);
   $stmt->bindParam(7,$endtDateInput);



   $stmt->execute();

   $row = $stmt->fetch(PDO::FETCH_ASSOC);
   $total_rows = $row['total_rows'];

   if ($total_rows!=0) {
//      echo'<script> alert("the appointment is already taken");</script>';
//      exit();
 echo "<font color=red>Already taken</font>";
   }
   else{
    //  $patient_obj->addAppointment();
echo "<font color=blue>Allow insertion</font>";
  }
}

  ?>

Current Data in db: <br>
Doctor1 From:  <font color=red>2021-11-28 12:05:12</font> to  <font color=red>2021-11-30 12:00:00</font><br>
Doctor1 From: <font color=red>2021-11-01 00:00:00</font> to  <font color=red>2021-11-02 00:00:00</font><br>

<br><br>

<form method=POST>
<input name=docid value="Doctor1"><br>
<input name=startDateInput value=""> (e.g. 2021-11-28 1:0:0)<br>
<input name=endDateInput value=""> (e.g. 2021-11-30 12:0:0)<br>
<input type=submit>
</form>

The above code can be see LIVE at http://www.createchhk.com/SO/testSO28Nov2021a.php