Solved

Need to check if table is already booked

Posted on 2010-08-14
3
463 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
[X]
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
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migrating to phpbb forum from vBulletin 4.2 3 93
Insert PHP into HTML page. 7 51
Ajax success not firing alert 6 40
Why is my select returning NaN 21 35
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

732 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