Solved

Need to check if table is already booked

Posted on 2010-08-14
3
462 Views
Last Modified: 2012-05-10
Hi experts!

I have 21 columns named;

----
table_nightclub_1, table_nightclub_2, table_nightclub_3, table_nightclub_4, table_nightclub_5, table_nightclub_6, table_nightclub_7, table_nightclub_8, table_nightclub_9, table_nightclub_10

table_lounge_1, table_lounge_2, table_lounge_3, table_lounge_4, table_lounge_5, table_lounge_6

table_patio_1, table_patio_2, table_patio_3, table_patio_4, table_patio_5
----

I have 21 checkboxes on my page to select if a table should be booked or not. These return the value "yes" or "no" to the database.

What I want, is to check if one of the checked tables already are booked, before I insert a new reservation in my database. Is there a easy way to do this?

My insert to SQL is attached.

THANKS in advance! :-)
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "new_tablebooking")) {
  $insertSQL = sprintf("INSERT INTO reservations (booker, customer_name, customer_mobile, customer_guests, `comment`, customer_email, table_nightclub_1, table_nightclub_2, table_nightclub_3, table_nightclub_4, table_nightclub_5, table_nightclub_6, table_nightclub_7, table_nightclub_8, table_nightclub_9, table_nightclub_10, table_patio_1, table_patio_2, table_patio_3, table_patio_4, table_patio_5, booking_time, booking_date, p_nr, queue, payment, table_lounge_1, table_lounge_2, table_lounge_3, table_lounge_4, table_lounge_5, table_lounge_6) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_SESSION['MM_UserId'], "int"),
                       GetSQLValueString($_POST['customer_name'], "text"),
                       GetSQLValueString($_POST['customer_mobile'], "text"),
                       GetSQLValueString($_POST['customer_guests'], "int"),
                       GetSQLValueString($_POST['comment'], "text"),
                       GetSQLValueString($_POST['customer_email'], "text"),
                       GetSQLValueString(isset($_POST['table_nightclub_1']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_2']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_3']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_4']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_5']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_6']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_7']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_8']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_9']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_nightclub_10']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_patio_1']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_patio_2']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_patio_3']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_patio_4']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_patio_5']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString($datetime, "date"),
                       GetSQLValueString($_POST['booking_time'], "date"),
                       GetSQLValueString($_POST['p_nr'], "int"),
                       GetSQLValueString($_POST['queue'], "text"),
                       GetSQLValueString($_POST['payment'], "text"),
                       GetSQLValueString(isset($_POST['table_lounge_1']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_lounge_2']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_lounge_3']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_lounge_4']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_lounge_5']) ? "true" : "", "defined","'yes'","'no'"),
                       GetSQLValueString(isset($_POST['table_lounge_6']) ? "true" : "", "defined","'yes'","'no'"));

  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($insertSQL, $db) or die(mysql_error());

  $insertGoTo = "tablebookings.php";
  header(sprintf("Location: %s", $insertGoTo));
}

Open in new window

0
Comment
Question by:sittinDuck
3 Comments
 
LVL 6

Expert Comment

by:nitinsawhney
ID: 33436656
The table structure you are having is ideally not suited for such transactions, Instead it should be a normalized database structure.

The database table structure should be like

--------------
   tables
--------------
ID
Name     (e.g. table_nightclub_1)

--------------
Customer
--------------
ID
UserID
Name
Mobile
Email

--------------
Reservation
---------------
ID
Customer_ID
Guests
booking_time
booking_date
p_nr
queue
comment
payment

If there is a possibility of one customer booking more than one table then there should be another table for taking care of that.

------------------------
reservation_tables
------------------------
reservation_id
table_id

Now accordingly there will be lot of changes that will need to be done at your end.
0
 
LVL 1

Accepted Solution

by:
scarybot earned 500 total points
ID: 33436705
I can't think of a way to do this all in one query...

What I would do is to make a query before that one, which requests the state of all of the tables on the booking date.


Try something along these lines.
$result2 = mysql_query("SELECT * from reservations where date=<date> limit 1") // you could so put additional conditions in here to search for a specific time slot.

// fill this with a full list of tables
$tablestr = "table_lounge1 table_lounge2 table_lounge3 table_lounge4....";
$tables = explode(' ', $tablestr);

$this_day = $result->fetchrow_assoc();

foreach ($tables as $thistable) {
  if ($this_day[$thistable] == 'yes') {
    echo "Oh no, this table is already booked!";
    exit;
  }
}





}

Open in new window

0
 

Author Comment

by:sittinDuck
ID: 33436726
True nitinsawhney, I will think about your suggestion! Thanks! :-)

Thank you scarybot, seems like the solution I need right now :-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wordpress Query 5 42
pass foreach data in php into jquery ajax 10 44
Find RGB colors from a screen. 2 15
if statement malfunction 5 16
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question