how to check and indicate the availability

[hiiiiii@]

hi  im doing a project on online table reseration. im using a table  "reservation" to store the table details of reserved table
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| reserve_id   | int(15)     | NO   | PRI | NULL    | auto_increment |
| member_id    | int(15)     | NO   |     | NULL    |                |
| table_id     | int(15)     | NO   |     | NULL    |                |
| reserve_date | date        | NO   |     | NULL    |                |
| reserve_time | varchar(50) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

these are the values inserted as the user tries to reserve
+------------+-----------+----------+--------------+--------------+
| reserve_id | member_id | table_id | reserve_date | reserve_time |
+------------+-----------+----------+--------------+--------------+
|         21 |         1 |        3 | 2014-05-01   | Lunch        |
|         22 |         1 |        3 | 2014-05-01   | Lunch        |
|         23 |         1 |        4 | 2014-05-01   | Dinner       |
|         25 |         1 |        3 | 2014-02-02   | Lunch        |
+------------+-----------+----------+--------------+--------------+

what i want is if the member id of 1, reserve table of id "3 " on date 1-05-2014 for lunch and if member id  of "2" also tries to reserve it should nt be reserved how to do ?

[iamthwee]

What what you're saying is member_id = 1 should have priority over member_id =2

[hiiiiii@]

ya i need priority also and if it is already booked it should not be reserved by other member

[diafol]

check for a booking at that date and period and for that table.

What you really want to do (I assume) is have a table to show data for a particular date and period (e.g. selected by dropdown fields). This shows available tables - so bookings can be made for empty tables or bookings can be deleted or bookings can be moved between tables.

Also be aware that restaurants usually take booking times not periods, as a period may have more than one "sitting". It's also useful for the restaurant to know if a table was booked at 6.30 for example, then they could release it to walk-in customers at 7.00 if nobody had turned up.

[hiiiiii@]

yes how to implement it .if a user enters the date the list of available time and tables should be listed ..

[diafol]

Many ways to do it. If you go down the "times" route, then you need to be aware that although say 7.30 may be free, a booking may have been made for 6.30 or 8.30 - in which case a 7.30 booking for a particular table would be most unwise. In this case you'd need to adapt your search for window either side of the target time (maybe 2 hours either side). It all depends on how complicated you need this to be.

[hiiiiii@]

im doing it in simple manner .the one tabel say T1 table can be booked for lunch and dinner .if t1 is booked for lunch on 2-05-2014 then it should not be booked by other user when he tries to book on same date same time ..it can be booked for dinner ..when the user tries to book it shoul display an error.

[urtrivedi]

I think you should put two timestamp columns book_from and book_upto
So that it will help you to easily query any conflict in booking very easily.

[hiiiiii@]

ook but i have certain constraints that t1,t2,t3 are the names/table number and it can be booked only for two periods "lunch or dinner" for any dates.before user click to reserves it should check the weather the particular table is available at the particular time(Lunch/dinner)

[hiiiiii@]

and also
i want the query how to display the available table, date and time to the user so that they can select the available one to reserve

[diafol]

You'll need a tables table:

Tables

table_id INT/PK
table_label CHAR/VARCHAR
periods TINYINT 1 (1=lunch,2=dinner,3=lunch and dinner)

Bookings

booking_id INT/PK
customer_id INT/FK
staff_id INT/FK
book_made_date DATETIME or INT (for unix timestamps)
book_for_date DATE
period TINYINT 1 (as for periods above)
[table_id INT/FK] - see below

Allowing for the booking of multiple tables in one booking - otherwise add table_id to the Bookings table above

BookingTables

bookingtables_id INT/PK
booking_id INT/FK
table_id INT/FK

For checking tables available at a particular date and period (assuming one table per booking):

SELECT tables.* FROM tables 
    LEFT JOIN bookings 
        ON bookings.table_id = tables.table_id 
    WHERE bookings.book_for_date = '$dateToCheck' 
        AND tables.periods & $periodToCheck 
        AND bookings.period != $periodToCheck

Off the top of my head without testing.

[hiiiiii@]

no it is not working .

[hiiiiii@]

i have two tables
table **
tableid
tablename
**RESERVE
id
tableid
memberid
bookdate
time(lunch/dinner)

table 
+----------+------------+
| table_id | table_name |
+----------+------------+
|        3 | RT1        |
|        4 | RT2        |
|        7 | RT3        |
+----------+------------+

+------------+-----------+----------+--------------+--------------+
| reserve_id | member_id | table_id | reserve_date | reserve_time |
+------------+-----------+----------+--------------+--------------+
|         21 |         1 |        3 | 2014-05-01   | Lunch        |
|         22 |         1 |        3 | 2014-05-01   | Lunch        |
|         23 |         1 |        4 | 2014-05-01   | Dinner       |
|         25 |         1 |        3 | 2014-02-02   | Lunch        |
|         27 |         1 |        3 | 2014-05-01   | Lunch        |
|         28 |         1 |        3 | 2014-05-01   | Lunch        |
|         29 |         1 |        3 | 2014-05-01   | Lunch        |
|         30 |         1 |        3 | 2014-05-01   | Lunch        |
|         31 |         1 |        7 | 2014-03-02   | Lunch        |
+------------+-----------+----------+--------------+--------------+

