[opera997]

how can i reserve a place (table) in a restaurant using php/mysql!! User must be able to see what's aviable and then simply reserve a place. (sorry for my english!) . For example I got 20 tables and each table can be reserved once per evening. Custumer id, table id will be used as Primary key and date/time will be also registered.

Can anyone help me with this?
Thanks

[blaw]

Hi there,

Originally Posted by opera997

how can i reserve a place (table) in a restaurant using php/mysql!! User must be able to see what's aviable and then simply reserve a place. (sorry for my english!) . For example I got 20 tables and each table can be reserved once per evening. Custumer id, table id will be used as Primary key and date/time will be also registered.

Can anyone help me with this?
Thanks

So each table info (i.e. location within the restaurant, capacity, etc) makes up one record, correct? I would use a flag (boolean) field to indicate the table's availability then.

IsReserved TINYINT(1) NOT NULL default '0';

Something like the above. Then, display the tables that have value of '0' (=false) for the customers to reserve. When a customer reserves the table, update the flag value to "1" (=true) so that other customers will never see it for the rest of the day.

Then, every night after the "deadline" for reservation for the night, I would run a script to archive that day's reservation info (for stat or bookkeeping purposes - whatever the reason it is for your restaurant) via cron. I would make this script such that it will "clean up" that day's customer info (empty the cutomer ID fields and other user-input fields, as well as set IsReserved filed back to "0").

Alternatively, you can make two tables - tblTABLE and tblRESERVATION (one-to-many relationship). This way, instead of "cleaning up" the table every night, you can leave them there. Just limit one entry per-table per-night by examining TableID and Date in tblRESERVATION. But I guess this is a bit more clumsy and I don't see the point of leaving the past data like that in the online database.

Hope this helps. =)

[opera997]

hi
Thanks for your help. Your ideas are very useful. I'm not very experienced with php programming. So what i really need is an good example script i think. I know little bit of this and a little bit of that but at the moment, i cant put an script together.
I have already tables "Customers", "Tabels", "Reservations".
My idea was :
* User opens the reservations page
* He/she sees which tables are available (So i need a query for selecting avaible tables and display matching rows on screen)
* then the user enters the customer id, date time, table number
* if the table already reserved, user get an error message
* if not reserved, user gets an confirmation message
and thats it
I have to search the net again ( i already searched 2 weeks for an good example but still not a good one) i think.

Thank you again for your time/help.

[blaw]

Hi there,

From marketing point of view, it is not recommended that you show all the tables available for the customers to "try to" reserve even if some of them have already been reserved by then - why not show them as "Reserved" or don't show the reserved tables at all? It's a little dissapointing to find out AFTER entering my c_id that the table had been already reserved...

If you have [Customer] +-< [Reservation] >-+ [Table] , I assume that there is going to be NO t_num in Reservation on any given day unless a customer books it with his/her c_id within that day. If this is the case, you need to look for t_num that does NOT have an entry in Reservation for today. In that case, if you build an SQL like this:

Code:

SELECT t_num FROM Table WHERE t_num NOT IN (SELECT t_num FROM Reservation WHERE r_date = CURDATE());

# Table (t_num[PK])
# Reservation (r_date[PK], t_num[PK/FK], c_id[FK])
# Customer (c_id[PK]) -- Actually we don't even need this for this particular example to work.

You can retrieve those tables that do not have entries in Reservation for today, which means they are available for reservation for today.

HOWEVER, in the above example, I used subquery, which I thought would only work on MySQL 4.1 or later.

If your MySQL is older than that, then you can split them into two SQL and compare the output after retrieving them with PHP or something. There may be a single query to retrieve what you want without subquery, but it's 5:00 in the morn where I am and I can't think straight right now.

One more thing - if you need to show all the tables anyway and then the avilability, simply use the subquery (the one in the brackets after "NOT IN"). This will get you all the table IDs (t_num) that are RESERVED for tonight. Put these into an array and show the availability by examining each table ID to see if it exists in the array (i.e. use in_array() or something).

Code:

// $a_tables = array containing 20 table IDs.
// $a_reserved = array containing the mysql result of the subquery as each value.
for ($i = 0; $i < count($a_tables); $i++) {
	// If this table ID is found in reserved tables for tonight, show 'RESERVED' else 'AVAILABLE'
	$is_res = (in_array($a_tables[$i], $a_reserved) ? 'RESERVED' : 'AVAILABLE');
	echo '<tr><td>'.$a_tables[$i].'</td><td>'.$is_res.'</td></tr>';
}

This should (I did not test it at all.. sorry!) output HTML rows whose 1st column contain table ID and the 2nd if available or not.
Hope this helps. Good night. =)