0

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.

Steve P
  • 709
  • 3
  • 22

2 Answers2

2

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.

Lotzki
  • 489
  • 1
  • 4
  • 18
  • the problem that i would have with setting the columns to unique is that i have several meeting rooms all with columns that may overlap, which i completely forgot about till you mentioned that :P i think i know how to sort it but don't know till i try :P oh and im using a php framework so most of that i need to decypher to get to work, i will get back to you =] – Steve P Dec 07 '12 at 14:53
  • 1
    Just try something, there are tons of methods to solve this :) (e.g. storing a combination of room number and time) – Lotzki Dec 07 '12 at 14:57
  • its getting the if else method to work that im struggling with rather than anything else (now) hrm I am wondering how i will get the number of results, i will look through the docs, see if i can find anything. thanks for the help =] – Steve P Dec 07 '12 at 15:25
  • 1
    I think I know what you mean, though it is usually your task to do the thinking I gave you some ideas, check out the edited answer :) Next time be sure to ask only specific problems, not a whole process. – Lotzki Dec 07 '12 at 16:15
0

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.

Steve P
  • 709
  • 3
  • 22