Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need to check if table is already booked

Posted on 2010-08-14
3
461 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

860 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