Leaderboard
Popular Content
Showing content with the highest reputation on 05/14/2024 in all areas
-
Slightly simpler this way (by resource headings) CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USER YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); ################################################################################ ## COLUMN HEADINGS ################################################################################ $res = $pdo->query("SELECT id, description FROM resource ORDER BY id "); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($res as $r) { $theads .= "<th>{$r['description']}</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare(" -- -- create temporary table containing the daily 30-minute timeslots -- WITH RECURSIVE timeslot (n, starttime, endtime, date) AS ( SELECT 1, '07:00:00', '07:30:00', ? UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') , date FROM timeslot WHERE n < 24 ) SELECT r.id as resid , time_format(starttime, '%H:%i') as time , b.id -- -- cross join the resource table with temp timeslot table to give rows for every timeslot for each resource -- then match these against the bookings to see which slots fall withing the booking range -- for the matching resource -- FROM resource r CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(t.date, ' ', t.starttime) < b.book_end AND CONCAT(t.date, ' ', t.endtime) > b.book_start AND r.id = b.resource_id ORDER BY starttime, resid "); $res->execute([ $wkcomm ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['resid']] = $r['id']; } ################################################################################ ## LOOP THROUGH ARRAY TO CREATE HTML ROWS ################################################################################ $tdata = ''; foreach ($data as $tslot => $slotdata) { $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>"; foreach ($slotdata as $booked) { $class = $booked ? 'w3-red' : ''; $tdata .= "<td class='$class'> </td>"; } $tdata .= "</tr>\n"; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { border-collapse: collapse; width: 100%; } th, td { padding: 4px; } th { border: 1px solid white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Resource Bookings</h1> </header> <form class='w3-light-gray w3-padding w3-margin-bottom'> Date: <input type='date' name='wkcomm' value='<?=$wkcomm?>'>   <button class='w3-blue w3-button'>Refresh</button> </form> <div class='w3-content'> <table class='w3-small' border='1'> <?=$theads?> <?=$tdata?> </table> </div> </body> </html>
1 point
This leaderboard is set to New York/GMT-05:00