Clever Geek Handbook

PHP / MySQL backup system

I am currently working on an equipment reservation system for my school.

This is basically what my tables look like:

tblEquipment:

     id name description
      1 Camera Takes pictures
      2 Projector Projects images
      3 Stereo Plays music

tblEvents:

      id equipmentID start end
       1 2.3 1251312300 1251315900 // Should I use comma delimited entries for equipmentID?   
       2 1 1251312300 1251315900

As far as my project is concerned, I have several questions:

1) If multiple pieces of equipment are reserved (which will happen more times than not), if the “equipment IDs” are comma separated in the equipment ID field?

2) Currently, when a user makes a reservation, they first select their "requested times" and then are presented with available items at that time. This is what I am using for this request:

$start = //user requested time
$start = //user requested time

SELECT equipmentID FROM tblEvents
 WHERE ($start >= start && $start <= end) 
 OR ($end >= start && $end <= end)
 OR ($start <= start && $end >= end

 while($row = mysql_fetch_array($data)) {

  echo $row['equipmentID'];  //Would echo something like: 
  echo "<br>";               //    2,3
                            //    1

 }

My question is:

How can I take the "results" of the above query and then re-query the "tblequipment" table, but exclude the items that were in the "results" above (because they will not be available). Bearing in mind that my query above may return multiple rows.

Any help on this would be great, thanks!

+2


source to share


2 answers


About # 1: No! No no no no no! If you have multiple reserved pieces of equipment, you should have multiple rows in the reservation table (which looks like here tblEvents

). To avoid duplicating other fields in tblEvents

, you usually create a third table, perhaps tblEventEquipment

one that simply shows which equipment belongs to which event.

If you want a comma-separated list for output purposes (which is not possible), you can always generate it with GROUP_CONCAT()

, but inside the table, you want one line to reserve a piece of hardware. Otherwise, SQL cannot effectively (or efficiently) determine which hardware is reserved at a particular time.

Regarding # 2, you need a query like:



SELECT * 
FROM tblEquipment
WHERE NOT EXISTS (
  SELECT 1 
  FROM tblEvents 
  WHERE tblEvents.equipmentID = tblEquipment.equipmentID
    AND $end >= start AND $start <= end
)

Here you select equipment for which there is no redundancy. Note that I have simplified your logic for determining if hardware is reserved or not by doing only two comparisons.

Finally, an unrelated note: I highly recommend storing timestamps as integers in a database table. Use the built-in MySQL type DATETIME

. If you need to convert to / from a timestamp, you can use the UNIX_TIMESTAMP()

and functions FROM_UNIXTIME()

.

+5


source


  • No, don't use comma separated values. If you want the user to be able to check multiple items, you need a new table:

    Tables: Users , Property , Checkout

    The new Checkout table will have the following fields:

    • ID
    • person_id
    • PROPERTY_ID
    • checkout_date
    • checkin_date

    This table can have multiple entries for any particular user. The user can be there once for a company laptop and again for a company projector:

    1 | 12 | 23 | 2009-08-17 | 0000-00-00
    2 | 12 | 28 | 2009-08-17 | 0000-00-00

  • As for checking if an element is reserved, I would like the field in the table property to contain a boolean value:

    is_reserved

    (BOOL)

    Finding available items is nothing more than checking all items with a BOOL value of false and the absence of a check in the table combined with the absence of checkin_date.



+2


source






More articles:


All Articles