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.
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.
// 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>');
}
}
To make a correct rowspan, you need to know the number in advance.
That leaves you with:
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.
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>';
}
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.
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.
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