[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

Booking system with PHP and MySQL

I am going to develope a sports center booking system.

I plan to have a web page that display a table contains a schedule that have date  as column and (day- Monday-Sunday) as row..
I want to store default value as false for each date and day.When user place booking, the value will change to true indicate the slot been booked. Now I have question of how should I store the date correspond to each availibility booking slot? Shall I use current system date and predict the date for each date .. if that is the case , how to handle the date if change from month to month?

How shall I store those value in MySQL so that I can display the previous and future date to display to user?User can always view the date that is whether booked by somebody or available for booking before he/she place booking.

Anyone can advice me?
0
redcoder
Asked:
redcoder
4 Solutions
 
gamebitsCommented:
You may want to look at this

http://www.jjwdesign.com/booking_calendar.html

either to use it or get inspiration.

Gamebits
0
 
JB04Commented:
I usually store dates using a unix timestamp, their are various functions available in PHP to manipulate timestamp's, for example doing eg.

echo date('r', strtotime('+1 month'));

would output the date it would be one month on from now.

For more information check out the PHP manual, all the available functions for manipulating time are shown on the bottom of this webpage:-


http://php.net/manual/en/ref.datetime.php


0
 
tdterryCommented:
As far as data organization goes, you don't want to have every date/day in the database.  You should only stored the bookings.  Your table might look something like this

tbl_booking
-------------
booking_id int |  date datetime | user_id int |  description varchar(100)

Put a key on the "date" field to make lookups fast

Then when you are building the HTML table to show the date(s) in question, you can search for all items in tbl_booking that are in your date range

To find all of the bookings in a specific range:
select * from tbl_booking where date > $start_date and date < $end_date_plus_one

You can store the dates in MySQL DATETIME format and use the MySQL date functions, or you can store the dates as UNIX timestamps and use the PHP functions.  I like using the MySQL functions because that puts the work in the DB where it belongs (my philosophy).

To find all of the bookings in the surrounding 10 weeks (5 prior and 5 next):
select * from tbl_booking where datediff($target_date,date) > -5 and datediff($target_date,date) < 5

If you use UNIX times, look up FROM_UNIXTIME and UNIX
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
tdterryCommented:
0
 
redcoderAuthor Commented:
So is that mean I have to fixed the date in my table that displayed in HTML in order to get the $start_date , $end_date_plus_one and $target_date...

0
 
redcoderAuthor Commented:
--> Put a key on the "date" field to make lookups fast

 Can u give me an example key ?
0
 
redcoderAuthor Commented:
And how would I get the date value from the HTML table that user choose to book on particular date?
0
 
tdterryCommented:
I assume you have some starting page from which the user will click or type a date to get to the detailed "booking page".  That's where you would get the $target_date.  You subtract a week or two and add a week or two to get the $start_date and $end_date_plus_one.  Say your page uses a table to display the calendar.  Each cell in your table would be associated with a date.  You query the DB for the range of dates in your table, and whatever comes up you use to fill in those cells with booking details, or a button linked to the booking or whatever.

"Key" means an index on a column in the database table.  If you tell the database to create an index key, it will speed up your queries, as in:

create table bookings (
    booking_id int NOT NULL auto increment,
    date datetime,
    description varchar(50),
    user_id int,
    PRIMARY KEY (booking_id),
    KEY (date)
) Type=MyISAM;

0
 
redcoderAuthor Commented:
I need something like shown in this URL:

https://hciresearch.hcii.cs.cmu.edu:8443/athletic/sport.jsp?SportID=4

It display time and date instead of calendar of a month.
0
 
tdterryCommented:
So what else do you need to know?  Your question is kind of open ended.  What's your experience level with HTML, PHP, MySQL, etc?
0
 
redcoderAuthor Commented:
I have did PHP and MySQL before but not up to master level. Only beginner level. I know how to get the value from and to MySQL.I know how to display the table in HTMl .

My problem now is don't know how to set a value in HTML table and how to get the value (date and time) from table in HTML and store it into MySQL (means when user select the particular cell in the table , the date and time is captured and store in MySQL). It is because it deals with current time, need to consider the rest of the day in the same week either before or after.

Sorry to confuse you with open ended question... I can open a new topic if u want me to. Just let me know.

0
 
redcoderAuthor Commented:
i thinnk I should clcose this topic to avoid open ended quesiton. And I will open a new one.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now