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

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

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

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
sittinDuckAuthor Commented:
True nitinsawhney, I will think about your suggestion! Thanks! :-)

Thank you scarybot, seems like the solution I need right now :-)
0
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
PHP

From novice to tech pro — start learning today.