Barand

You actually have choice of mysqli or PDO to replace your mysql.* functions. Don't be fooled into thinking the easier route must mysqli because it's spelt almost the same. PDO is the simpler of the two interfaces.

DATA TABLE: mongoose +----+---------+ | id | testcol | +----+---------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 2,3 | | 5 | 1,4 | | 6 | 2,5 | | 7 | 1,3 | | 8 | 2,4 | +----+---------+ Find records in mongoose where testcol contains 1 or 3 ... mysql> SELECT DISTINCT id, testcol -> FROM mongoose -> JOIN ( -> SELECT 1 as n -> UNION SELECT 3 -> ) nums ON FIND_IN_SET(n, testcol) -> ; +----+---------+ | id | testcol | +----+---------+ | 1 | 1 | | 3 | 3 | | 4 | 2,3 | | 5 | 1,4 | | 7 | 1,3 | +----+---------+ edit: The subquery effectively gives you a temporary table "nums" ... TABLE: nums +---+ | n | +---+ | 1 | | 3 | +---+

Table subquery ?

$hours_per_day = 6; $total_hours = 20; $days = floor($total_hours / $hours_per_day); $hours_remaining = $total_hours - $days * $hours_per_day; echo "$days days and $hours_remaining hours" ; //--> 3 days and 2 hours

@Jokeh There's a good chance that the question is no longer relevant after nearly 2 1/2 years

Ensure users have to log in before they can vote so you know their user_id. When they vote, store the poll_id and user_id. Put a unique constraint on (poll_id, user_id) so that combination can not be added twice.

Fixed. What editor are you using? <?php include 'functions.php'; // Connect to MySQL $pdo = pdo_connect_mysql(); // If the GET request "id" exists (poll id)... if (isset($_GET['id'])) { // MySQL query that selects the poll records by the GET request "id" $stmt = $pdo->prepare('SELECT * FROM polls WHERE id = ?'); $stmt->execute([$_GET['id']]); // Fetch the record $poll = $stmt->fetch(PDO::FETCH_ASSOC); // Check if the poll record exists with the id specified if ($poll) { // MySQL query that selects all the poll answers $stmt = $pdo->prepare('SELECT * FROM poll_answers WHERE poll_id = ?'); $stmt->execute([$_GET['id']]); // Fetch all the poll anwsers $poll_answers = $stmt->fetchAll(PDO::FETCH_ASSOC); // If the user clicked the "Vote" button... if (isset($_POST['poll_answer'])) { // Update and increase the vote for the answer the user voted for $stmt = $pdo->prepare('UPDATE poll_answers SET votes = votes +1 WHERE id = ?'); $stmt->execute([$_POST['poll_answer']]); // Redirect user to the result page header ('Location: result.php?id=' . $_GET['id']); exit; } } else { die ('Poll with that ID does not exist.'); } } else { die ('No poll ID specified.'); } ?> <?=template_header('Poll Vote')?> <div class="content poll-vote"> <h2><?=$poll['title']?></h2> <p><?=$poll['des']?></p> <form action="vote.php?id=<?=$_GET['id']?>" onSubmit="disable()" method="post"> <?php for ($i = 0; $i < count($poll_answers); $i++): ?> <label> <input type="radio" name="poll_answer" value="<?=$poll_answers[$i]['id']?>" <?=$i == 0 ? ' checked' : ''?>> <?=$poll_answers[$i]['title']?> </label> <?php endfor; ?> <div> <input type="submit" name="submit" value="Vote"> <a href="result.php?id=<?=$poll['id']?>">View Result</a> </div> </form> </div> <?=template_footer()?>

