Booking system advice php and mysql tables

HI all i hope you can bear with me on this one,

I have some tables and scripts to add fairground rides to my databases at present.

I need to add some sort of booking system so that I can book rides using a html form and php scripts to process the information.

The nature of the booking system is that there are four rides that can be booked and the system should allow for certain criteria to be met too.

I have not really done something like this before so I would like to ask advice on the following setup, I do not have problems creating the myql tables or indeed I can try to work out the php scripting that would be suitable for the job.

Just a point I should make, i have a table with the ride information in it that has the following information.


Now i will be using the following fields and displaying it in table row format and adding some other checkboxes, and check boxes something like this:

checkbox(selectitem)  | ridename(text) | duration(text) | quantity max 4(select box) | price(text) | total price (text field)

In addition to these I will be asking for some customer details on my form

Customer name
email address
telephone number

There should also be a checkbox that when ticked should attempt to book seats together(near each other/same car/same seat or same row) running updates queries to my booking tables.

So what about these rides I have?

Ok the rides are as follows with price details how many seats and how many rows/cars and the duration of each ride:

Pirate Ship                       20 - 5 rows of 4 seats       30 mins      £1.00
Roller Coaster      16 - 4 rows of 4 seats                      20 mins      £3.00
Big Wheel                      32 - 8 cars of 4 seats                      30 mins      £1.50
Log flume                  10 boats 4 seats each                 20 mins     £2.00  (Boats leave every 2 mins)

These rides will be put into either a single or different tables with the correct ammount of seats/rows/boats to be booked and the customer should be able to schedule the rides so that they can select all four rides without them clashing with one another.

I have been asked to allow bookings to be made for a 3 hour period so that should give enough time to book rides without them clashing with one another.

I need some major advice on what would be the best ways to achieve something like this, i am sure that some of you guys have had to do something similar to this in the past but I must admit the complexity involved is probably far too much for just one question.  Therefore my question is to what will be the best way to do this?

Many thanks

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pjcrooks2000Author Commented:
PS:  I have an idea to create another flat booking table that will store each ride on a line and assign a number to the full order, I have apage whereby the customer can come back to check their order by using their name and the reference number that I shall be outputing to them on the confirmation or order page.

So in theory I will have a flat order table with all of the details put into it direct from the database one line for each ride and another table that will actually allow me to book the rides into it bu running update queries.

Subscribing to thread.

Hello again, I will come back and have a look at this tomorrow if noone else has helped you before then, a little too tired to give sufficient advice tonight. Another one of those early mornings for the both of us :)
pjcrooks2000Author Commented:
Indeed, I just though I would throw this one in with the mix just to really test you :)

Actually if you don't mind I will let you look at the full assignment specification, i personally think our tutor has gone nuts with tis one.... Perhaps you can advise me how to tackle the assignment in a meaningful way..  Going for the 100% marker with this one :)

Have a good night speak soon ....
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I will probably be away for the best part of this weekend coming but il be around for the next couple of days so we should be able to get you well on the way by then.

By the way, as you probably know, for anything that comes even remotely under the category of "homework questions" i'm only (strictly speaking) allowed to help you and give advice according the member agreement but i think we will be ok there, you have picked this stuff up well so there will be no problem in communicating ideas and methods.

Anyway, until tomorrow, good night :)
pjcrooks2000Author Commented:
Great stuff,

Yep I intend to do as much as I can myself but we have had one 1/2 hour lecture on PHP for this module.  I think my tutor is nuts as I said earlier.  Ive been reading the books watching the movies and doing a lot of searching, just somtimes when you get stuck some advice is required.

Many thanks speak tomorrow :)
pjcrooks2000Author Commented:
Good afternoon Diablo

Here is a break down of what I need to do for your perusal 
Good timing :)

Was just looking through the question. Il have a look through that then we can tackle this one head on. I think i would agree that your tutor is nuts lol, most people start out with "hello world", this is ambitious.
pjcrooks2000Author Commented:
It is level three i suppose, just refresh page i sent you and let me know if it changes at the top please ....

This would never happen in the real world as you and I both know.... Thanks, i mean serioulsy a them park ride booking system... hes a nutter :)
I had to do something similar to this a couple of years ago for an airline booking system only it was to be done in Excel and VBA, nightmare. Atleast this one is do-able :)

>> just refresh page i sent you and let me know if it changes at the top please ....

Got it noted :) You can remove it now, il do that if needed but we should be able to tackle the most of it ok here.

I think what we will do is get it working at a basic level and then add the extra features and tighter validation in after, in other words lay the foundation then build the house.

I think as the prices etc are fixed we will have them as static variables on the processing page which will be used in conjunction with the post data, the alterantive is storing all of these values in a database but as i said, seeing as they are static and fixed values there shouldn't be a need.

Where are you at at this point in time, do you have the form done?
pjcrooks2000Author Commented:
Ok cool, jobs done.... Well I have sort of a form structure for it without any scripting done to date.. was going to drop the form into my php.... I did this form a few days ago so it's probably not suitable considering the full breath of the requirements.

