PHP+MySQL join statement arrange multiple values as row in mysql_fetch array

Question

hi, I am using a MySQL INNER JOIN statement which returns various results for one distinct record in the main table from the joins, similar to http://stackoverflow.com/questions/1038653/how-to-join-multiple-joins-yet-return-distinct-values My query is

SELECT  cl.*, dep.dept_name  
FROM epapers.clientelle  cl 
INNER JOIN  epapers.dept_staff_users depu 
ON depu.user_id=cl.user_id 
INNER JOIN epapers.dept dep 
ON dep.dept_id=depu.dept_id 
group by cl.user_id 
ORDER BY cl.user_name ASC, 

I would like to display the above in a table shown below

echo "<tr bgcolor='#CCCCCC'>
<td >$num</td><td >".$row[user_id]."</td><td>".$row[user_name]."</td>
<td >".$row[fname]."</td><td >".$row[lname]."</td>
 <td >".$row[dept_name]."</td>
 <td >".$row[dept_name]."</td>
 <td >".$row[dept_name]."</td>
 <td >".$row[email]."</td>";
 $TrID = "$row[user_id]";
 echo "<td >";
 echo '<form name="activate" action="activate_acc.php" method="POST" ;">';
 echo "<input type='hidden' name='TrID' value='$TrID'>";    
 echo "<input type='checkbox' name='active' value='$row[active]'>"; 
 echo '<input type="submit" name="Submit" value="Delete">';
 echo '</form>';
 echo "</td >";
 ...

Note the the departments can be upto 3 departments. How do I return the mysql query results in a single row for the departments?

Answer 1

If you are using MySQL you can use GROUP_CONCAT but you will have to do some post processing and it's a bit ugly.

SELECT  cl.*, GROUP_CONCAT( DISTINCT dep.dept_name SEPARATOR '|' ) as dept_name 
FROM epapers.clientelle  cl 
INNER JOIN  epapers.dept_staff_users depu 
ON depu.user_id=cl.user_id 
INNER JOIN epapers.dept dep 
ON dep.dept_id=depu.dept_id 
group by cl.user_id 
ORDER BY cl.user_name ASC



foreach( ....
    $departments = explode( '|', $row['dept_name'] );
...
    if( isset( $departments[0] ) ) {
        echo "<td>{$departments[0]}</td>";
    } else {
        echo "<td></td>";
    }
    // same check
    echo "<td>{$departments[1]}</td>";
    // same check
    echo "<td>{$departments[2]}</td>";

It's kinda denormalisation at runtime...

Answer 2

well the way you can do it with a mysql query is to join the table for each of the three departments, such as this: select a.dept_id as dept_1 b.dept_id as dept_2 etc and join the same table with different for each dept id. this way is very processor intensive though. It would be a lot faster to get the data the way you have your query written, and then process the data with a function prior to using it. here is a simple example of how i would do it:

function prep_data($mysql_result)
{    
    $results = array();
    $work = array();
    while ($row = mysql_fetch_assoc($mysql_result))
    {
        if ($work['user_id'] == $row['user_id'])
        {
            // just add dept data to dept array
            $dept[] = $row['dept_id'];
        }
        else
        {
            // data changed            
            if (isset($work['user_id']))
            {
                // assign dept array to data array
                $work['dept_id'] = $dept;
                // add work array to results collection
                array_push($results, $work);                
                // start working with the new user_id
                $work = $row;
                $dept = array($row['dept_id']);
                continue;                
            } 
            else
            {
                // this is first pass.  grab the row data and start the dept[] array;
                $work = $row;
                $dept = array($row['dept_id']);
                continue;
            }
        }
    }
    $work['dept_id'] = $dept;
    array_push($results, $work);
    return($results);
}

// good luck