Barand

Open your browser's develop tools. Click on "Network" tab Click on one of the rows to view its headers FYI, when using date(), the default time is the current time, so this will suffice... <?= date("d-M-Y H:i:s") ?> BST

You can save MSWord docs as html. In this case, equations are saved as image files in a subfolder EG

Note that you are outputting Hours:seconds and not hours:minutes!

Safe browsing dictates never to accept links from strangers, so please post code (in a code block using <> button). The usual reason for a header () to fail is because output has already been sent to the browser (even if it is only whitespace) at the time of the call. Check this is not the case. Also make sure you call exit() after the call so there is no unwanted subsequent processing of the page. If the above is the reason you should have received an error message - do you have error reporting ON?

On the subject of "loose typing of variables", version 8 of PHP is stricter than earlier versions. It could be that be that the failure now is due to an upgrade to this version from an old version. What error messages are you getting?

You can do it SQL when you load the CSV data into MySQL using the STR_TO_DATE() function to reformat the dates // CSV INPUT $input = 'Tom DiCanari,28/Apr/24 11:50 AM,29/Apr/24 5:29 PM'; // create tete table $pdo->exec("CREATE TABLE IF NOT EXISTS test1 (name varchar(50), timefrom datetime, timeuntil datetime)"); // process the input $stmt = $pdo->prepare("INSERT INTO test1 (name, timefrom, timeuntil) VALUES (?, STR_TO_DATE(?, '%d/%b/%y %h:%i %p'), STR_TO_DATE(?, '%d/%b/%y %h:%i %p'))"); $data = str_getcsv($input); $stmt->execute($data); then mysql> SELECT name -> , timefrom -> , timeuntil -> , timestampdiff(MINUTE, timefrom, timeuntil) as mins_diff -> FROM test1; +--------------+---------------------+---------------------+-----------+ | name | timefrom | timeuntil | mins_diff | +--------------+---------------------+---------------------+-----------+ | Tom DiCanari | 2024-04-28 11:50:00 | 2024-04-29 17:29:00 | 1779 | +--------------+---------------------+---------------------+-----------+

Some example code for you // INPUT DATES $col1 = '28/Apr/24 11:50 AM'; $col2 = '29/Apr/24 5:29 PM'; // FORMAT DEFINITIONS $jira = 'd/M/y h:i a'; $SQLformat = 'Y-m-d H:i:s'; $dt1 = DateTime::createFromFormat($jira, $col1); // create DateTime object $dt2 = DateTime::createFromFormat($jira, $col2); $d = $dt1->diff($dt2); // create DateInterval object echo $dt1->format($SQLformat) .'<br>'; // output dates echo $dt2->format($SQLformat) .'<br>'; printf("%d days %d hr %d min", $d->days, $d->h, $d->i); // show difference Output... 2024-04-28 11:50:00 2024-04-29 17:29:00 1 days 5 hr 39 min

Check the content of your arrays. It looks like $allowed_forums contains a blank entry.

There are about a dozen array keys in that picture. If you want help... Be specific. Post textual code in a code block (<> button) and not a useless picture.

By now you should have learned to place code inside a code block! I'm happy try and help. Any data tables I already have from you are several years out of date and does not include the following tables used in your code: subject_position dummy arms_level promotion I would, therefore, require an up-to-date DB dump. (I'll PM you with my email address). Also - what MySQL version are you using?

FYI Here is a simpler non-recursive solution... $cat_data = []; $res = $pdo->query("SELECT id , CONCAT(name, '::', position) as name , parent FROM category "); foreach ($res as $r) { $cat_data[$r['id']] = [ 'name' => $r['name'], 'parent' => $r['parent'] ] ; } $breadcrumbs = retrieve_category_path(552, $cat_data); echo $breadcrumbs; function retrieve_category_path($id, &$cats) { if (!isset($cats[$id])) { return "Unknown category"; } $category_path_array = []; do { array_unshift($category_path_array, $cats[$id]['name']); $id = $cats[$id]['parent']; } while ($id); return join(' / ', $category_path_array); }