I created an extra table to define which category the values were in mysql> select * from catval; +-----+------+ | val | cat | +-----+------+ | 1 | 4 | | 2 | 4 | | 3 | 4 | | 4 | 4 | | 5 | 3 | | 6 | 3 | | 7 | 2 | | 8 | 2 | | 9 | 1 | | 10 | 1 | +-----+------+ then $sql = "SELECT a.cat as cata , b.cat as catb FROM datatb d JOIN catval a ON d.grpa = a.val JOIN catval b ON d.grpb = b.val "; $result = $db->query($sql); //categories $cat = [ 4 => ['name'=>'1:4', 'recs'=>[]], 3 => ['name'=>'5:6', 'recs'=>[]], 2 => ['name'=>'7:8', 'recs'=>[]], 1 => ['name'=>'9:10','recs'=>[]] ]; $n = 0; while ($row = $result->fetch_assoc()) { $cat[$row['cata']]['recs'][$n][] = $row['cata']; $cat[$row['catb']]['recs'][$n][] = $row['catb']; $n++; } // the output echo "<table border='1' style='width:500px; border-collapse:collapse;'>"; foreach ($cat as $c) { echo "<tr><th>{$c['name']}</th>"; for ($i=0; $i<$n; $i++) { echo '<td style="text-align:center;">' . (isset($c['recs'][$i]) ? join(',', $c['recs'][$i]) : '&ndash;') . "</td>"; } echo "</tr>\n"; } echo "</table>\n";

Why don't you reveal the data in the datatb table so we can see the input. Then show us what the output should look like from that data. That way we might able to see the path from one to the other.

Are you sure you have correct path - those names don't have much in common with the sample filenames that you posted earlier. Given the volume, it will better to chunk the $names array and add 1000 records per query. // populate the drawings table $chunks = array_chunk($names, 1000); foreach ($chunks as $ch) { $db->exec("INSERT IGNORE INTO drawings (drawing) VALUES " . join(',', $ch)); }

$table .= $columnKey ?

$db should be a valid PDO connection. Change to $pdo if that's yours.

Is this faster? $db->exec("DROP TABLE IF EXISTS drawings"); $db->exec("CREATE TABLE drawings (drawing varchar(20) not null PRIMARY KEY)"); $files = glob('files/*.*'); foreach ($files as $f) { $names[] = "('" . pathinfo($f, PATHINFO_FILENAME) . "')"; } // populate the drawings table $db->exec("INSERT IGNORE INTO drawings (drawing) VALUES " . join(',', $names)); // now delete those drawings NOT found in production table $db->exec("DELETE d FROM drawings d LEFT JOIN production_data p ON locate(d.drawing, p.description) WHERE p.description IS NULL; ");

perhaps $table .= join(',', $columnValue);

I think I'd prefer to see some of the data you are dealing with first. What does the data in drawings_pdf_files and production_data tables look like (few sample records)? What is the structure of production data table?

I think he wants $start = ($page - 1) * $limit The hard-coded 50 will give problems if $limit becomes something other than 50.

"sans regex" method <?php $str = "CONOCO 1'10x8 VC DF TP SGN||PRINCIPAL ILLUMINATION||ENG: CO3028TP_0VPR||DWG: CO200428||TO BE: DYED DIESEL (SPEC)||"; $result = ''; $link = 'DWG: <a href="http://www.domain.com?drawing=#">#</a>'; $p1 = $p2 = 0; while (($p1 = strpos($str, 'DWG:', $p2)) !== false) { $result .= substr($str, $p2, $p1 - $p2); $p2 = strpos($str, '||', $p1+4); $drwg = trim(substr($str, $p1+4, $p2 - ($p1 + 4))); $result .= str_replace('#', $drwg, $link); } $result .= substr($str, $p2); echo $str . '<br>' . $result; ?>

You could roll your own. function twoColorCircle($a, $b, $sz) { $out = "<svg width='$sz' height='$sz' viewBox='0 0 1000 1000'> <linearGradient id='grad2' x1='0' y1='0' x2='1' y2='0'> <stop offset='0%' style='stop-color:$a'/> <stop offset='50%' style='stop-color:$a'/> <stop offset='50%' style='stop-color:$b'/> <stop offset='100%' style='stop-color:$b'/> </linearGradient> "; $c = 500; $r = 499; $out .= "<circle cx='$c' cy='$c' r='$r' fill='url(#grad2)' stroke='#000' /> </svg>"; return $out; } foreach ([16,32,64,128,256] as $sz) echo twoColorCircle('#5fc75d' , '#f19e2d' , $sz); echo '<br>'; foreach (['16em','8em','4em','2em','1em'] as $sz) echo twoColorCircle('#5fc75d' , '#f19e2d' , $sz);

Post in the "job Offerings" section of this site and provide contact information.

I enjoy the challenge when someone posts a problem I can get my teeth into.

Not a one-liner, but still shorter foreach ($keys as $i => $k) { @list($k1, $k2) = explode('_', $k); if ($k2) $$k1[$k2] = array_column($arrayValues, $i); else $$k1 = array_column($arrayValues, $i); }

