I need to build an HTML table from a set of 12 weeks of mysql tables. It is trending information we're pulling from our ATS system. I have primary keys in mysql that might not always be the same across all 12 weeks and I want to be sure to include all of them in the resulting HTML table. There is 4 columns in the tables and not every row might have all columns filled out, but I want them included anyway.
I've been trying to come up with a method and looking up what examples others have done and haven't found one that fits my situation.
This is what the tables look like. There is 12 of them that I'm pulling with the variable $week1 - $week12. I want all recruiterId's included in the table and then 12 columns after that with 3 rows each for these different fields. Below the table example is the HTML email I put together.
recruiterId, hoursBilled, gpDollars, newHires
861 98 2
857 24 $987
855 11.25
854 24.25
851 4 $1076
<table class="tg">
<tr>
<th class="tg-0pky"></th>
<th class="tg-0pky"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
<th class="tg-0lax"></th>
</tr>
<tr>
<td class="tg-0pky">Recruiter</td>
<td class="tg-0pky">Hours Billed</td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
</tr>
<tr>
<td class="tg-0lax"></td>
<td class="tg-0lax">GP Dollars</td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
</tr>
<tr>
<td class="tg-0lax"></td>
<td class="tg-0lax">New Hires</td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
</tr>
</table>
I would want the table to be 14 rows across the top which only show the weeks from the dates I get. Then it would have the recruiters name on the second down from the top left. To the right of the recruiter name it would be the 3 data fields, one for each row. Then each of those would count out across the remaining 12 rows, one for each week.
-----------------------------------------------------------------------------
| |week1|week2|week3|week4|week5|week6|week7|
-----------------------------------------------------------------------------
recruiter |hoursBilled| 4 | 5 | 2 | 20 | 43 | 342 | 432
-----------------------------------------------------------------------------
|gpDollars | $24 | $32 |$56 |$65 |$32 | $54 | $53
-----------------------------------------------------------------------------
|newHires | 4 | | | 5 | | |
-----------------------------------------------------------------------------
recruiter2|hoursBilled|
etc,etc