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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

You may want to look at this

either to use it or get inspiration.

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:-

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

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

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
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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...

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

 Can u give me an example key ?
redcoderAuthor Commented:
And how would I get the date value from the HTML table that user choose to book on particular date?
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;

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

It display time and date instead of calendar of a month.
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?
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.

redcoderAuthor Commented:
i thinnk I should clcose this topic to avoid open ended quesiton. And I will open a new one.
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.