We want to add a new element to the $cpa array in each call to the function. To do this we need always to add to the original empty array. The ampersand allows us to to this. Without it, a copy of the array would be passed to the function and we would just keep adding to a new empty array each time. &$cpa passes the array by reference (ie its address in memory) instead of a copy. P.S. This method below (which stores all the category data into a $cat_data array instead of running a query in every call to the function, is 5x faster. $cat_data looks like this... Array ( [532] => Array ( [name] => Motorbikes::1 [parent] => 0 ) [533] => Array ( [name] => Cars::2 [parent] => 0 ) [534] => Array ( [name] => Boats::3 [parent] => 0 ) [535] => Array ( [name] => Bicycles::4 [parent] => 0 ) . . . ) CODE $cat_data = []; $res = $pdo->query("SELECT id , CONCAT(name, '::', position) as name , parent FROM category "); foreach ($res as $r) { $cat_data[$r['id']] = [ 'name' => $r['name'], 'parent' => $r['parent'] ] ; } $category_path_array = []; retrieve_category_path ($cat_data, 552, $category_path_array); $breadcrumbs = join('/', $category_path_array); echo $breadcrumbs; function retrieve_category_path (&$cats, $id, &$cpa) { array_unshift($cpa,$cats[$id]['name']); if ($cats[$id]['parent']) { retrieve_category_path($cats, $cats[$id]['parent'], $cpa); } } Thank you - much appreciated.

After calling your recursive array you need finish up with a single array - you output a separate array during each iteration. Don't connect to db inside functions - it's the slowest part of the process and inefficient (plus you can quickly reach your connctions limit). Connect once then pass the connection to the functions. Don't include the separator in you results - add that when you implode (join). (Sorry for any delay - it's not easy to load an image of data into a database test table) My method... function retrieve_category_path($pdo, $id, &$cpa){ // DB connection // Company ID $stmt = $pdo->prepare("SELECT CONCAT(name, '::', position) as name , parent FROM category WHERE id = ? "); $stmt->execute([$id]); while($row=$stmt->fetch()){ $parent =$row['parent']; $name = $row['name']; $cpa[] = $row['name']; // append into array if($row['parent'] > 0){ retrieve_category_path($pdo, $row['parent'], $cpa); } } } $category_path_array = []; retrieve_category_path($pdo, 552, $category_path_array); $breadcrumbs = join('/', array_reverse($category_path_array)); echo $breadcrumbs; // Cars::2/Sports cars::1/Petrol::1/2 Door::0

Instead of the above final line, can you post something processable such as var_export($category_path_array) Or, better still, a dump of your test data (struture and data) so we can reproduce the array ourselves. (The more you help us the more we can help you)

I was going to suggest mysql> select * from tprojects; +-----------+---------+----------+------------+ | IdProject | service | IsActive | start | +-----------+---------+----------+------------+ | 6 | 123 | 1 | 2024-05-01 | | 7 | 123 | 1 | 2024-05-02 | | 8 | 123 | 1 | 2024-05-03 | | 9 | 123 | 1 | 2024-05-04 | | 10 | 123 | 1 | 2024-05-05 | | 11 | 123 | 1 | NULL | | 12 | 123 | 1 | NULL | | 13 | 123 | 1 | NULL | | 14 | 123 | 1 | NULL | | 15 | 123 | 1 | NULL | | 16 | 123 | 1 | NULL | | 17 | 123 | 1 | NULL | +-----------+---------+----------+------------+ mysql> SELECT COUNT(IdProject) as Sales -> , DATE_FORMAT(start, '%M %Y') AS Month -> FROM tprojects -> WHERE IsActive = '1' -> AND Service = 123 -> AND MONTH(start) IS NOT NULL -> GROUP BY month; +-------+----------+ | Sales | Month | +-------+----------+ | 5 | May 2024 | +-------+----------+

Are you also doing something silly like storing your "start" column as type VARCHAR instead of type DATE?

Execute your query in phpmyadmin. What do you get?

That seems to suggest you have 61 entries with an invalid date (null) and 5 entries for month 5 (may). I don't understand where "Count" index is coming from in that results array as the count() has an column alias "Sales".

To get the month in your results you need to select it. Your query only selects the count.

"o" and "v" are table aliases for the option and vote tables. First I made a couple of additions to the tables... Poll table - added "polling_day" date column (so we now know the current poll when voting) Vote table - added unique index on user_id/option_id to prevent double-voting. CREATE TABLE `vote` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `option_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_vote_user_opt` (`user_id`,`option_id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `poll` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `polling_day` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; The sample code below uses a query of the poll_option table to get the options for the current poll and generates the checkboxe for the form. The checkboxes are names "vote[]" so they are posted as an array. Note that only "checked" checkboxes are posted so you just need to loop through the array of $POST['vote'] values and insert a vote record for each (with the id of the logged-in user. CODE <?php require 'db_inc.php'; // USE YOUR OWN ... $pdo = mdbConnect('db1'); // ... CONNECTION CODE $_SESSION['user_id'] = 123; // emulate user being logged in - testing only. ################################################################################ ## Handle posted form data ################################################################################ if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Use "INSERT IGNORE ... " so attemmpts to vote twice for an option are ignored $stmt = $pdo->prepare("INSERT IGNORE INTO vote(user_id, option_id) VALUES (?, ?)"); foreach ($_POST['vote'] as $oid) { $stmt->execute( [ $_SESSION['user_id'], $oid ] ); } header("Refresh: 0"); // reload form exit; } ################################################################################ ## Build arrays of vote options for the current poll ################################################################################ $res = $pdo->query("SELECT o.id, o.opt FROM poll_option o JOIN poll p ON o.poll_id = p.id WHERE p.polling_day = CURDATE() ORDER BY opt "); $options = array_column($res->fetchAll(), 'opt', 'id'); if (empty($options)) { exit ("Today is not a polling day"); } ################################################################################ ## Build check box list for the form ################################################################################ $chkboxes = ''; foreach ($options as $val => $label) { $chkboxes .= "<input type='checkbox' name='vote[]' value='$val'>&ensp;$label<br>"; } ?> <!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'> </style> </head> <body> <form method='POST'> <div class='w3-margin w3-padding w3-border'> <h3>Select your preferences</h3> <?=$chkboxes?> <br> <input type='submit'> </div> </form> </body> </html>

I prefer to use a function to build the options from an array (or query results), creating the html for the options and selecting the appropriate item. An example is below. DATA USED RESOURCE USER +----+--------------------+ +----+----------+--------------+ | id | description | | id | username | fullname | +----+--------------------+ +----+----------+--------------+ | 1 | Car - C43 GBT | | 1 | tess | Tess Tickell | | 2 | Van - D76 GBT | | 2 | hugh | Hugh Jass | | 3 | Minibus - MN11 BUS | | 3 | tom | Tom DiCanari | +----+--------------------+ | 4 | lucy | Lucy Lastik | | +----+----------+--------------+ | | +---------+ | | +---------------------+ | | BOOKING | | +----+-------------+---------------------+---------------------+---------+ | id | resource_id | book_start | book_end | user_id | +----+-------------+---------------------+---------------------+---------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | 1 | | 2 | 1 | 2024-05-13 12:00:00 | 2024-05-13 17:00:00 | 2 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | 3 | | 5 | 2 | 2024-05-13 11:30:00 | 2024-05-17 18:00:00 | 4 | +----+-------------+---------------------+---------------------+---------+ OUTPUT EXAMPLE CODE EXAMPLE <?php require 'db_inc.php'; // USE YOUR OWN ... $pdo = mdbConnect('db1'); // ... CONNECTION CODE ################################################################################ ## Build arrays of dropdown options ################################################################################ $res = $pdo->query("SELECT id, description FROM resource ORDER BY description "); $results = $res->fetchAll(); $resources = array_column($results, 'description', 'id'); $res = $pdo->query("SELECT id, fullname FROM user ORDER BY fullname "); $results = $res->fetchAll(); $users = array_column($results, 'fullname', 'id'); /*************************************************************************** * function to generate menu options from array and select current value * * @param array $optArray Array of menu options * @param mixed $current Currnt value to be selected */ function menuOptions(&$optArray, $current='') { $opts = "<option value=''>- select -</option>\n"; foreach ($optArray as $val => $txt) { $sel = $val==$current ? 'selected' : ''; $opts .= "<option $sel value='$val'>$txt</option>\n"; } return $opts; } ################################################################################ ## Build html table for sreen display ################################################################################ $res = $pdo->query("SELECT b.id as bid , r.id as rid , u.id as uid , date_format(b.book_start, '%b %d - %h:%i%p') as stime , date_format(b.book_end, '%b %d - %h:%i%p') as etime FROM booking b JOIN user u ON b.user_id = u.id JOIN resource r ON b.resource_id = r.id ORDER BY rid, book_start, book_end "); $tdata = ''; foreach ($res as $r) { $tdata .= "<tr> <td>{$r['bid']}</td> <td><select name='resid'>" . menuOptions($resources, $r['rid']) . "</select></td> <td><select name='userid'>" . menuOptions($users, $r['uid']) . "</select></td> <td>{$r['stime']}</td> <td>{$r['etime']}</td> </tr> "; } ?> <!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: 8px; } th { border: 1px solid white; background-color: black; color: white; } </style> </head> <body> <header class='w3-indigo w3-padding'> <h1>Dropdown Menus</h1> </header> <table border='1'> <tr><th>ID</th> <th>Item</th> <th>User</th> <th>From</th> <th>Until</th> </tr> <?=$tdata?> </table> <br> <?=pdo2text($pdo, "select * from resource")?> </body> </html>

Added user table and also added user_id to booking table records... USER +----+----------+--------------+ | id | username | fullname | +----+----------+--------------+ | 1 | tess | Tess Tickell | | 2 | hugh | Hugh Jass | | 3 | tom | Tom DiCanari | | 4 | lucy | Lucy Lastik | +----+----------+--------------+ BOOKING +----+-------------+---------------------+---------------------+---------+ | id | resource_id | book_start | book_end | user_id | +----+-------------+---------------------+---------------------+---------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | 1 | | 2 | 1 | 2024-05-13 12:00:00 | 2024-05-13 17:00:00 | 2 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | 3 | | 5 | 2 | 2024-05-13 11:30:00 | 2024-05-17 18:00:00 | 4 | +----+-------------+---------------------+---------------------+---------+ I also added styles for each user name to define background colours. I used a couple of SQL WINDOW functions to the query the row_number() so I know which is the first cell of each booking (That's the only on we need to output) the row count() so we know how many rows the cell should span. 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 as id , u.fullname as booked_by , u.username , t.date , t.starttime , ROW_NUMBER() OVER w1 as slotnum , COUNT(b.id) OVER w2 as slotcount -- -- 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 LEFT JOIN user u ON b.user_id = u.id WINDOW w1 AS (PARTITION BY id, t.date ORDER BY t.starttime) , w2 AS (PARTITION BY id, t.date) ORDER BY starttime, resid "); $res->execute([ $wkcomm ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['resid']] = ['bid' => $r['id'], 'num' => $r['slotnum'], 'span' => $r['slotcount'], 'cust' => $r['booked_by'], 'class' => $r['username'] ]; } ################################################################################ ## 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) { if ($booked['bid']) { if ($booked['num']==1) { $tdata .= "<td class='booked {$booked['class']}' rowspan='{$booked['span']}'>{$booked['cust']}</td>"; } } else { $tdata .= '<td>&nbsp;</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; } .booked { color: white; border: 3px solid #E0E0E0; text-align: center; } .tess { background-color: #A91723; } .hugh { background-color: #54BC54; } .tom { background-color: #EC9807; } .lucy { background-color: #8F1FCF; } </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?>'> &emsp; <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>

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'>&nbsp;</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?>'> &emsp; <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>

Try it and see.

I suggest output like this below. I have used yiur two resources and ny booking data looke like this... mysql> select * from booking; +----+-------------+---------------------+---------------------+ | id | resource_id | book_start | book_end | +----+-------------+---------------------+---------------------+ | 1 | 1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | | 2 | 1 | 2024-05-15 09:00:00 | 2024-05-15 19:00:00 | | 4 | 1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | | 5 | 2 | 2024-05-13 12:00:00 | 2024-05-17 18:00:00 | +----+-------------+---------------------+---------------------+ The output looks lke this for the 2 resources... CODE <?php require 'db_inc.php'; // $pdo = mdbConnect('db1'); // USE YOUR OWN CONNECTION CODE $wkcomm = $_GET['wkcomm'] ?? date('Y-m-d'); $resid = $_GET['resid'] ?? ''; ################################################################################ ## COLUMN HEADINGS ################################################################################ $d1 = new DateTime($wkcomm); $di = new DateInterval('P1D'); $dp = new DatePeriod($d1, $di, 6); $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>"; foreach ($dp as $d) { $theads .= "<th>" . $d->format('D M d') . "</th>"; } $theads .= "</tr>\n"; ################################################################################ ## QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT ################################################################################ $res = $pdo->prepare("-- -- create temporary table containing the dates of the selected week -- WITH RECURSIVE dateslot (n, slotdate) AS ( SELECT 1, ? UNION ALL SELECT n+1 , date_add(slotdate, INTERVAL 1 DAY) FROM dateslot WHERE n < 7 ), -- -- create temporary table containing the daily 30-minute timeslots -- timeslot (n, starttime, endtime) AS ( SELECT 1, '07:00:00', '07:30:00' UNION ALL SELECT n+1 , addtime(starttime, '00:30:00') , addtime(endtime, '00:30:00') FROM timeslot WHERE n < 24 ) SELECT slotdate , time_format(starttime, '%H:%i') as time , b.id -- -- cross join the two temporary tables to give rows for every timeslot for the seven days -- then match these against the bookings to see which slots fall withing the booking range -- for the selected resource -- FROM dateslot d CROSS JOIN timeslot t LEFT JOIN booking b ON CONCAT(d.slotdate, ' ', t.starttime) < b.book_end AND CONCAT(d.slotdate, ' ', t.endtime) > b.book_start AND b.resource_id = ? ORDER BY starttime, slotdate "); $res->execute([ $wkcomm, $resid ]); $data = []; foreach ($res as $r) { $data[$r['time']][$r['slotdate']] = $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'>&nbsp;</td>"; } $tdata .= "</tr>\n"; } ################################################################################ ## FUNCTIONS ################################################################################ function resourceOptions($pdo, $current) { $res = $pdo->query("SELECT id, description FROM resource ORDER BY description "); $opts = "<option value=''>- select resource -</option>\n"; foreach ($res as $r) { $sel = $r['id']==$current ? 'selected' : ''; $opts .= "<option $sel value='{$r['id']}'>{$r['description']}</option>\n"; } return $opts; } ?> <!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'> Week commencing: <input type='date' name='wkcomm' value='<?=$wkcomm?>'> &emsp; Resource: <select name='resid'> <?=resourceOptions($pdo, $resid)?> </select> <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>