CODEWITHSUNDEEP
phpmysqlhtml
hash
hash

Reputation: 963

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

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.

Upvotes: 1

Views: 6993

Answers (7)

boerl
boerl

Reputation:

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

Upvotes: 0

Jrgns
Jrgns

Reputation: 25188

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.

Upvotes: 0

John Nilsson
John Nilsson

Reputation: 17307

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.

Upvotes: 0

Ross
Ross

Reputation: 46987

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>';
}

Upvotes: 0

Tomalak
Tomalak

Reputation: 338208

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.

Upvotes: 0

Greg
Greg

Reputation: 321638

// 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>');
    }
}

Upvotes: 4

Veynom
Veynom

Reputation: 4147

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.

Upvotes: 4

Related Questions