so far what ever data the user enters it will be inserted into the reserve table.multiple values are stored in reserve table .
i dont want to accept multiple values .here table id 3 is booked for lunch twice here for same date it should not be inserted what is the solution for this ..

[diafol]

The purpose of 1 or 2 for Lunch / Dinner (you couldn't have 3 in the bookings table - it wouldn't make sense) is that it takes less space (memory) and it's normalized. SO if you wanted to add a breakfast at a later time, you'd give it a value of 4.

Anyway, you seem to have your own structure and are happy with this.

If you've called the tables table 'table' you MUST backtick it in your queries because it is a reserved word - https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

SELECT `table`.* FROM `table` 
    LEFT JOIN reserve 
        ON reserve.table_id = `table`.table_id 
    WHERE reserve.reserve_date = '$dateToCheck' 
        AND reserve.reserve_time != '$periodToCheck'

Not tested.

[hiiiiii@]

no row is retrieved .this is not working is there any other solutions

[diafol]

No, I messed up. How about this...

SELECT `table`.* FROM `table` 
    WHERE table_id NOT IN (SELECT table_id FROM reserve WHERE reserve.reserve_date = '2014-04-02' 
        AND reserve.reserve_time = 'dinner')

So from in PHP, something like:

SELECT `table`.* FROM `table` 
    WHERE table_id NOT IN (SELECT table_id FROM reserve WHERE reserve.reserve_date = '$date' 
        AND reserve.reserve_time = '$time')

Now that does work - tested

[hiiiiii@]

thank you its working .thank a lot Mr diafol.

[diafol]

OK, is this solved or is there more to it?

[hiiiiii@]

ya i need to display the result (the available tables) in a combo box how to do .

<?php
    //Start session
    session_start();
    require_once('connection/config.php');
    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    if(!$link) 
    {
        die('Failed to connect to server: ' . mysql_error());
    }

    //Select database
    $db = mysql_select_db(DB_DATABASE);
    if(!$db) 
    {
        die("Unable to select database");
    }

    //Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) 
    {
        $str = @trim($str);
        if(get_magic_quotes_gpc()) 
        {
            $str = stripslashes($str);
        }
        return mysql_real_escape_string($str);
    }


    $date = clean($_POST['date']);
    $time = clean($_POST['time']);

if (isset($_GET['id']))
{

        //get user id
        $id = $_GET['id'];
        $result=mysql_query( "SELECT `tables`.* FROM `tables` 
    WHERE table_id NOT IN (SELECT table_id FROM reserve WHERE reserve.reserve_date = '$date' 
        AND reserve.reserve_time = '$time')");
        echo "the list of available tables \n";
         while($row=mysql_fetch_array($result))
         {

         echo "<select><option value=$row[table_id]> $row[table_name]"; 
         }

    }

only one value is displaying in the combo box .

[diafol]

First off, this code looks to be 5 years+ out of date. You should use mysqli or PDO. Here's a mysqli version:

<?php
    session_start();
    $select = '';    
    if($_POST)
    {
        require_once('connection/config.php');
        $db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

        $date = $_POST['date'];
        $time = $_POST['time'];

        $result= $db->prepare( "SELECT `tables`.* FROM `tables` WHERE table_id NOT IN (SELECT table_id FROM reserve WHERE reserve.reserve_date = ? AND reserve.reserve_time = ?)");
        $result->bind_param("ss",$date,$time);
        $result->execute(); 

        while($row = $result->fetch(MYSQLI_ASSOC)) $select .= "<option value='{$row['table_id']}'>{$row['table_name']}</option>"; 
    }
?>

<select name="freetables">
<?php echo $select;?>
</select>

Not tested. Not sure about the $_GET['id'] - doesn't seem to be used so I took it out.

[hiiiiii@]

ya i used the id to get the member id.im not geting the output ..
i just echoed the table name so im geting the result. i have attached the file please check .i need to display the table name in combo box or else suggest any other way

[hiiiiii@]

hi Mr diafol i have managed to do that. i have another doubt that the reservation can be done one month before so i should display only the dates for one month not other than one month from now how to do if the user enters next month it should give error.

[diafol]

check the date against date + 1 month to see if it's greater. Have a look at the DateTime object:

$now = date('Y-m-d');
$date = new DateTime();
$date->add(new DateInterval('P1M'));
$check = $date->format('Y-m-d');

if($check > $now) //raise error

Something like that I think. You may need to convert your date format mm-dd-yyyy from your form to Y-m-d though

[hiiiiii@]

i have the date input
<input type=date name =date>
so its showing all the dates past and present date ( i can select 2012 and also 2015)
in my case it should show only 10 dates or one month dates from todays date .
how to do this ?

[diafol]

I think I'm edning up doing most of the work here hiiiii@. How about you research this? Placing a limit on a html5 datepicker.

You can stipulate min and max properties. You can set the min to today and the max to today+1month from php

[hiiiiii@]

ok will try .sorry to distrub u ..

[diafol]

Not disturbing me, just think that you could do some active learning. :)

[hiiiiii@]

i tried to do but i couldnt get .how to display the present dates and 30 days from today if user tries to enter date above 30 or below 30 error should be display .