asked on

Need to check if table is already booked

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

nitinsawhney

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.

ASKER CERTIFIED SOLUTION

scarybot

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sittinDuck

ASKER

True nitinsawhney, I will think about your suggestion! Thanks! :-)

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