I am making an online calendar and can not have 2 schedules scheduled on the same day and at the same time for the same professional Mysql PHP PDO

Asked

Viewed 50 times

0

I’m making a website for a barber shop and I need to solve a little problem, in this schedule there are 3 professionals who work in the barbershop, and have the option of choosing the professional, so I’ve been hitting my head for more than a week to solve SELECT and I can’t, so, there’s the column prof, start date) and time so it can’t be scheduled more than once on the same day at the same time for the same professional, can anyone help me? Thanks in advance! I will post my code here!

$dados = filter_input_array(INPUT_POST, FILTER_DEFAULT);

//Converter a data e hora do formato brasileiro para o formato do Banco de Dados
$data_start = str_replace('/', '-', $dados['start']);
$data_start_conv = date("Y-m-d H:i:s", strtotime($data_start));

$data_end = str_replace('/', '-', $dados['end']);
$data_end_conv = date("Y-m-d H:i:s", strtotime($data_end));

$query_event = "INSERT INTO agendar (title, fone, prof, servicos, color, start, hora, end) VALUES (:title, :fone, :prof, :servicos, :color, :start, :hora, :end)";

$insert_event = $conn->prepare($query_event);
$insert_event->bindParam(':title', $dados['title']);
$insert_event->bindParam(':fone', $dados['fone']);
$insert_event->bindParam(':prof', $dados['prof']);
$insert_event->bindParam(':servicos', $dados['servicos']);
$insert_event->bindParam(':color', $dados['color']);
$insert_event->bindParam(':start', $data_start_conv);
$insert_event->bindParam(':hora', $dados['hora']);
$insert_event->bindParam(':end', $data_end_conv);

if ($insert_event->execute()) {
    $retorna = ['sit' => true, 'msg' => '<div class="alert alert-success" role="alert">Agendamento cadastrado com sucesso!</div>'];
    $_SESSION['msg'] = '<div class="alert alert-success" role="alert">Agendamento cadastrado com sucesso!</div>';
} else {
    $retorna = ['sit' => false, 'msg' => '<div class="alert alert-danger" role="alert">Erro: Agendamento não foi cadastrado com sucesso!</div>'];
}


header('Content-Type: application/json');
echo json_encode($retorna);
php mysql sql json pdo

1 answer

0

The problem you are trying to solve with the search is solved in DB and not in the code.

At first, if you set the user identifier (prof) and the time of service (start) as a single key, your problem is solved. - Mysql - Unique Keys. If there is an attempt to dial at the same time, when submitting the request to DB, the insertion fails and the application only has to deal with the exception.

If the duration of the services is not important or the selection values are predefined, the above solution is sufficient. Otherwise, you can/you have to make DB stronger. With triggers - Mysql - Triggers - you can easily check whether a tag is overwritten with an existing one.

  • Hi friend, I could not understand you, I confess that I do not understand very well of Mysql, but how I would make this change in BD and how would put an echo to appear to the client that this time is unavailable?

  • You can give the commands in Mysql Workbench, to give an example. The first link I sent you describes how you can modify your table to enter the primary key. You have to adapt the commands to your case (Something like this: ALTER TABLE agendar ADD UNIQUE (prof, start); - This assumes that the table had no primary key). User information must be triggered (directly or indirectly) by the code block that is executed when the Insert fails. php&#xA; if ($insert_event->execute()) { &#xA; (...)&#xA; } else {&#xA; // Aqui!!!!!&#xA; }&#xA; (...)&#xA;

  • Good evening buddy, it worked perfectly, already myth thanks for the help, saved my life, rsrsrs.. The code got like this:

  • $query_event = "ALTER TABLE schedule ADD UNIQUE (prof, start, time)"; $query_event = "INSERT INTO schedule (title, phone, prof, services, color, start, time, end) VALUES (:title, :phone, :prof, :services, :color, :start, :time, :end)";

  • $insert_event = $conn->prepare($query_event);&#xA;$insert_event->bindParam(':title', $dados['title']);&#xA;$insert_event->bindParam(':fone', $dados['fone']);&#xA;$insert_event->bindParam(':prof', $dados['prof']);&#xA;$insert_event->bindParam(':servicos', $dados['servicos']);&#xA;$insert_event->bindParam(':color', $dados['color']);&#xA;$insert_event->bindParam(':start', $data_start_conv);&#xA;$insert_event->bindParam(':hora', $dados['hora']);&#xA;$insert_event->bindParam(':end', $data_end_conv);

  • if ($insert_event->execute()) { $returns = ['sit' => true, 'msg' => '<div class="Alert Alert-Success" role="Alert">Scheduled successfully! </div>']; $_SESSION['msg'] = '<div class="Alert Alert-Success">Successfully booked! </div>'; } Else { $returns = ['sit' => false, 'msg' => '<div class="Alert Alert-Danger" role="Alert">Sorry, this time is unavailable, try another time! </div>']; } header('Content-Type: application/json'); echo json_encode($returns);

  • Thank you very much, you saved my life, such a simple code and me hitting myself and breaking my head, thank you very much for the force!

Show 2 more comments

Browser other questions tagged php mysql sql json pdo

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