How do I display mysql data in row span using PHP?

Question

I have data from MySQL showing all organisations a customer got, with all details of employess in each organisation. I want to list each organisation name only once i.e. in a single cell ( row span) and all employees in that organisation against this name like:

Org1     Emp1 Name, Emp1 Phone, Emp1 Address
         Emp2 Name, Emp2 Phone, Emp2 Address


Org2     Emp1 Name, Emp1 Phone, Emp1 Address
         Emp2 Name, Emp2 Phone, Emp2 Address

How do I display this data because the number of employess for each organisation is not known in advanced, so I do'nt about setting value of rowspan. Similarly how do I start a row for other organisation? Do I have to write two queries?

Many Thanks.

Answer 1

Classic.

Workaround: only display the name if different than the previous one. You can even not bother about the rowspan (you keep an empty cell).

$currentOrg = '';
while ($row = mysql_fetch_object($query)) {
   if ($row->org != $currentOrg) {
      echo "$row->org".
   }
   $currentorg = $row->org;
}

Not the most beautiful but so simple.

Answer 2

// Get the data
$data = mysql_query('SELECT org, emp_name, emp_phone, emp_address FROM x');

// Store it all in a 2D array, keyed by org
$rows = array();
while ($row = mysql_fetch_assoc($data))
{
    // Initialise each org to an empty array (not really needed in PHP but I prefer it)
    if (empty($rows[$row['org']]))
        $rows[$row['org']] = array();

    $rows[$row['org']][] = $row;
}

// Print it out
foreach ($rows as $org => $employees)
{
    print('<tr><td rowspan="' . count($employees) . '">' . htmlentities($org) . '</td>');

    foreach ($employees as $i => $employee)
    {
        // If $i == 0, we've already printed the <tr> before the loop
        if ($i)
            print('<tr>');

        print('<td>......</td></tr>');
    }
}

Answer 3

To make a correct rowspan, you need to know the number in advance.

That leaves you with:

• iterating the query result twice, counting the values until they change
• asking the DB server for the count

Personally, I would go with method number two. DB servers are quite efficient with counting rows, this will probably be a lot faster when there are many rows to display.

Answer 4

It could be easier (but less efficient) to make a query for each organisation (plus one query to find how many organisations there are presumably).

A better way to do it would be to loop through the array beforehand. For example:

$sql = $mysqli->query('SELECT * FROM `organisation_members` ORDER BY `organisation` DESC');

if (!$sql || $sql->num_rows) {
    // No data
} else {
    $data = array();
    while ($row = $sql->fetch_assoc()) {}
        if (!array_key_exists($row['organisation'], $data)) {
            $data[$row['organisation']] = array();
        }
        $data[$row['organisation']][]['name'] = $row['name'];
        // ...
    }
    $sql->close();
    echo '<table>';
    foreach ($data as $org => $people) {
        $people_in_org = count($data[$org]) - 1;
        $counter = 0;

        echo '<tr>';
        echo '<td rowspan="' . $people_in_org + 1 . '">' . $org . '</td>';

        while ($counter < $people_in_org) {
            if (counter > 0) {
                echo '<tr>';
            }
            echo '<td>' . $people[$counter]['name'] . '</td>';
            // etc
            echo '</tr>';
        }
    }
    echo '</table>';
}

Answer 5

To conserve memory you could iterate over the resultset while org is the same buffering the rows, when org changes, print the current batch and start buffering the next batch.

Answer 6

It won't help you with the rowspan, but look into the WITH ROLLUP modifier. It returns the data in a format similiar to what you want.

Answer 7

What about using pear's HTML_Table package like in the following example, through i also like Jrgns's ROLLUP version

 <?php

 require_once "HTML/Table.php";




 $table = new HTML_Table(array('border'=>'1'));
 $bo=array(
  array('6','a2','a3','a4'),
  array('1','b2','b3','b4'),
  array('1','c2','c3','c4') ,
  array('2','c2','c3','c4') ,
  array('2','c2','c3','c4') ,
  array('4','c2','c3','c4') );

 foreach ($bo as $r => $borow)
  $table->addRow($borow);

 $rsFirst=0;
 $rsLen=0; 
 foreach ($bo as $r => $borow) {
  if ($r!=0 and $borow[0]!=$prevrow[0] ) {
   //jump in values
   $table->setCellAttributes ( $rsFirst,0, array('rowspan'=>$rsLen));
   $rsFirst=$r;
   $rsLen=0;
  }
  $prevrow=$borow;
  $rsLen++; 
  if ($r==sizeof($bo) - 1) {
   $table->setCellAttributes ( $rsFirst,0, array('rowspan'=>$rsLen));
  }
 }


 echo $table->toHTML();

 ?>

servas, boerl