Sthurley Sthurley - 25 days ago 20
MySQL Question

Displaying a day of bookings for a room

i am working on a school room booking system but i am having some trouble outputting the bookings.

Public function displayRoomByDay() {
$query = DB::GetInstance()->query("SELECT B.roomid, B.period, R.roomname, B.bookingdate, B.bookingid FROM booking B INNER JOIN room R on b.roomid = r.roomid WHERE bookingdate = '2016-11-03' and b.roomid=1 ORDER BY b.period"); //Inner join
$count = $query->count();
$freeCount = 0;
for($periods=1;$periods<=6;$periods++) {
for($x=0;$x<$count-1;$x++) {
$outputted=false;
$freeCount=0;
do {
if($query->results()[$x]->period == $periods) {
echo $query->results()[$x]->period . '<br>';
$outputted=true;
} else {
echo 'FREE' . '<br>';
$freeCount = 1;
}
} while($outputted = false and $freeCount=0);
}
}
}


This is the function that i use to output my data. My SQL query returns two items, a booking in period 5 and a booking in period 1 (i have tested this through PHPMyAdmin). I am trying to use nested for loops and a do while loop to loop through the periods available in a day (6). From there i loop through the two bookings that my sql query returns which is this code:

for($x=0;$x<$count-1;$x++) {
$outputted=false;
$freeCount=0;
do {
if($query->results()[$x]->period == $periods) {
echo $query->results()[$x]->period . '<br>';
$outputted=true;
} else {
echo 'FREE' . '<br>';
$freeCount = 1;
}
} while($outputted = false and $freeCount=0);
}


However when i run my page, i get 1 FREE FREE FREE FREE FREE, when i am trying to get 1 FREE FREE FREE FREE 5 as they are when the bookings are.

Answer

Your logic is very complicated for this task. You can simplify it like:

public function displayRoomByDay()
{
    $query = DB::GetInstance()->query("
        SELECT B.roomid,
               B.period,
               R.roomname,
               B.bookingdate,
               B.bookingid
        FROM booking B
        INNER JOIN room R 
           ON b.roomid = r.roomid
        WHERE bookingdate = '2016-11-03'
          AND b.roomid=1 ORDER BY b.period"); //Inner join

    $results = array_reduce($query->results(), function ($carry, $item) {
        $carry[$item->period] = $item;
        return $carry;
    }, []);

    $periods = range(1, 6);

    foreach ($periods as $period) {
        if (isset($results[$period]) {
            echo $period . '<br />';
        } else {
            echo 'FREE' . '<br />';
        }
    }
}

I think that's all you need.

Comments