Just one point about the Prices, these are stored in a table anyway for which I will be pulling all of the ride information.  You have seen the rides table already but it now looks like this::SQL code i mean

ridename CHAR(20) NOT NULL,
description TEXT NOT NULL,
restrictions TEXT NOT NULL,
price DECIMAL (2,2) NOT NULL,
minutes INT(3) NOT NULL,
image_loco CHAR(100) NOT NULL

If possible I want to develop this for one or two rides to begin with and then add other rides via the admin form... I suppose the last ride the log flume will really throw everything out as its so much different to the other rides.

Let me ellaborate on the price thing I don't think I am making sense, i think in short what I am trying to say is that I want the price perhaps to be dynamic taken from the DB and not as a variable... But I am not going to argue with you if you think storing the variable is the best thing to do then i will go with you on that...   :)

Cheers !

p.s.  Just requested some information for a job as a Junior PHP developer, even though I am 32 years old ... lol

pjcrooks2000Author Commented:
>>  I want the price perhaps to be dynamic taken from the DB and not as a variable.

That'll be fine, it just means a select query will be needed to obtain the data, not a problem though.

I am thinking that the easiest way of going about obtaining the necessary information from the form is a html array.

The only data needed from the form (correct me if i am wrong) is which rides are selected and how many are in the cart for that ride. The rest of the information can be cross referenced from the table when its processed.

As a starting point, (i presume/hope this form is dynamically generated) for the checkboxes, make them a html array with the ride id as the key, so their name will look like this:


This will make life easier when processing later. It might be practical to use a 2D array along with the quantity but this depends on how you are handling the Add to cart option?
pjcrooks2000Author Commented:
OK yes as far as I understand the rides can be selected and a quantity for each selected, adding to cart I suppose does not have to be done via a link rather the variables can be collated towards the end, but I am not sure on the best method here.

Also there should be an option whereby the persons doing the booking will be able to check a box that will allow them to have all of their seats together. For this reason I think it would be a good idea to restict the maximum number of seats allowed to 4 per booking.  Similar to what you find on Tickermaster's site.  But even more important here as each car or row only has 4 seats, if someone picked 6 seats then that would mean 1 and 1/2 cars which I think is unecessary.

Also the times for which the rides run is of importance, i don't know whether to provide functionality on the form itself so that they can select running times or for the system to calcualte that itself by running through my tables and checking.

It's all a bit confusing for me at this point and I am trying to get my head around this one.  

You will know whats best to do as you have done something similar in the past and I will wait fot your advice on that I think :)
The problem is even if you restrict the seating to a maximum of 4 per booking what happens if only 2 seats are booked and the next person wants to book 3 seats, in order to keep them together you would then have to put them in a new row. As this goes on you will end up having rows with odd seats left here and there. To tackle this you would then have to calculate, with php, if a row has enough seats available together for the booking quantity... and if not you would then move on to a new row.

A 4 seat restriction would certainly simplify the problem though so i think we will keep that in mind.

The other problem, which is related to the above, is the times and booking with that in mind. I can't remember how i tackled this with the airline system, but the issue is you have a 24 hour time period where as you have to control several different rides operating on different time scales with different bookings being made for each. Needless to say it would be easiest to have a full sql table mapped out with a row for each time slot etc but i don't think this is going to be at all practical here which means more in the line of calculation.

I am going to have another look through the task link then il get back to you.
pjcrooks2000Author Commented:
Ok cool, yes its a bit of a headache and thats why I suggested breaking it down into smaller chucks.

Ok will wait for you reply, I just have to go and bleed all the radiators upstairs ones are not working :) You don't have a php script hat can bleed radiators do you?

In a perfect world i'd have a script that could do the cooking, hoovering and ironing :)

Coding the script shouldn't be so difficult, but figuring out the logic behind how the script will work is what stands in the way of that as always.

Here is something along the lines of what i have been thinking of:

You have a main table which will contain the time periods and bookings. The ride periods for either 20 minutes, or 30 minutes so you could handle this on a generic level by dividing the time scale up into 10 minute phases. That way a 20 minute booking takes up 2 phases and a 30 minute booking takes up 3 phases. That table would also have to contain a ride id and a customer id to cross reference.

Then each of the rides would have a table of its own which would contain the more specific information such as the seat quantity for the booking etc.

If this sounds at all vague, its because right now it's a little vague in my mind :) It needs more thought put to it which i will gladly do later however at this point i am afraid i am out of time.

One thing i might suggest is running this element of the project past the Experts in the mySQL TA, they will probably be able to better advise you on a suitable database structure. Coding is something i have no trouble with but theory is not my strong point.

I will post back when i get back online, good luck in the mean time :)
pjcrooks2000Author Commented:

OK i think I have to read and read the last comment... I got lost after the first line :)

In the meantime I have done as you said and changed the reserve.html file to reserve.php so that it runs on the server.  it's not parsing embedded php in html so I gave it the php extension instead.

