Do not register in the time zone if it already exists

Question

Do not register in the time zone if it already exists

Navigation

#1 by (5 votes)
#2 by (3 votes)

4

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

Example: I have an event in the DB beginning 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 do not allow it because it is in the time zone already registered ...

The DB has this structure: start - varchar (500) - example of register "18:00" and end - varchar (500) - example of register "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

asked by anonymous 24.08.2014 / 00:23

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 I should have a "double" test, no chance to take a test here right now.

24.08.2014 / 02:35

Is it possible to replace the alarm control of my car with an application? [closed] How to do the Facebook content upload technique?

score 3 · Accepted Answer

To do this, you need to modify the fields for datetime and write the complete date and time in them, for example: 2014-08-23 18:00 . And before inserting a new appointment, you have to do a query to see if a record is coming up, comparing whether start or end times are within the new start times and end, as in the example below:

Edit: Includes two more conditions to check if the new start and end times are not entirely within an already registered appointment.

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