Do not register in the time range if it already exists

Asked

Viewed 114 times

4

How not to register events in the time zone that is already registered ?

Example: I have an event in BD starting at 18:00 and ending at 22:00, I need that if the user tries to register a new event from 19:00 to 23:00 the system does not allow because it is in the already registered time range...

BD is with this structure: inicio - varchar(500) - example of registration "18:00" and end - varchar(500) - example of registration "22:00"

The Mysql Query for PHP 5.4:

$query = mysql_query("INSERT INTO compromiso (termino, inicio) VALUES ('$termino', '$inicio')") or die(mysql_error());
php mysql

  • It would help if the column were DATETIME, have a chance to change?

  • can I change! how is the time format ? 22:00:00 ?

  • I actually think it would be better for TIME right ?

  • 1

    If the tracks never cross overnight, TIME better yes.

  • in fact they pass from one day to the next, but I have other fields with the day, month and year...

  • Ever tried to use between with a select into?

  • @Patrick, set an example as an answer...

  • I can’t, but take a look at that answer, it might help you: http://stackoverflow.com/a/3025332/3029543

  • Ah, avoid using it mysql, instead, use mysqli.

Show 4 more comments

2 answers

5

select * 
from compromisso 
where (
      ('2014-08-23 19:00' between inicio and termino
       or
       '2014-08-23 23:00' between inicio and termino)
       or
       (inicio BETWEEN '2014-08-23 19:00' AND '2014-08-23 23:00' 
        or 
        termino BETWEEN '2014-08-23 19:00' AND '2014-08-23 23:00')
      )

I think you should have a "double" test, no chance of doing a test here now.

3


To do this more easily you need to modify the fields to datetime and save the full date and time in them for example: 2014-08-23 18:00. And before entering a new appointment, you have to make a query to see if any registration results, comparing if start or end hours are within the new start hours and end, as in the example below:

Edit: Includes two more conditions, to check if the new start and end hours are not fully within an already registered commitment.

SELECT * FROM compromisso WHERE
(inicio BETWEEN '2014-08-23 19:00' AND '2014-08-23 23:00')
OR (termino BETWEEN '2014-08-23 19:00' AND '2014-08-23 23:00')
OR ('2014-08-23 19:00' BETWEEN inicio AND termino)
OR ('2014-08-23 23:00' BETWEEN inicio AND termino)

Sqlfiddle

  • The Sqlfiddle query could be in a Rigger , generating an error if there is event already registered, simple way to ensure the integrigade.

Browser other questions tagged php mysql

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