如何使用PHP在行跨中显示mysql数据？

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:

我有来自MySQL的数据,显示了客户获得的所有组织,以及每个组织中雇员的所有详细信息。我想列出每个组织名称只有一次,即在单个单元格(行间距)中,并且该组织中的所有员工都反对这个名称,如:

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?

如何显示此数据,因为每个组织的雇员人数在高级时都不知道,所以我不知道设置rowspan的值。同样,我如何为其他组织开始一行?我必须写两个查询吗?

Many Thanks.

7 个解决方案

#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.

不是最美丽而是如此简单。

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

#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

向DB服务器询问计数

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.

就个人而言,我会选择方法二。数据库服务器在计算行时非常有效,当要显示许多行时,这可能会快得多。

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

#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.

为了节省内存,您可以迭代结果集,同时org与行缓冲相同,当组织更改时,打印当前批处理并开始缓冲下一批。

#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.

它对rowpan没有帮助,但请查看WITH ROLLUP修饰符。它以类似于您想要的格式返回数据。

#7

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

使用pear的HTML_Table包比如下面的例子,通过我也喜欢Jrgns的ROLLUP版本

    <?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

#1