query and loop inside a loop?

[Vicarious]

I am having a pretty hard time coming up with a solution to my problem here. I dont know that I can express what I am hoping to do properly in words as I am still relatively new to php so hopefully this will help. This is something close to what I am hoping to end up with:

---

| JOB_ID |
|------------|
|emp_name1|
|emp_name2|

|emp_name3|

	  $query_dj = mysql_query("SELECT DISTINCT labor.job_id, sales.job_id FROM sales 
			INNER JOIN labor ON sales.id = labor.job_id
			WHERE sales.date_out = '0000-00-00' AND labor.off_on = '1'");

while ($itemdj = mysql_fetch_array($query_dj)){
	echo" 
	<div id=\"jip\" style=\"background-color:#B0C4DE;width:170px;height:300px;float:left;text-align:center;\">
	<br /><center><table>
	  <tr>
    	    <th>$itemdj[1]</th>
	  </tr>";

  $query_ej = mysql_query("SELECT employee.emp_name FROM labor 
			INNER JOIN employee ON employee.id = labor.emp_id
			WHERE labor.off_on = '1' AND labor.job_id = '$itemdj[0]'");

	while ($itemdej = mysql_fetch_row($query_ej)){
	echo" 
	  <tr>
   	    <td>$itemej[1]</td>
	  </tr>";
	}
	echo"</table></center>
	</div>";
}
	echo"
   <div id=\"footer\" style=\"background-color:#99CC66;clear:both;text-align:center;\">
	<br />
	Some Text Here
	</div>

The output I am getting now displays proberly except for listing the emp_names under each job_id. I can get all the data I need from one query if needed however I have not been able to display it properly. The end result will have somewhere between 1 and 10 DISTINCT job_names each with multiple emp_names working on them.

I have looked into many various ways of achieving the output I am looking for but so far I have not succeeded. I have looked at GROUP_CONCAT(), DISTINCT JOINS, foreach, one loop inside another (as the above code shows) and a few more Im sure I am forgetting.

What is going to be my best approach to get something like this done. Apparently I just do not know enough about all the various functions of php to make this work yet. Do I need to look further into some of the ways I have already started looking into or am I way off base here?

Thanks much, I hope I was clear enough in what I am trying to do.

[Bonesnap]

Vicarious;11005461 wrote:

I can get all the data I need from one query if needed however I have not been able to display it properly.

Can you post the query?

[Vicarious]

Sure, the only info I am looking to display is the sales.job_id and the employee.emp_name fields which can be returned with something along the lines of the following:

SELECT `sales.job_id`, `employee.emp_name` FROM `sales`
	INNER JOIN `labor` ON `sales.id` = `labor.job_id`
	INNER JOIN `employee` ON `employee.id` = `labor.emp_id` 
                WHERE `sales.date_out` = '0000-00-00' AND `labor.off_on` = '1' GROUP BY `sales.job_id`;

example result of that query:

+-----------+----------+
| job_id | emp_name |
+-----------+----------+
| WIN047MBR | Eric |
| PER230COU | Melton |
| MIL505LOF | Greg |
| WIN090MAG | Jimmy |
| WIN047MBR | Kenny |
| WIN047MBR | Roger |
| PER230COU | Dee |
| WIN090MAG | Archie |
+-----------+----------+
8 rows in set (0.00 sec)

[Vicarious]

Actually I copied the wrong query there as with the GROUP BY it only outputs the first row of each job_id.

The previous result came from this query:

SELECT sales.job_id, employee.emp_name FROM sales
INNER JOIN labor ON sales.id = labor.job_id
INNER JOIN employee ON employee.id = labor.emp_id 
WHERE sales.date_out = '0000-00-00' AND labor.off_on = '1';