A problem with queries like that - there is no "exit" instruction. As I said, better to get the necessary data with the query +---------+----------+----------------+---------------------+----------------+------------+---------------------+---------------------+-----------+---------------------+---------------------+ | task_id | username | task_type | task_start | duration_hours | date | day_start | day_end | day_hours | hol_start | hol_end | +---------+----------+----------------+---------------------+----------------+------------+---------------------+---------------------+-----------+---------------------+---------------------+ | 1 | User 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-23 | 2020-06-23 08:00:00 | 2020-06-23 16:00:00 | 7.5000 | NULL | NULL | | 1 | User 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-24 | 2020-06-24 08:00:00 | 2020-06-24 16:00:00 | 7.5000 | 2020-06-24 08:00:00 | 2020-06-24 10:00:00 | | 1 | User 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-25 | 2020-06-25 08:00:00 | 2020-06-25 16:00:00 | 7.5000 | NULL | NULL | | 1 | User 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-26 | 2020-06-26 08:00:00 | 2020-06-26 16:00:00 | 7.5000 | NULL | NULL | | 1 | User 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-29 | 2020-06-29 08:00:00 | 2020-06-29 16:00:00 | 7.5000 | NULL | NULL | | 1 | User 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-30 | 2020-06-30 08:00:00 | 2020-06-30 16:00:00 | 7.5000 | NULL | NULL | | 2 | User 2 | Piping | 2020-06-24 08:00:00 | 8 | 2020-06-24 | 2020-06-24 08:00:00 | 2020-06-24 12:00:00 | 4.0000 | NULL | NULL | | 2 | User 2 | Piping | 2020-06-24 08:00:00 | 8 | 2020-06-25 | 2020-06-25 12:00:00 | 2020-06-25 16:30:00 | 4.0000 | NULL | NULL | | 2 | User 2 | Piping | 2020-06-24 08:00:00 | 8 | 2020-06-26 | 2020-06-26 08:00:00 | 2020-06-26 16:00:00 | 7.5000 | NULL | NULL | | 2 | User 2 | Piping | 2020-06-24 08:00:00 | 8 | 2020-06-29 | 2020-06-29 08:00:00 | 2020-06-29 16:00:00 | 7.5000 | NULL | NULL | | 2 | User 2 | Piping | 2020-06-24 08:00:00 | 8 | 2020-06-30 | 2020-06-30 08:00:00 | 2020-06-30 16:00:00 | 7.5000 | NULL | NULL | | 2 | User 2 | Piping | 2020-06-24 08:00:00 | 8 | 2020-07-01 | 2020-07-01 08:00:00 | 2020-07-01 12:00:00 | 4.0000 | NULL | NULL | +---------+----------+----------------+---------------------+----------------+------------+---------------------+---------------------+-----------+---------------------+---------------------+ the process the results to get the output, EG <?php include 'db_inc.php'; $db = pdoConnect('neo'); const DT_FORMAT = 'j M g:i a'; const HRS_PER_DAY = 10; const PIX_PER_HR = 12; const PIX_PER_ROW = 30; const HEAD_HT = 50; $wk_commence_date = $_GET['wkcomm'] ?? '2020-06-22'; $res = $db->prepare("SELECT task_id , CONCAT('User ', u.user_id) as username , task_type , task_start , duration_hours , date , CONCAT(date, ' ', day_start) as day_start , CONCAT(date, ' ', day_end) as day_end , day_hours , hol_start , hol_end FROM ( SELECT ot.task_id , ot.user_id , tt.task_type , tt.duration_hours , ot.start_timestamp as task_start , ot.start_timestamp + INTERVAL (duration_hours DIV 7.5) DAY -- whole days + INTERVAL 60 * (duration_hours - (duration_hours DIV 7.5)*7.5) MINUTE -- remaining portion on final day as task_end FROM operatives_tasks ot JOIN tasks USING (task_id) JOIN task_types tt USING (task_type_id) WHERE date(ot.start_timestamp) BETWEEN ? AND ? + INTERVAL 4 DAY ) task JOIN users u ON task.user_id = u.user_id LEFT JOIN ( SELECT date , och.user_id , och.start_time as day_start , och.finish_time as day_end , (timestampdiff(MINUTE, och.start_time, och.finish_time) - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_hours , oal.start_timestamp as hol_start , oal.end_timestamp as hol_end FROM date LEFT JOIN operative_contracted_hours och ON weekday(date) = och.week_day LEFT JOIN operatives_annual_leave oal ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time) AND oal.end_timestamp > CONCAT(date, ' ', och.start_time) AND och.user_id = oal.user_id ) days ON task.user_id = days.user_id AND days.date BETWEEN date(task_start) AND date(task_start) + interval 7 day ORDER BY username, task_id, task_start, date "); $res->execute([ $wk_commence_date, $wk_commence_date ]); $data = []; foreach ($res as $r) { if (!isset($data[$r['username']][$r['task_id']])) { $data[$r['username']][$r['task_id']] = [ 'type' => $r['task_type'], 'start' => $r['task_start'], 'end' => '', 'hours' => $r['duration_hours'], 'days' => [] ]; } $data[$r['username']][$r['task_id']]['days'][] = [ 'date' => $r['date'], 'dstart' => $r['day_start'], 'dend' => $r['day_end'], 'dhours' => $r['day_hours'], 'hstart' => $r['hol_start'], 'hend' => $r['hol_end'] ]; } function processUser($tasks, $wk_commence_date) { $ktasks = count($tasks); $chartht = $ktasks * PIX_PER_ROW + HEAD_HT; $chartwid = 500 + 5 * HRS_PER_DAY * PIX_PER_HR; $id_x = 5; $type_x = $chart = "<svg width='$chartwid' height='$chartht'> <style type='text/css'> .work {fill: #2DABE1; stroke-width: 0} .leave {fill: #C38E31; stroke-width: 0} .nonwork {fill: #CCCCCC; stroke-width: 0} .bkgrd {fill: #000; stroke: #EEE; stroke-width: 1} .grid {fill:transparent; stroke: #FFF; stroke-width: 1} .gridh {fill:transparent; stroke: #666; stroke-width: 1} .txt {fill: #FFF; font-size: 8pt; font-family: calibri, sans serif;} </style> <rect x='0' y='0' width='$chartwid' height='$chartht' class='bkgrd' /> <path d='M 500 25 h $chartwid' class='grid' /> <path d='M 0 50 h $chartwid' class='grid' /> \n"; $chart .= "<text x='5' y = '41' class='txt'>Task</text>\n"; $chart .= "<text x='300' y = '41' class='txt'>Start</text>\n"; $chart .= "<text x='400' y = '41' class='txt'>Finish</text>\n"; // times and date headings $dt = new DateTime($wk_commence_date); $dp = new DatePeriod($dt, new DateInterval('P1D'), 5); $x = 500 + HRS_PER_DAY * PIX_PER_HR / 2; $txt_y = HEAD_HT/2 - 9; foreach ($dp as $d) { $chart .= "<text x='$x' y='$txt_y' class='txt' text-anchor='middle'>" . $d->format('j M Y') . "</text>\n"; $x += HRS_PER_DAY * PIX_PER_HR; } for ($x=500; $x<$chartwid; $x += PIX_PER_HR) { $chart .= "<path d='M $x 45 v $chartht' class='gridh' />\n"; } for ($x=500; $x<$chartwid; $x += HRS_PER_DAY * PIX_PER_HR) { $chart .= "<path d='M $x 0 v $chartht' class='grid' />\n"; } $x = 500+PIX_PER_HR; $txt_y = HEAD_HT - 9; for ($d=0; $d<5; $d++) { $x1 = $x; foreach ([9,12,15] as $h) { $chart .= "<text x='$x1' y='$txt_y' class='txt' text-anchor='middle'>$h</text>\n"; $x1 += 3 * PIX_PER_HR; } $x += HRS_PER_DAY * PIX_PER_HR; } $dt = new DateTime($wk_commence_date); $dp = new DatePeriod($dt, new DateInterval('P1D'), 5); $x = 500 + HRS_PER_DAY * PIX_PER_HR / 2; $txt_y = HEAD_HT/2 - 9; foreach ($dp as $d) { $chart .= "<text x='$x' y='$txt_y' class='txt' text-anchor='middle'>" . $d->format('j M Y') . "</text>\n"; $x += HRS_PER_DAY * PIX_PER_HR; } for ($x=500; $x<$chartwid; $x += PIX_PER_HR) { $chart .= "<path d='M $x 45 v $chartht' class='gridh' />\n"; } for ($x=500; $x<$chartwid; $x += HRS_PER_DAY * PIX_PER_HR) { $chart .= "<path d='M $x 0 v $chartht' class='grid' />\n"; } // now do the tasks $tnum = 0; foreach ($tasks as $tid => $tdata) { $chart .= processTask($tnum, $tid, $tdata, $wk_commence_date); } $chart .= "</svg>\n"; return $chart; } function processTask($tnum, $tid, $tdata, $wk_commence_date) { $vOrigin = HEAD_HT + $tnum * PIX_PER_ROW; $txt_y = $vOrigin + PIX_PER_ROW - 9; $sdt = new DateTime($tdata['start']); $start = $sdt->format(DT_FORMAT); $calculated_end = ''; $output = "<text x='5' y='$txt_y' class='txt'>$tid</text> <text x='35' y='$txt_y' class='txt'>{$tdata['type']}</text> <text x='300' y='$txt_y' class='txt'>$start</text> \n"; // process each day until task completion is reached foreach ($tdata['days'] as $dayno => $d) { $output .= processDay($wk_commence_date, $vOrigin, $tdata, $d); } return $output; } function processDay($wk_commence_date, $vOrigin, &$tdata, $d) { if ($d['dend'] < $tdata['start']) return ''; if ($tdata['hours'] <= 0) return; $chart_day_start = new DateTime($d['date'] . ' 08:00:00'); $chart_day_end = new DateTime($d['date'] . ' 18:00:00'); $day_start = new DateTime($d['dstart']); $day_end = new DateTime($d['dend']); $output = ''; $top = $vOrigin + 5; $rHt = PIX_PER_ROW - 10; // non-work // if ($day_start > $chart_day_start) { // $x1 = hOffset($chart_day_start, $wk_commence_date); // $wid = hOffset($day_start, $wk_commence_date) - $x1; // $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n"; // } // if ($day_end < $chart_day_end) { // $x1 = hOffset($day_end, $wk_commence_date); // $wid = hOffset($chart_day_end, $wk_commence_date) - $x1; // $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n"; // } $x1 = hOffset($chart_day_start, $wk_commence_date); $wid = hOffset($chart_day_end, $wk_commence_date) - $x1; $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n"; // holidays if ($d['hstart']) { $hol_start = new DateTime($d['hstart']); $hol_end = new DateTime($d['hend']); $dth1 = max($hol_start, $chart_day_start); $dth2 = min($hol_end, $chart_day_end); $x1 = hOffset($dth1, $wk_commence_date); $wid = hOffset($dth2, $wk_commence_date) - $x1; $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='leave' />\n"; if ($hol_start <= $day_start) { $day_start = $hol_end; } else $day_end = $hol_start; $d['hours'] = ($day_end->getTimestamp() - $day_start->getTimestamp())/3600; } if ($tdata['hours'] <= $d['dhours']) { $day_end = clone $day_start; $mins = $tdata['hours'] * 60; $day_end->add(new DateInterval("PT{$mins}M")); $tdata['hours'] = 0; } else { $tdata['hours'] -= $d['dhours']; } $x1 = hOffset($day_start, $wk_commence_date); $wid = hOffset($day_end, $wk_commence_date) - $x1; $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='work' />\n"; if ($d['dhours'] > 4.5) { $break_start = clone $day_start; $break_start->add(new DateInterval('PT270M')); $x1 = hOffset($break_start, $wk_commence_date); $wid = PIX_PER_HR / 2; $output .= "<rect x='$x1' y='$top' width='$wid' height='$rHt' class='nonwork' />\n"; } if ($tdata['hours'] == 0) { $txt_y = $vOrigin + 21; $output .= "<text x='400' y='$txt_y' class='txt'>" . $day_end->format(DT_FORMAT) . "</text>\n"; } return $output; } function hOffset (DateTime $time, $wk_commence_date) { $dt1 = new DateTime($wk_commence_date); $dt2 = clone $time; $day_offset = $dt1->diff($time)->d * HRS_PER_DAY * PIX_PER_HR + 500; $dt2->setTime(8,0,0); $hour_offset = ($time->getTimestamp() - $dt2->getTimestamp())/3600 * PIX_PER_HR; return $day_offset + $hour_offset; } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="creation-date" content="05/10/2019"> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <title>Schedule</title> <style type="text/css"> body { font-family: verdana,sans-serif; font-size: 10pt; padding: 20px 50px; } </style> </head> <body> <header class="w3-container w3-blue-gray w3-center"> <h1>NEO Schedule</h1> </header> <div class="w3-container w3-left"> <?php foreach ($data as $username => $tasks) { echo "<h3>$username</h3>\n"; echo processUser($tasks, $wk_commence_date); } ?> </div> </body> </html> I just noticed it's not quite right yet - the second one should finish at 8:30, not 9am. But it's well on the way

Unnecessary, but harmless.

Didn't you get the memo about the regulation of working hours for AI?

For tasks like this, a really useful addition to any database is a date table (either permanent or temporary containing just the required range of dates CREATE TABLE `date` ( `date` date NOT NULL, PRIMARY KEY (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; For this I just created date records from June 15 to July 15. I also assume in the following query that no task will go over 7 days (change as required) The purpose of the query is to give the task details and the day-by-day hours workable. From hereon I would recommend looping through the query results (in PHP) until the task is completed. I don't think the effort required to do it all in SQL is worth it. SELECT task_id , u.user_id , task_type , task_start , duration_hours , date , day_start , day_end , day_hours , hol_start , hol_end FROM ( SELECT ot.task_id , ot.user_id , tt.task_type , tt.duration_hours , ot.start_timestamp as task_start , ot.start_timestamp + INTERVAL (duration_hours DIV 7.5) DAY -- whole days + INTERVAL 60 * (duration_hours - (duration_hours DIV 7.5)*7.5) MINUTE -- remaining portion on final day as task_end FROM operatives_tasks ot JOIN tasks USING (task_id) JOIN task_types tt USING (task_type_id) ) task JOIN users u ON task.user_id = u.user_id LEFT JOIN ( SELECT date , och.user_id , och.start_time as day_start , och.finish_time as day_end , (timestampdiff(MINUTE, och.start_time, och.finish_time) - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_hours , oal.start_timestamp as hol_start , oal.end_timestamp as hol_end FROM date LEFT JOIN operative_contracted_hours och ON weekday(date) = och.week_day LEFT JOIN operatives_annual_leave oal ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time) AND oal.end_timestamp > CONCAT(date, ' ', och.start_time) AND och.user_id = oal.user_id ) days ON task.user_id = days.user_id AND days.date BETWEEN date(task_start) AND date(task_start) + interval 7 day ORDER BY task_id, date; I added a second user with different working hours and a second task. +---------+---------+----------------+---------------------+----------------+------------+-----------+----------+-----------+---------------------+---------------------+ | task_id | user_id | task_type | task_start | duration_hours | date | day_start | day_end | day_hours | hol_start | hol_end | +---------+---------+----------------+---------------------+----------------+------------+-----------+----------+-----------+---------------------+---------------------+ | 1 | 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-23 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 1 | 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-24 | 08:00:00 | 16:00:00 | 7.5000 | 2020-06-24 08:00:00 | 2020-06-24 10:00:00 | | 1 | 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-25 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 1 | 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-26 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 1 | 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-29 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 1 | 1 | Boiler Install | 2020-06-23 08:00:00 | 9 | 2020-06-30 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 2 | 2 | Boiler Install | 2020-06-24 08:00:00 | 9 | 2020-06-24 | 08:00:00 | 12:00:00 | 4.0000 | NULL | NULL | | 2 | 2 | Boiler Install | 2020-06-24 08:00:00 | 9 | 2020-06-25 | 12:00:00 | 16:00:00 | 4.0000 | NULL | NULL | | 2 | 2 | Boiler Install | 2020-06-24 08:00:00 | 9 | 2020-06-26 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 2 | 2 | Boiler Install | 2020-06-24 08:00:00 | 9 | 2020-06-29 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 2 | 2 | Boiler Install | 2020-06-24 08:00:00 | 9 | 2020-06-30 | 08:00:00 | 16:00:00 | 7.5000 | NULL | NULL | | 2 | 2 | Boiler Install | 2020-06-24 08:00:00 | 9 | 2020-07-01 | 08:00:00 | 12:00:00 | 4.0000 | NULL | NULL | +---------+---------+----------------+---------------------+----------------+------------+-----------+----------+-----------+---------------------+---------------------+