Prevent user from registering 3 times in the same week

Asked

Viewed 112 times

-1

I am developing a system for scheduling reservations of locations and equipment for users registered in this system, which in case are teachers, reserve such items for their classes: the teacher logs in the system, chooses the equipment and location and chooses a free date and time to make use of this reservation.

I’m trying to make sure that the same user can’t book more than 3 times in the same week, this being the week from Monday to Saturday. I tried to do with several PHP and SQL functions (Mysql) but did not have any success.

Here is the structure of my SQL table:

 CREATE TABLE agend_reservas (
  ID_AGENDAMENTO bigint(20) NOT NULL,
  LOCAL1  int(11) DEFAULT NULL,
  LOCAL2  int(11) DEFAULT NULL,
  LOCAL3  int(11) DEFAULT NULL,
  LOCAL4  int(11) DEFAULT NULL,
  LOCAL5  int(11) DEFAULT NULL,
  LOCAL6  int(11) DEFAULT NULL,
  LOCAL7  int(11) DEFAULT NULL,
  LOCAL8  int(11) DEFAULT NULL,
  LOCAL9  int(11) DEFAULT NULL,
  LOCAL10 int(11) DEFAULT NULL,
  LOCAL11 int(11) DEFAULT NULL,
  LOCAL12 int(11) DEFAULT NULL,
  LOCAL13 int(11) DEFAULT NULL,
  EQUIPAMENTO1  int(11) DEFAULT NULL,
  EQUIPAMENTO2  int(11) DEFAULT NULL,
  EQUIPAMENTO3  int(11) DEFAULT NULL,
  EQUIPAMENTO4  int(11) DEFAULT NULL,
  EQUIPAMENTO5  int(11) DEFAULT NULL,
  EQUIPAMENTO6  int(11) DEFAULT NULL,
  EQUIPAMENTO7  int(11) DEFAULT NULL,
  EQUIPAMENTO8  int(11) DEFAULT NULL,
  EQUIPAMENTO9  int(11) DEFAULT NULL,
  EQUIPAMENTO10 int(11) DEFAULT NULL,
  INTERNET varchar(3) NOT NULL,
  RESERVADO_PARA varchar(60) NOT NULL,
  RESERVA_PARA_DATA date NOT NULL,
  HORA_INICIO time NOT NULL,
  HORA_FIM time NOT NULL,
  RESERVADO_NA_DATA date NOT NULL,
  OBSERVACAO text
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the structure of my table, the field RESERVA_PARA_DATA is the date the user will use the reservation, the field RESERVADO_NA_DATA is the current date of the server that the user made the reservation - that date doesn’t matter - and the field RESERVADO_PARA is the name of the user who is booking.

php mysql

  • A Count in the bank according to your criteria within the week in question no longer helps you ?

  • I’m not getting to know what to use in Where to be able to filter in what week should the check take place.

  • Add in the question the structure of your table. Without this it is difficult to help. Already in advance you should probably have a Date field in your table with your reservations, with this just make a count within the current week that will return the amount of records found. With this validate if the return is greater than X times.

  • @Lucasbarbosafonseca Add the code to make it easier for us to help

  • I edited the question

3 answers

1

I think it is very quiet to solve your problem, first based on the day the person is making the booking you will need to know which is the first and last day of the current week, so use the code below:

$semana_atual           = date('w');
$primeirodia_semanatual = date('Y-d-m', strtotime('-'.$semana_atual.' days'));
$ultimodia_semanaatual  = date('Y-d-m', strtotime('+'.(6-$semana_atual).' days'));

echo 'Primeiro dia Semana Atual: '.$primeirodia_semanatual;
echo '<br/>';
echo 'Último dia Semana Atual: '.$ultimodia_semanaatual;

NOTE: I put the printing of the variables just so you understand the logic.

After determining the first day and the last day of the current week, just use the dates/variables in a query, to check how many reservations were made that week, by who is trying to book, so I understood the teacher.

select count(*)
  from agend_reservas
  where reserva_para_data between '$primeirodia_semanatual'
                              and '$ultimodia_semanaatual'
    and reservado_para = '$login_de_quem_esta_reservando'

If the result of count(*) is greater than 3, means that the teacher has already booked 3x in the current week :), that is, can not book more in the week, will have to wait the following week.

0


As I had already commented on your question, if you make a count on your table agend_reservations will be right with you.

In your case it would look like this:

$datenow = date('Y-m-d');// Data atual

$first = (new DateTime($datenow))->modify('Last Monday')->format('Y-m-d');
$last = (new DateTime($datenow))->modify('Next Sunday')->format('Y-m-d');

$sql = "SELECT COUNT(*) AS TOTAL FROM agend_reservas 
            WHERE   reserva_para_data BETWEEN '$first' AND '$last'
            AND     reservado_para = '$usuario_resevando'";

$result = mysqli_query($sua_conexao, $sql);
$data = mysql_fetch_assoc($result);
$qtd = $data['TOTAL'];

if($qtd > 2) 
    die("Não é permitido cadastrar 3 reservas na mesma semana");
    // Aqui trate da melhor forma que ficar conveniente para a sua aplicação

Useful links:

COUNT

Catch the first and last day of the week

I hope I’ve helped.

  • Looking at your solution I believe that $Qtde, will bring registration amount one, why in the query you are using the COUNT(*), which will bring the registration total in a single line, are sure it works, you tested?

  • @Raphaelgodoi very good your remark and you’re right, I edited the answer.. Grateful!

  • Now that the answer is edited, could reconsider the downvotes ?

  • 1

    Thank you very much guy even, before posting my question here was this logic I had in mind, but I didn’t know how to catch the second-last week and next Saturday the date that the user wishes to use the reservation to be able to play in a query and compare with the between the booking amount between the two dates, I found it very complicated, but thanks to you I learned, executed, it worked, I will be able to use in future applications and this response will be able to help many other developers. Thank you very much, hug

-1

Voce could add two boolean columns primeira_reserva and segunda_reserva in the table. When someone makes the reservation, he checks if the column primeira_reserva is true.

If it is false, it makes the reservation and marks the column as true. Case by case primeira_reserva be true, it checks whether the column segunda_reserva is true.

If it is false, it makes the reservation and marks the column as true. Case by case segunda_reserva be true, it simply slashes the reserve.

This way, no one could register more than 2 times a week.

Then just program the application to mark these two columns as false weekly. :)

  • I understood, but how I would basically mark the column as fake weekly?

  • You can set up your server to do this automatically for you: https://answall.com/questions/26112/como-fazer-um-script-runautomatically

  • Thanks friend, I’ll test and I give back

  • Okay, good luck buddy! :)

  • 1

    The reply of @8bit served more to my case but thank you, I am very grateful for the help :D

  • Nothing, a pleasure to help! C:

Show 1 more comment

Browser other questions tagged php mysql

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