Directory that lists employee information - follow-up Yesterday I posted a question involving multiple nested queries.  The queries pulled information from the database and created a directory listing of all employees.  There are two many-to-many relationships involving 5 tables.  For each employee they can have multiple job titles and multiple departments.

$stmt = $mysqli-&gt;prepare("SELECT employeeId, firstName, middleName, lastName, suffix, profilePhoto, GROUP_CONCAT(DISTINCT jobName ORDER BY jobTitleId), GROUP_CONCAT(DISTINCT departmentName ORDER BY departmentId), GROUP_CONCAT(DISTINCT departmentURL ORDER BY departmentId) FROM employee 
INNER JOIN employee_has_jobTitle ON employeeId = employee_has_jobTitle.employee_employeeId 
INNER JOIN jobTitle ON employee_has_jobTitle.jobTitle_jobTitleId = jobTitleId 
INNER JOIN employee_has_department ON employee.employeeId = employee_has_department.employee_employeeId
INNER JOIN department ON employee_has_department.department_departmentId = departmentId
GROUP BY employeeId ORDER By lastName, firstName");

$stmt-&gt;execute();
$stmt-&gt;store_result();
$stmt-&gt;bind_result($employeeId, $firstName, $middleName, $lastName, $suffix, $profilePhoto, $jobName, $departmentName, $departmentURL);

//set up the list
echo "&lt;ul class='plainList'&gt;";

while($stmt-&gt;fetch()){

    echo "&lt;li&gt;&lt;div class='clearfix'&gt;&lt;img src='/profileImages/$profilePhoto' alt='$firstName $lastName' class='directoryPhoto'&gt;";

    $deptName = explode(",", $departmentName);
    $deptURL = explode(",", $departmentURL);
    $jobTitle = explode(",", $jobName);
    echo "&lt;strong&gt;$firstName $lastName&lt;/strong&gt;&lt;br&gt;";

    $counter = 0;
    foreach($deptName as $name) {
        //echo a preceding comma if not the first department
        if($counter &gt; 0) echo ", ";

        echo "&lt;a href='$deptURL[$counter]'&gt;$name&lt;/a&gt;";
        $counter++;
    }

    echo "&lt;br&gt;";

    foreach($jobTitle as $job) {
        echo "$job&lt;br&gt;";
    }

    echo "&lt;/div&gt;&lt;/li&gt;";
}

//close the list
echo "&lt;/ul&gt;&lt;br&gt;";


If you check my other question, you can see that this is an improvement.  However, I don't think the query and code are as elegant as they should be.  Is there an obvious way to simplify the code and enhance performance that I'm not seeing?

Screenshot of the ERD for reference:
in your while statement you are listing all sorts of information inside of one list item tag, I am not so sure this is intentional,  it looks like it is going to be really messy. I think you should look into creating sub-lists there especially for the multiple Department names and Job Names.  you want something like an XML Date File here,  maybe I am overstepping my reviewer line and telling you to change the structure, but this is going to look really messy on a website I think.

something like this would be more convenient.

&lt;li&gt;
    &lt;div class='clearfix'&gt;
       &lt;img src='/profileImages/$profilePhoto' alt='$firstName $lastName' class='directoryPhoto' /&gt;
       &lt;strong&gt;$firstName $lastName&lt;/strong&gt; &lt;!-- should probably be a span with styling hooks --&gt;
       &lt;ul&gt;
           &lt;li&gt;Department1&lt;/li&gt;
           &lt;li&gt;Department1&lt;/li&gt;
       &lt;/ul&gt;
       &lt;ul&gt;
           &lt;li&gt;Job1&lt;/li&gt;
           &lt;li&gt;Job2&lt;/li&gt;
       &lt;/ul&gt;
    &lt;/div&gt;
&lt;/li&gt;


I don't know exactly what you want your finished product to look like, but this structure is far more solid than what your PHP is going to output,  you should look into making it output something like this,  because this is going a lot easier to work with for Styling and manipulating with PHP.

all of your image tags (&lt;img&gt;) need to be closed(&lt;img /&gt;), so does your break tags (&lt;br&gt;,&lt;br /&gt;)



From what you are saying it sounds like you want something more like this

&lt;li&gt;
    &lt;ul&gt;
        &lt;li&gt;
            &lt;img src='/profileImages/$profilePhoto' alt='$firstName $lastName' class='directoryPhoto' /&gt;
        &lt;/li&gt;
        &lt;li&gt;
            &lt;strong&gt;$firstName $lastName&lt;/strong&gt; &lt;!-- should probably be a span with styling hooks --&gt;
        &lt;/li&gt;
        &lt;li&gt;Department1, Department1&lt;/li&gt;
        &lt;li&gt;Job1, Job2&lt;/li&gt;
    &lt;/ul&gt;
&lt;/li&gt;


I removed the Div tag and some of the other stuff just so we could visualize this better.  this is a little more organized than what you are going to get with your current code.  it will still be easier to deal with.  

As Far as whether or not to do another list for the Department Names and Job Titles,  I would go with yes, even if they only have a single item, it will be easier to navigate the ones that do have them, and easier to manipulate the data,  it is more maintainable I think.



I mentioned still using nested lists for the Department Names and Job Names,  you should still have a title for the list item that is holding them, so you know what they are, but I will leave that up to you

&lt;li&gt;
    &lt;ul&gt;
        &lt;li&gt;
            &lt;img src='/profileImages/$profilePhoto' alt='$firstName $lastName' class='directoryPhoto' /&gt;
        &lt;/li&gt;
        &lt;li&gt;
            &lt;strong&gt;$firstName $lastName&lt;/strong&gt; &lt;!-- should probably be a span with styling hooks --&gt;
        &lt;/li&gt;
        &lt;li&gt;
            &lt;ul&gt;
                &lt;li&gt;Department1&lt;/li&gt;
                &lt;li&gt;Department1&lt;/li&gt;
            &lt;/ul&gt;
        &lt;/li&gt;
        &lt;li&gt;
            &lt;ul&gt;            
                &lt;li&gt;Job1&lt;/li&gt;
                &lt;li&gt;Job2&lt;/li&gt;
            &lt;/ul&gt;
        &lt;/li&gt;
    &lt;/ul&gt;
&lt;/li&gt;


so here is what that would look like with out the titles for the sub lists