If you want to grab a copy of the new file source here you go

Thanks muchly on with aborbing your last post !
pjcrooks2000Author Commented:
Hi D,

Just wondering if your still around and if you have had any further thoughts ?

If not I hope you had a nice night :)
Hi, sorry i haven't forgotten about this, i'm tied up in my own work at the moment unforutently and i'm going to need a little time to think this one through. Il try and put some time aside for it tomorrow :)
pjcrooks2000Author Commented:
Not a problem sir :)

Your not working right now are you?  I am just looking at what job options I have myself.   Pizza and coffee night is it?

pjcrooks2000Author Commented:
Its been a while since I have written a CV, but then again I have a whole lot more to put into it now.  I just want to be happy with my job what ever it is... Have a good morning :)
I'm just working on a freelance project at the moment, just about to call it a night, ready for an early start in the morning and another long day, plenty of coffee to come i think.

Good luck with the job hunting, i hope that works out for you :)

Il be in touch when i have a moment tomorrow, hopefully with a clear cut idea for how to go about doing this. Night :)
pjcrooks2000Author Commented:
Cheers... sweet dreams now!
pjcrooks2000Author Commented:
Just an idea and some thoughts I was having.

I suppose I could make all of my tables for the rides and populate them with the seats and rows and times etc etc...  I could have a field for each seat that indicates whether it is booked or not .. so its either empty or "booked"

I could then have some tables for inserting data, customer and orders table using an customerid relationship.  So if my customer books 4 seats for the roller coaster I would firstly run the booking query on the rides tables.. updating the fields to booked and whatever fields I book I could use the seat number and row number in my customer order table by running an insert into customer/orders.

Does this sound plausable to you D?  Hopefully you will get some time today to check this out :)

Sorry for slow replies, i'm seriously tied down with other commitments at the moment.

The bottom line here is you can make this work best with a cross reference system, you have a main table that contains one set of information and secondary tables that contains the finer details.

Your idea is certainly one way of approaching it. I was trying to think of a way you could simplify the database structure but the hold back was what ever you do to reduce the complication there means more processing with php later, so in fact the whole point of trying to simplify the process is defeated.

I think if i was going to do this i would have a table which contained all of the time slots for the period of time within the day that you are focusing on. I would then do as you said and have additional tables (probably one for each ride) which contained the seat availability data, There would then be a final table which contained customer information. The tables could then be cross referenced to work out which rides seats are available and at what times etc.

Your thinking is on the right track, so i would work along those lines. When something cannot be simplified in a certain way without making life more difficult in another its easiest to take the basic approach and make everything easily accessible and manageable. There are however experts who will be able to better advise you on the theory behind this, this isn't my strong point.

Now heres the bad news, as i said before i am pretty tied up with other things at the moment so i'm not sure how much time i will be able to spend on EE this month, if i am on here it will be for very short periods to provide "short and sweet" answers. To ensure that you get the best possible advice for this project I am going to recommend you take the elements of this question and break them up into smaller chunks (as you suggested earlier) and ask them as new questions so other experts can help you.

If i happen to be around at the time and have a moment spare i will happily help you out, this way just means you don't have to be waiting around for me to get back to you, i'm going to be a little bit of an unreliable resource at the moment :)

As for this question, i am quite happy for you to get a points refund at Community Support as i haven't had the time to provide you with adequate help.

I wish you the best of luck with this project and i hope the job front works out for you, apologies for not being able to be more helpful this time around :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pjcrooks2000Author Commented:
Not a problem Diablo,

It does not have to be in yet so I think what I will do is sit on it for a while and work on some other stuff that i have to do.

I have had several ideas today i just need to work them out and practice them in my head, thanks for all the help thus far and if your around in a week or so I may catch you on here.

Good luck with your current workload I am sure you soon crack it off!

Cya soon

Thank you for your understanding :)

A little tip to make even the most complicated things simplified, try not to think of the process in terms of php, but instead think of it as basic english. If you can get down on a piece of paper what should happen and how you want it to work when it comes to coding it will be no problem, real basic example:

if (the form is submitted) {
 if (post field "input_name" is valid) {
  run the database query

... etc.

Obviously its best used with more complicated scenarios, but thats what i mean by it anyway :)

If you have any questions open when i'm about and available i will drop in and see what i can do to help. I *should* be around more consistantly after the weekend.

BTW thank you for the Accepted answer and the A grade even though it wasn't a fully answered question. It promoted me to a "Certified PHP Sage". Before long i expect you will have the ability to be up there in the Top 15 list giving me a bit of competition :)
pjcrooks2000Author Commented:
Yeah indeed a little bit of pseudocode did no one any harm.

heh brilliant about the sage certification, thats excellent well done, you deserve to be there.

Hopefully one day top 15 Hmmmm heres hoping......

Have a good week anyroad, I am compiling my report for final year project so i have lots to get on with in that.  I will try during that time to write the English like version down of what I want to achieve.

No doubt you will find out what the plan is when i get somewhere with it.

cya soon!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.