I'm building a custom report module for my SaaS and need a little advice on a query I'm building (MySQL and PHP). I'm dynamically building a table, and each record that is returned will be one row on that table. It dynamically joins many different tables, and it all works great with the exception of 3 tables I need to join.
All the other tables are a 1-1 LEFT JOIN. For example if I'm querying the employee table and trying to find department_id, it joins just hunky dory. It'll join like this:
First | Last | Dept
John | Smith | Toy Department
The thing that makes this complicated is that those extra 3 tables have a 1-many join. It may also be important to point out that when I make this join it could pull anywhere from 2-50 rows of data. For example if I'm querying the employee table and need to join the clock in/out table it'd end up looking like this:
First | Last | Time
John | Smith | 8:00 am
John | Smith | 12:00 pm
John | Smith | 1:00 pm
John | Smith | 5:00 pm
If I did a right join, the first/last name would be null but I'd still have multiple rows.
The reason why I want to keep them in one row is because with the table that I'm setting up when the report is pulled, when the it hits the that needs to use one of the 3 1-many tables, it needs to stay in the same data cell (this is out of my control, it just has to happen):
<table>
<tr>
<td>John</td>
<td>Smith</td>
<td>8:00 am<br />12:00 pm<br />..... </td>
</tr>
</table>
I really really really want to avoid having to hit that cell and then running another query to pull out all the data. The reason being is that I could have a report run with thousands of rows with 25+ columns and it would run that same query 25x1000's of rows.
What I was hoping for was something like this when the data gets returned:
First | Last | Time_1 | Time_2 | Time_3 | Time_4
John | Smith | 8:00 am | 12:00 pm | 1:00 pm | 5:00 pm
Some thoughts I've had is to run a while loop in PHP and dynamically build the Joins for MySQL, but I run into the problem of the fact that I'd need to supply how many times to join the table to get that column right. I could technically do:
for($i = 1; $i < 50; ++$i) {
$sql_fields .= "time_clock_{$i}.time AS Time_{$i},";
$joins .= "LEFT JOIN time_clock AS time_clock_{$i} ON time_clock_{$i}.employee_id = emp.id";
}
$sql_fields = rtrim($sql_fields,',');
mysql_query("SELECT emp.field_blah, emp.field_blah2, {$sql_fields} FROM employees AS emp {$joins} WHERE condition = blah");
I'm just afraid that this would really cause some performance issues with MySQL and all the crazy joins I'd have in there. Anyone have a better idea? I'll entertain anything at this point.
