PHP MySQL Help

[StuHannah]

Hi All,

 

I am starting to look at building a web based allocation to allow staff to book out our Minibus'.

 

The easy part I have achieved, I have a list of the minibus' on the page in a table on the left hand side for staff to be able to see what we have, but the next bit is that I have to set it up in such a way where there are 8 sessions available for this to be booked, so I have been able to do this and loop through the database results to get what I am after.

 

The problem I am facing now is that we have some permanent bookings that need to the made to the system, I was thinking of having a table with the session number and details about the booking and then my normal tables which hold the bookings. The part I am stuck at is, how can I take data from the static table and populate it before then allowing staff to be able to book it.

 

Hope that all makes sense.

 

S.

[ginerjm]

No it doesn't.

 

So - you're trying to setup bookings.  That would involve the resource id (which bus), the client id (the booker), the datetime for beginning and end, plus other things.  If you want to make permanent bookings, ie, reservations, simply make the booking with a unique client id (you) and the bus id, and the times you want to make the bus unavailable for others to use because you are using it.

[StuHannah]

Sorry about that, I'll try and make it clearer now.

 

So basically, we have several minibus' we use and staff book them out using a diary, very old school. I want to replace this now. We plan to split the timing into slots, so we could have around 8 slots that we can book the minibus’ out to.

 

There will be a table called 'tbl_vehicles'

 

VehicleID

VehicleReg

VehicleCapacity

 

The next table will be the list of static bookings that are updated annually, I can manually do these as it’s typically specific times, such as Friday Afternoon, Tuesday Morning.

 

This table will be named ‘tbl_static’

 

ID

Day

Slot

Name

VehicleID

 

The plan would be that before the system is used I would pre-populate any static bookings into the backed and then from the client end these would be greyed out and not be able to book them.

 

The next table would be ‘tbl_bookings’ which would be similar to the tbl_static except this will contain the bookings that have been made and we can then run reports on that table.

 

Hope that makes sense now, and I hope this is achievable, don’t worry I am not seeking the full answer, just hoping what I want to work on makes sense.

 

Stuart

[ginerjm]

So that is pretty much what I suggested except in my approach the bookings are all in one table.  You may have to setup a rule for how far in advance you can make bookings and then each day run a cron job to post the 'permanent' bookings I referred to onto the new day that opens up every day.  This could use your static table as the source for the new bookings but any querying would be done from only one table.

[Psycho]

I would go with one of two approaches.

 

Option A:

1) I would call the static table 'recurring' as this is similar to most calendar schedules.

2) In the bookings table have a column for a 'recurring' id.

3) In the recurring table add columns for a begin date and an end date rather than updating every year.

3) When someone saves a recurring booking have that process add/update the (future) individual records to the normal booking table and include the recurring id. Likewise, if you ever delete a recurring booking have it delete all the future bookings associated with that id. This simplifies the process of pulling the data to show the current booking and available slots. Also, when scheduling a recurring booking the system can identify if there are any existing conflicts that may be resolved.

 

Option B:

Keep the two tables completely separate. When pulling the data for a particular period you will need to pull data from both tables and cross-index when producing the output. I don't know what your process looks like now, but here's a rough idea of how it could be accomplished.

 

1) Pull the data from the recurring table and populate the data into an array in a logical format. E.g. $recurring[day][slot][vehicle] = id. The format would likely need to align with how you process the individual bookings.

 

2) Pull the data from the bookings table and put them into a similarly structured array.

 

3) Loop through the days you want to produce with sub loops for slot and vehcile. On each iteration check if there is a corresponding record in either table and, if so, display the relevant content.

 

 

$recurring = array();
while($row = $recurringQuery->fetch())
{
    $recurring[$row['day']][$row['slot']][$row['vehicle']] = $row;
}
 
$bookings = array();
while($row = $bookingsQuery->fetch())
{
    $bookings[$row['day']][$row['slot']][$row['vehicle']] = $row;
}
 
//Loop through each 'day' to be displayed. I'm using an array, but
//this would probably be creates some other way
foreach($days as $day)
{
    //Loop over each slot
    for($slot=1; $slot<=8; $slot++)
    {
        //Loop over each vehicle id/descriptor
        foreach($vehicles as $vehicle)
        {
            //Check if there is a record in the booking table
            if(isset($bookings[$day][$slot][$vehicle]))
            {
                //Get booking data and format output
                $data = $bookings[$day][$slot][$vehicle];
            }
            //Check if there is a record in the booking table
            if(isset($recurring[$day][$slot][$vehicle]))
            {
                //Get booking data and format output
                $data = $recurring[$day][$slot][$vehicle];
            }
            //Check if there is a record in the booking table
            else
            {
                //Slot is available, create applicable output
            }
        }
    }
}

[StuHannah]

Thank you both for your replies, definetly on the right track now.

 

Stuart