Jump to content

Trying to make a simple booking system

CENK

By CENK
in PHP Coding Help

 Share

Recommended Posts

CENK Newbie

CENK

Posted
Posted

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!

Link to comment
Share on other sites
CENK Newbie

CENK

Posted
  • Author
Posted

First of all, thanks for the feedback.

consider

1. if end time is before start time, is it an error or does it span two dates? (how do you account for that)

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.

 

2. is there more than one room? how do you account for that?

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.

 

3. google PHP BETWEEN datetime

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);";

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.