Avoid date and time duplicity for the same scheduling professional

Asked

Viewed 1,674 times

-1

Friends I’m trying to create an agenda for a dental clinic, but I’m having a hard time preventing the client from scheduling a date and time that has already been scheduled for that selected professional. For example, someone has already scheduled the service with professional John the date 01/06/2015 at 12:00hs, I would like someone else not be able to schedule for the same professional the same date and time, that the system warned to choose another date and time for that professional or to schedule for another professional available for the desired date and time.

I am using this code below that I register, but it does not prevent duplicate scheduling registration.

<?php if(isset($_POST['enter'])){

$nome = $_POST['nome'];
$tel = $_POST['tel'];
$cel = $_POST['cel'];
$email = $_POST['email'];
$plano = $_POST['plano'];
$horas = $_POST['horas'];
$prof = $_POST['prof'];
$data = $_POST['data'];
$tempo = date("dd/mm/YY His",time());


$pdo = new PDO('mysql:host=localhost;dbname=site', "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT * FROM agendar WHERE data LIKE = '{$data}' AND horas ='{$horas}' AND prof = '{$prof}'");
if($stmt->rowCount()>=1){

echo "<meta http-equiv='refresh' content='0; URL= agenda.php'>
      <script type=\"text/javascript\">
      alert(\"Esta data e hora já esta agendada para esse Profissional!<br />
              Tente com outro Profissional ou outra data e hora!<br />
              Obrigado!!!\");</script>";

 return die;

 }else{

 $stmt = $pdo->prepare ('INSERT INTO agendar (nome, tel, cel, email, plano, prof, data, horas)
                        VALUES (:nome, :tel, :cel, :email, :plano, :prof, :data, :horas)');

 $stmt->execute(array(':nome' => $nome,
                     ':tel' => $tel,
                     ':cel' => $cel,
                     ':email' => $email,
                     ':plano' => $plano,
                     ':prof' => $prof,
                     ':horas' => $horas,
                     ':data' => $data,
                     ':data' => $data,
                     ));

  if($stmt == ''){
    echo "<script language='javascript'>
          window.alert('Ocorreu um erro ao Agendar sua Avaliação!');
          </script>";
  }else{
    echo "<script language='javascript'>
          window.alert('Avaliação Agendada com sucesso!');
          </script>";

}}}
?>

I use that line of code $sql = mysql_query("SELECT * FROM agendar WHERE data LIKE '".$data."' AND horas ='".$horas."' AND prof = '".$prof."'"); if(mysql_num_rows($sql)>=1){ in Mysql to avoid duplicity, and works legal, preventing scheduling.

I adapted for the PDO getting like this $stmt = $pdo->prepare("SELECT * FROM agendar WHERE data LIKE = '{$data}' AND horas ='{$horas}' AND prof = '{$prof}'"); if($stmt->rowCount()>=1){ but I don’t know if I made the adaptation correctly, and I think that might be where I’m going wrong!!!

As requested I am entering the FORM code below:

<form name="enter" method="post" action="" enctype="multipart/form-data">
<table cellpadding='2' cellspacing='2'>
<tr>
<td align="center"><span style="font:15px Verdana, Geneva, sans-serif; color:#000066;">AGENDE HOJE MESMO SUA AVALIAÇÃO</td>
</tr>
<tr>
<td><span>Nome:</span><input style="width:250px" type="text" name="nome" value=''></td>
</tr>
<tr>
<td><span>Telefone Residêncial:</span><input style="width:166px" type="text" name="tel" value='(21) '></td>
</tr>
<tr>
<td><span>Telefone Celular:</span><input style="width:188px" type="text" name="cel" value='(21) '></td>
</tr>
<tr>
<td><span>E-mail:</span><input style="width:247px" type="text" name="email" value=''></td>
</tr>
<tr>
<td align="center">
<?php 
$conn = new PDO('mysql:host=localhost;dbname=site', 'root', '');
$stmt = $conn->prepare('SELECT plano FROM planos');
$stmt-> execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(isset($_POST['enter'])){

$plano = $_POST['plano'];
$pdo = new PDO('mysql:host=localhost;dbname=site', "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('UPDATE agendar SET plano = :plano');
$stmt->execute(array( ':plano' => $plano));
}
?>
<select name="plano">
<?php foreach($result as $row){ ?>
    <option value="<?php echo $row['plano'];?>"><?php echo $row['plano'];?></option>
    <?php } ?>
</select><br />
<?php 
$conn = new PDO('mysql:host=localhost;dbname=site', 'root', '');
$stmt = $conn->prepare('SELECT prof FROM profissionais');
$stmt-> execute(); 
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(isset($_POST['enter'])){

$prof = $_POST['prof'];
$pdo = new PDO('mysql:host=localhost;dbname=site', "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('UPDATE agendar SET prof = :prof');
$stmt->execute(array( ':prof' => $prof));
}
?>
<select name="prof">
 <?php foreach($result as $row){ ?>
    <option value="<?php echo $row['prof'];?>"><?php echo $row['prof'];?></option>
    <?php } ?>
 </select><br />
</td>
</tr>                       
<tr>
 <td><input width="10" type="text" name="data" value="Data" id="data" />
<img src="img/calendar.png" width="30" height="30" style="float: none; margin-top: -22px; cursor: pointer;" title="Selecione a data do Agendamento!" onClick="$('#data').focus();" />
<?php 
$conn = new PDO('mysql:host=localhost;dbname=site', 'root', '');
$stmt = $conn->prepare('SELECT horas FROM horarios');
$stmt-> execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(isset($_POST['enter'])){

$horas = $_POST['horas'];
$pdo = new PDO('mysql:host=localhost;dbname=site', "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('UPDATE agendar SET horas = :horas');
$stmt->execute(array( ':horas' => $horas));
}
?>
<select name="horas">
<?php foreach($result as $row){ ?>
    <option value="<?php echo $row['horas'];?>"><?php echo $row['horas'];?></option>
    <?php } ?>
</select><br />
</td>
</tr>                       
<td><input class="input" type="submit" name="enter" value="Agendar" />  <span>Se preferir, ligue para: (21)2290.3702</span></td>
</tr>
</table>
</form>

If friends can help me, I’d be grateful.

Hugs to all!!!

php html mysql pdo

  • I find it easier to release only the available schedules for the person to post.. the same way a flight check-in works... the user can only choose the available seats. ?

  • you could put the form that the user registers the time as well?

  • OK Andrei, I just entered the code of FORM that I’m wearing. I think that’s what you. asked, am I right? Thanks for the attention, thanks!!!

1 answer

0

I’ve analyzed your codes, and I think you could do it in stages so you don’t have a problem with duplicity...

Before the user registers name, address, etc... he has to choose 1 day that is best for him to make the appointment at the clinic. Then he chooses the times that are available on that selected date. And then he registers the personal data...

I formatted your code like this

schedule.php

      // aqui o codigo verifica se existe na url a data e a hora, se não existir ele executa baixo...
     <?php if(!isset($_GET['data']) && !isset($_GET['hora'])){ ?>

    <form method="get" action="agendar.php">
     <td><input width="10" type="text" name="data" value="Data" id="data" />
    <img src="img/calendar.png" width="30" height="30" style="float: none; margin-top: -22px; cursor: pointer;" title="Selecione a data do Agendamento!" onClick="$('#data').focus();" />
    <input width="10" type="submit" name="envia" value="Selecionar" /> 
     </form>
    // aqui o codigo verifica se existe na url a data e a hora, se não existir a hora ele executa baixo...
   <?php  } elseif(isset($_GET['data']) && !isset($_GET['hora']) { ?>

     <form method="get" action="agendar.php">

    <?php 

    //***** alterado ***** > a variável que estava escrita era a $pdo
    $conn= new PDO('mysql:host=localhost;dbname=site', "root", "");

    $data = $_GET['data'];
    //MUITO IMPORTATE: aqui estou selecionando apenas os horários vazios da tabela horário conforma a tabela agendar daquela data selecionada.
    $stmt = $conn->prepare("SELECT horas FROM horarios LEFT JOIN agendar ON agendar.horas = horarios.horas WHERE horarios.horas IS NULL AND agendar.data = $data");
    $stmt-> execute();
    //repare que eu alterei esta linha de código pois não estava funcionando com ASSOC, afinal oque você precisa é de um array
    $result = $stmt->fetchAll();
    if(isset($_POST['enter'])){

    $horas = $_POST['horas'];
    $pdo = new PDO('mysql:host=localhost;dbname=site', "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare('UPDATE agendar SET horas = :horas');
    $stmt->execute(array( ':horas' => $horas));
    }
    ?>
    <select name="horas">
    <?php foreach($result as $row){ ?>
        <option value="<?php echo $row['horas'];?>"><?php echo $row['horas'];?></option>
        <?php } ?>
    </select><br />
     <input width="10" type="submit" name="envia" value="Selecionar" />
   </form>
         // aqui o codigo verifica se existe na url a data e a hora, se existir a hora e a data ele executa baixo..
        <?php  } elseif(isset($_GET['data']) && isset($_GET['hora']) { ?>

       <form name="enter" method="post" action="" enctype="multipart/form-data">
        <table cellpadding='2' cellspacing='2'>
        <tr>
        <td align="center"><span style="font:15px Verdana, Geneva, sans-serif; color:#000066;">AGENDE HOJE MESMO SUA AVALIAÇÃO</td>
        </tr>
        <tr>
         <td align="center"><span style="font:15px Verdana, Geneva, sans-serif; color:#000066;">Data selecionada: <input name="data" value="<?php echo $_GET['data'] ?>"/></td>
        </tr>
        <td align="center"><span style="font:15px Verdana, Geneva, sans-serif; color:#000066;">Hora selecionada:  <input name="hora" value="<?php echo $_GET['hora'] ?>"/></td>
        </tr>

        <tr>
        <td><span>Nome:</span><input style="width:250px" type="text" name="nome" value=''></td>
        </tr>
        <tr>
        <td><span>Telefone Residêncial:</span><input style="width:166px" type="text" name="tel" value='(21) '></td>
        </tr>
        <tr>
        <td><span>Telefone Celular:</span><input style="width:188px" type="text" name="cel" value='(21) '></td>
        </tr>
        <tr>
        <td><span>E-mail:</span><input style="width:247px" type="text" name="email" value=''></td>
        </tr>
        <tr>
        </table>
        </form>
        <?php  }  ?>

now just enter in the database

I did it with the get method just to give you an idea.

But that selection of the time of day that are available will avoid duplicity for Voce as it will only select the time fields of the time table that are available.

I hope I’ve helped.

  • Thanks Andrei, I will analyze your code, so I learn a little more...rsrsrsrs... Once again BRIGADÃO for your attention. Hugs!!!

  • Tranquil Murilo, hug

  • Andrei, I entered your changes, but it is referring to the following error when I select a date and click on select: Notice: Undefined variable: Conn in C: wamp www site agenda agendar.php on line 46, and line 46 is so: $stmt = $conn->prepare("SELECT horas FROM horarios LEFT JOIN agendar ON agendar.horas = horarios.horas WHERE horarios.horas IS NULL AND agendar.data = $data");. If you can help me with this mistake, I’ll be grateful. Hug friend!!!

  • found the error... typed a comment there with ***** for you to view.. But see, this code is just a template of how you should act...

  • Maybe it doesn’t work the right way because I’m not doing tests and stuff...

Browser other questions tagged php html mysql pdo

You are not signed in. Login or sign up in order to post.