StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, you should separate your database slightly, you should have four tables. <code>rooms</code>, <code>prices</code>, <code>clients</code> and <code>bookings</code>. Setup somewhat like this...</p> <p><code>rooms</code> should have the following fields: <code>id</code>, <code>name</code> and <code>description</code>.</p> <p><code>prices</code> should have the following fields: <code>id</code>, <code>price</code>, <code>room_id</code> and <code>day</code>.</p> <p><code>clients</code> should have the following fields: <code>id</code> and whatever else you want to store on the user, such as first and last names, phone number or whatever.</p> <p><code>bookings</code> should have the following fields: <code>id</code>, <code>client_id</code>, <code>room_id</code>, <code>started_at</code> and <code>ended_at</code>. Preferably the <code>started_at</code> and <code>ended_at</code> fields will be an int, filled with PHP's <code>time()</code> method.</p> <p>You can add any extra fields you want/need to the tables.</p> <p>With the tables separated out like this, you will be able to properly query the database. So to answer your questions...</p> <ol> <li><p>look at the date of room in use... You can now query to see if a room is in use on a specific date by doing the following...</p> <pre><code><?php $selectedDate= mktime(); // Create a UNIX timestamp based on the day the user selected. $query = "SELECT r.name, r.description FROM rooms r, bookings b WHERE b.room_id = r.id AND b.started_at < $selectedDate OR b.ended_at > $selectedDate"; $result = $pdo->query($query); ?> </code></pre></li> <li><p>Look up cost for that date</p> <pre><code><?php $selectedDate = mktime() // Create a UNIX timestamp based on the day the user selected $dayOfWeek = Date('N', $selectedDate); // This will give the numerical day of the week. $query = "SELECT price FROM prices WHERE room_id = $roomId AND day = $dayOfWeek"; $result = $pdo->query($query); ?> </code></pre></li> <li><p>Record that unit cost in the client table</p> <p>Doing this is just silly on account of already having the information in another table. Never recreate the same information in a database. If you are, you have built your database incorrectly.</p></li> <li><p>Grand total in the customer table</p> <p>Again, silly... don't recreate data...</p> <ol> <li><p>Though, to find that information out, you would first need to do a query on the bookings table, and see the start and end date for which the user will be occupying the room. </p></li> <li><p>Do a calculation on how long the client will be in the room for, (ended_at - started_at) / 86400, (86400 is the number of seconds in a day) that will give the number of days the client is in the room for. </p></li> <li><p>Now that you know which days, and how long the client will be in the room, you can dynamically create a sql call to select the days of the week you need, remember <code>Date('N', $timeStamp)</code> will give you the numerical day of the week for a given timestamp. </p></li> <li><p>Then it is just a matter of doing simple addition.</p></li> </ol></li> </ol> <p>I have given you the basics here, You can modify the query from answer one to show you if a room is available to be booked within the time frame the user asked for.</p> <p>I hope that covers everything you asked about...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload