Insert rows from another table by date

AndyTr

Hi all,

Apologies if this question has already been answered elsewhere, but sadly I can't figure out how to do this from the tutorials...

I've currently got a single HTML table that displays information about each job in date order, with each job stored as a separate row in a 'jobs' MySQL table. To make this easier for the users to read and find jobs at a glance, I've added a blank row, followed by a contrasting coloured header row with the day and date before each block of jobs for that day. This bit I can figure out how to do in DataTables, but ...

I've also got a second MySQL table containing simple text snippet 'reminders/memos' for the office staff, which are inserted after the date header row and before the jobs. These can span a date range, so the same message displays under the header row for each date that it is applicable, e.g. "Dave is on holiday" could start 02/01/17 and end 05/01/17, so this message would appear on each of the 4 relevant days.

To try and demonstrate this visually:

| << [blank row for aesthetic purposes only]

---

| Mon 2nd Jan 2017

---

| Calendar entry 1 - Dave is on holiday
| Calendar entry 2 - Jane is unavailable after 2pm

---

| Data row for job 1
| Data row for job 2
| Data row for job 3
| Data row for job 4

---

| << [blank row for aesthetic purposes only]

---

| Tues 3rd Jan 2017

---

| Calendar entry 1 - Dave is on holiday

---

| Data row for job 5
| Data row for job 6
| etc.

---

| << [blank row for aesthetic purposes only]

---

etc., etc.

At the moment, it is a simple case of returning a recordset from the jobs table, then looping through it in date order and adding in the relevant header and memo rows when the date changes between the last row and current row.

I was hoping to use DataTables to provide the users with the awesome filter features, so that they could use drop downs to quickly display all jobs between two dates or for a single customer, etc. Would it be possible for somebody to give me some pointers on how to build a table in the above format using DataTables, please?

Cheers

Andy

AndyTr

Anybody able to help?

Thanks!