MySQL INSERTing rows into a database with datetime

问题:

I am making a meeting room booking system in which there should be no times within the start and end dates so in theory the validation should check for no dates/times within one start and end date time frame.

I have two tables, I can insert into it fine with both start and end dates so the only columns i am interested in at the moment are these

meetingrooms
|------------------------------------|
|- bookingtime -|-bookingend-|

I understand the principle behind the sanity check and the check i can do in psudocode. Here is the code i have got so far - >

p4a_db::singleton()->query("INSERT INTO meetingrooms(location_id, bookingtime, bookingend, merono_id)
                WHERE bookingtime < " . $date . " AND bookingend > " . $date . "
                OR
                bookingdate < " . $date . " AND bookingend > " . $dateend . "
                VALUES(?,?,?,?)",
                array($location, $date, $dateend, $merono));

I don't want to insert data directly into the statement but until i understand how to do this i am stuck, so the question,

How do i perform a sanity check before the data is inserted so that i don't get dates within booked times.

any help would be greatly appreciated.

回答1:

Edit:

I've been overthinking my answer and I realized that the old solution will not work in your case since you need the time span, comparing the start and end date is useless.

My way of processing this would be:

1. Save the dates as int, use 24h system (7:40am is 740, 9:50pm is 2150)
2. Check for stored dates where: (Start<NewStart<End)
3. Check for stored dates where: (Start<NewEnd<End)
4. When processing several rooms, just store room number + time as int. That way you can still use the method from 2 and 3.

2 and 3 can be done in a sql query, check out this link.

Old answer (checking for duplicates)

This is an example of how to check for duplicates (in this case email) before inserting the text:

    $emailexist = $mysqli->prepare("select email from users where email = ?");
    $emailexist->bind_param('s', $email);
    $emailexist->execute();
    $emailexist->store_result();
    if ($emailexist->num_rows > 0) {
        $emailexist->close();
        $mysqli->close();
        return true;
    }
    else {
        $emailexist->close();
        $mysqli->close();
        return false;
    }

It checks if there are rows which contain the string. If so (if number of rows higher than 0) it returns true (which means, the date already exists).

You can just adapt this to you code.

However, you could also just set the columns to UNIQUE. Then you get an error when trying to insert it. It is easier and you won't have problems with concurrent connections.

回答2:

after a long and intensive search, I have now got a working example of this method, along with a method of protecting against sql injection, here's the code;

if ($this->BookingValue == 1)
    {
        $sql = "SELECT COUNT(*) as num FROM meeting_room_bookings
                WHERE
                (
                (? < start_at AND ? > start_at)
                OR
                (? > start_at AND ? < end_at)
                )
                AND
                meeting_room_id = ?";

        $result = p4a_db::singleton()->fetchRow($sql, array($date, $date, $date, $dateend, $merono));

        if ( 0 == $result["num"] )
        {

         p4a_db::singleton()->query("INSERT INTO meeting_room_bookings (start_at, end_at, meeting_room_id)
                    VALUES
                    (?,?,?)", array($date, $dateend, $merono));

            return true;
        }       
        else
        {
         return false;

There isn't much to explain about this code, but in term of differences, (excluding the change in column names with the table) the query is now prepared before the value is set, then it is possible to use it in an if statement, thus allowing the validation to take place to filter results between different dates.

along with this i have added validation to stop dates from other meeting rooms being included within the statement via the AND statement where the meeting room id is limeted to a single value.

Although now, which will lead on to a separate question is another thrown error that comes from this statement, i know the insert is sound but something from this prepared statement causes the error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens File: Pdo.php, Line: 234

Although now i am looking into a error that is thrown from the prepared statement and will update this answer when there is a fix, thanks for the help.