pjcrooks2000
asked on
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.
rideID
ridename
decription
restrictions
price
duration_in_minutes
image
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
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.
rideID
ridename
decription
restrictions
price
duration_in_minutes
image
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
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 :)
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 :)
ASKER
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 ....
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 ....
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 :)
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 :)
ASKER
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 :)
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 :)
ASKER
Good afternoon Diablo
Here is a break down of what I need to do for your perusal http://82.43.246.169/spec.html
Here is a break down of what I need to do for your perusal http://82.43.246.169/spec.html
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.
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.
ASKER
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 :)
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?
>> 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?
ASKER
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
CREATE TABLE rides (rideID INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
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
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
CREATE TABLE rides (rideID INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
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
ASKER
>> 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:
name="checkbox[ride_id_her
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?
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:
name="checkbox[ride_id_her
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?
ASKER
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 :)
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.
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.
ASKER
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?
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 :)
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 :)
ASKER
Huh!
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 http://82.43.246.169/reserve.phps
Thanks muchly on with aborbing your last post !
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 http://82.43.246.169/reserve.phps
Thanks muchly on with aborbing your last post !
ASKER
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 :)
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 :)
ASKER
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?
Your not working right now are you? I am just looking at what job options I have myself. Pizza and coffee night is it?
ASKER
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 :)
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 :)
ASKER
Cheers... sweet dreams now!
ASKER
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 :)
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 :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Patrick
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
Patrick
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 :)
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 :)
ASKER
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!
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!
ASKER
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.