CENK

First of all, thanks for the feedback. There must be an error that occur, it can only be made bookings for one day at the time. If they want one that stops at 23:59, and wants it to continue the next day, they have to make two reservations. (I have made the some changes to the times insted of 00:00 and 24:00, I have 00:01 and 23:59.) It would probably be best that if they choose a start time, and that only times that occur after that time they selected will be shown in the end time. But I have no idea how to do that. If you have a hint for what I can search for I will be grateful. Yes, there is more than one room. I think I have a solution for this, and it is to make the column "Romnr" primary key as well. I have done some tests, and it's seems to work. I haven't done much changes in my SELECT-sentence. But, I think it works as it is. It does take account for room number, and when the booking starts and ends. $query = "SELECT * FROM testmoterom WHERE (Romnr='$moterom' AND '$bookstart' <= slutt AND '$bookslutt' >= start);";

I have been given an assignment to make a simple booking system that is going to be used for booking of meeting rooms. And I have had an idea how I want to do it, but I’m not very good with php and sql. This is what I have now, but I have trouble with double bookings, and check if times are booked. I am sure there are a lot of other approaches to how to do this, and I am open for other ideas. This is the code i used for creating the table for registration of rooms, which I'm refering to in the other table CREATE TABLE RegistrerMoterom (Romnr INT(3) NOT NULL, PRIMARY KEY (Romnr)); This is the code I used for the table for register bookings CREATE TABLE testmoterom (Romnr INT(3) NOT NULL, start DATETIME NOT NULL, slutt DATETIME NOT NULL, PRIMARY KEY (start, slutt) FOREIGN KEY (Romnr) REFERENCES RegistrerMoterom(Romnr)); This is the form I am using to book rooms. <!--Start form for booking --> <form id="bookingmmoterom" name="bookingmmoterom" method="post" action=""> <select name="moterom" id="moterom"> <option></option> <?php include("HenteRaderMoterom.php"); /* Dynamic drop-down box with rooms that are stored in table "RegistrerMoterom "*/ ?> </select> <em>(velg møterom)</em> <label for="dato">Dato: </label><input id="dato" name="dato" type="date"/> <!-- HTML5 for choosing a date --> </select> </br> </br> <!-- Start time for booking --> <select name="start" id="start"> <option value="00:00">00:00</option> <option value="00:30">00:30</option> <option value="01:00">01:00</option> <option value="01:30">01:30</option> <option value="02:00">02:00</option> <option value="02:30">02:30</option> <option value="03:00">03:00</option> <option value="03:30">03:30</option> <option value="04:00">04:00</option> <option value="04:30">04:30</option> <option value="05:00">05:00</option> <option value="05:30">05:30</option> <option value="06:00">06:00</option> <option value="06:30">06:30</option> <option value="07:00">07:00</option> <option value="07:30">07:30</option> <option value="08:00">08:00</option> <option value="08:30">08:30</option> <option value="09:00">09:00</option> <option value="09:30">09:30</option> <option value="10:00">10:00</option> <option value="10:30">10:30</option> <option value="11:00">11:00</option> <option value="11:30">11:30</option> <option value="12:00">12:00</option> <option value="12:30">12:30</option> <option value="13:00">13:00</option> <option value="13:30">13:30</option> <option value="14:00">14:00</option> <option value="14:30">14:30</option> <option value="15:00">15:00</option> <option value="15:30">15:30</option> <option value="16:00">16:00</option> <option value="16:30">16:30</option> <option value="17:00">17:00</option> <option value="17:30">17:30</option> <option value="18:00">18:00</option> <option value="18:30">18:30</option> <option value="19:00">19:00</option> <option value="19:30">19:30</option> <option value="20:00">20:00</option> <option value="20:30">20:30</option> <option value="21:00">21:00</option> <option value="21:30">21:30</option> <option value="22:00">22:00</option> <option value="22:30">22:30</option> <option value="23:00">23:00</option> <option value="23:30">23:30</option> <option value="24:00">24:00</option> </select> <!-- When booking should end --> <select name="slutt" id="slutt"> <option value="00:00">00:00</option> <option value="00:30">00:30</option> <option value="01:00">01:00</option> <option value="01:30">01:30</option> <option value="02:00">02:00</option> <option value="02:30">02:30</option> <option value="03:00">03:00</option> <option value="03:30">03:30</option> <option value="04:00">04:00</option> <option value="04:30">04:30</option> <option value="05:00">05:00</option> <option value="05:30">05:30</option> <option value="06:00">06:00</option> <option value="06:30">06:30</option> <option value="07:00">07:00</option> <option value="07:30">07:30</option> <option value="08:00">08:00</option> <option value="08:30">08:30</option> <option value="09:00">09:00</option> <option value="09:30">09:30</option> <option value="10:00">10:00</option> <option value="10:30">10:30</option> <option value="11:00">11:00</option> <option value="11:30">11:30</option> <option value="12:00">12:00</option> <option value="12:30">12:30</option> <option value="13:00">13:00</option> <option value="13:30">13:30</option> <option value="14:00">14:00</option> <option value="14:30">14:30</option> <option value="15:00">15:00</option> <option value="15:30">15:30</option> <option value="16:00">16:00</option> <option value="16:30">16:30</option> <option value="17:00">17:00</option> <option value="17:30">17:30</option> <option value="18:00">18:00</option> <option value="18:30">18:30</option> <option value="19:00">19:00</option> <option value="19:30">19:30</option> <option value="20:00">20:00</option> <option value="20:30">20:30</option> <option value="21:00">21:00</option> <option value="21:30">21:30</option> <option value="22:00">22:00</option> <option value="22:30">22:30</option> <option value="23:00">23:00</option> <option value="23:30">23:30</option> <option value="24:00">24:00</option> </select> </br> <input type="submit" value="Book rom" id="bookrom" name="bookrom"> <input type="reset" value="Nullstill" id="nullstill" name="nullstill"> </form> </body> </html> The php code that runs after the user press submit, but it does stop at the SELECT-sentence. And this is the error that displays: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00 <= 'slutt' AND 2012-04-03 01:00 >= 'start')' at line 1 <?php $moterom=$_POST["moterom"]; $start=$_POST["start"]; $slutt=$_POST["slutt"]; $dato=$_POST["dato"]; $bookrom=$_POST["bookrom"]; $bookstart=$dato." ".$start; /*Puts date and time into one varable for start time*/ $bookslutt=$dato." ".$slutt; /*Puts date and time into one varable for end time*/ if ($bookrom) { include("db-tilkobling.php"); /*Includes connection to database*/ $moterom=$_POST["moterom"]; $start=$_POST["start"]; $slutt=$_POST["slutt"]; $dato=$_POST["dato"]; $bookstart=$dato." ".$start; $bookslutt=$dato." ".$slutt; $query = "SELECT * FROM testmoterom WHERE (Romnr='$moterom' AND $bookstart <= 'slutt' AND $bookslutt >= 'start');"; //Check if room i taken $result = mysql_query($query) or die(mysql_error());; /*IT STOPS HERE*/ if (mysql_num_rows($result) >= 1) { // If it has been reseventet an event print ("Det har allerede blitt resertert noe i tidsrommet. Sjekk ledige tider i høyre kolonne"); } else { // If everyting is OK, insert data into database $sqlSetning="INSERT INTO testmoterom VALUES ('$moterom','$bookstart','$bookslutt');"; mysql_query($sqlSetning) or die ("ikke mulig å registrere data i databasen"); print ("Takk for din registrering! Følgende tider er nå registrert: </br> Romnr: $moterom </br> $dato fra kl. $start til $slutt"); } } ?> SO, in the end I want to know what I am doing wrong, and what other approaches I can(should) take. I am open for other solutions! Ideally I wanted to use check boxes with only available times, then when I insert them they will be stored in different lines i the database. Thanks!