从数据库中获取记录时的动态行跨度 [英] Dynamic rowspan while fetching records from database

问题描述

Ename   Sal      
tom     100
tom     200
bill    100
bill    250
bill    450
bill    400

这是提供上述输出的查询和html结构.

This is the query and html structure which has given the above output.

<?php 
$sql = "select * from emp ";
$result= mysql_query($sql);
while($row=mysql_fetch_array($result))
{
  <tr >
    <td rowspan=""  ><?php echo $row['ename']; ?></td>
    <td><?php echo $row['esal']?></td>
  </tr>
  <? }?>

我如何获得以下输出:

Ename   Sal      
tom     100
        200
bill    100
        250
        450
        400

推荐答案

抱歉，我的英语不好: 在这里，我已经回答了这个问题如何显示数据从具有动态行跨度的数据库中.再次让我尝试回答这个问题.首先，以免我们进行mysql查询.

sorry for my poor english: Here I had answered this question How to show data from database with dynamic rowspan. Again let me try to answer this question. First lest us work on mysql query.

MySql工作:

在mysql查询中，您尚未查询排序依据.因为在现实生活中，您不能指望汤姆的所有记录都在账单记录中.例如，进行以下插入.

In the mysql query you have not queried for order by. Because in real life, you can not expect that after all the records of tom, bills record will be there. For example take the following insertion.

INSERT INTO test_work(ename, sal) 
               VALUES("tom",  100), 
                     ("bill", 450), 
                     ("bill", 100), 
                     ("tom",  200),
                     ("bill", 250),
                     ("bill", 400),
                     ("James", 50);
SELECT * FROM test_work;

结果:

+-------+------+
| ename | sal  |
+-------+------+
| tom   |  100 |
| bill  |  450 |
| bill  |  100 |
| tom   |  200 |
| bill  |  250 |
| bill  |  400 |
| James |   50 |
+-------+------+

因此，您的mysql查询应按ename排序.在此，每个人的 sal 也应该被命令.所以我们的查询:

So your mysql query should be order by ename. Here also each person's sal should be ordred . So Our query:

SELECT * FROM emp ORDER BY ename, sal;

编码:

1. 整个任务可以分为3部分.

1. The whole task we can divide into 3 parts.

1. Mysql数据获取并存储在数组中.
2. 计算行距
3. 打印

MySql数据提取:

MySql Datafetching:

在从mysql服务器获取数据期间，我们总是应该尝试使用mysql_fetch_assoc函数而不是mysql_fetch_array.因为mysql_fetch_assoc将仅返回ename和sal.但是mysql_fetch_array将返回索引为ename，sal，0、1的数组.

During data fetching from mysql server always we should try to use mysql_fetch_assoc function instead of mysql_fetch_array . Because mysql_fetch_assoc will return only ename and sal. But mysql_fetch_array will return array with indexes ename, sal, 0, 1.

    # connect to mysql server
    # and select the database, on which
    # we will work.
    $conn = mysql_connect('', 'root', '');
    $db   = mysql_select_db('test');

    # Query the data from database.
    $query  = 'SELECT * FROM test_work ORDER BY ename, sal';
    $result = mysql_query($query);

    # Intialize the array, which will 
    # store the fetched data.
    $sal = array();
    $emp = array();

    # Loop over all the fetched data, and save the
    # data in array.
    while($row = mysql_fetch_assoc($result)) {
        array_push($emp, $row['ename']);
        array_push($sal, $row['sal']);
    }

计算行距:

Calculating Row Span:

    # Intialize the array, which will store the 
    # rowspan for the user.
    $arr = array();

    # loop over all the sal array
    for ($i = 0; $i < sizeof($sal); $i++) {
        $empName = $emp[$i];

        # If there is no array for the employee
        # then create a elemnt.
        if (!isset($arr[$empName])) {
            $arr[$empName] = array();
            $arr[$empName]['rowspan'] = 0;
        }

        $arr[$empName]['printed'] = "no";

        # Increment the row span value.
        $arr[$empName]['rowspan'] += 1;
    }

当您要打印arr数组时，输出将是:

when you will print_r the arr array the output will be:

Array
(
    [bill] => Array
        (
            [rowspan] => 4
            [printed] => no
        )

    [James] => Array
        (
            [rowspan] => 1
            [printed] => no
        )

    [tom] => Array
        (
            [rowspan] => 2
            [printed] => no
        )

)

按行打印:

Printing with rowspan:

    echo "<table cellspacing='0' cellpadding='0'>
            <tr>
                <th>Ename</th>
                <th>Sal</th>
            </tr>";


    for($i=0; $i < sizeof($sal); $i++) {
        $empName = $emp[$i];
        echo "<tr>";

        # If this row is not printed then print.
        # and make the printed value to "yes", so that
        # next time it will not printed.
        if ($arr[$empName]['printed'] == 'no') {
            echo "<td rowspan='".$arr[$empName]['rowspan']."'>".$empName."</td>";
            $arr[$empName]['printed'] = 'yes';
        }
        echo "<td>".$sal[$i]."</td>";
        echo "</tr>";
    }
    echo "</table>";

代码优化:

现在，我们可以结合使用rowspan计算和mysql数据获取.因为在将获取的数据保存到数组期间，我们可以计算行跨度.所以我们的最终代码:

Now we can combine the rowspan calculation and mysql data fetching. Because during saving the fetched data in array we can calculate the rowspan. So our final code:

<!DOCTYPE html>
<html>
    <head>
        <style>
            table tr td, table tr th{
                border: black 1px solid;
                padding: 5px;
            }
        </style>
    </head>
    <body>
        <?php
        # connect to mysql server
        # and select the database, on which
        # we will work.
        $conn = mysql_connect('', 'root', '');
        $db   = mysql_select_db('test');

        # Query the data from database.
        $query  = 'SELECT * FROM test_work ORDER BY ename, sal';
        $result = mysql_query($query);

        # $arr is array which will be help ful during 
        # printing
        $arr = array();

        # Intialize the array, which will 
        # store the fetched data.
        $sal = array();
        $emp = array();

        #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%#
        #     data saving and rowspan calculation        #
        #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%#

        # Loop over all the fetched data, and save the
        # data.
        while($row = mysql_fetch_assoc($result)) {
            array_push($emp, $row['ename']);
            array_push($sal, $row['sal']);

            if (!isset($arr[$row['ename']])) {
                $arr[$row['ename']]['rowspan'] = 0;
            }
            $arr[$row['ename']]['printed'] = 'no';
            $arr[$row['ename']]['rowspan'] += 1;
        }


        #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
        #        DATA PRINTING             #
        #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%#
        echo "<table cellspacing='0' cellpadding='0'>
                <tr>
                    <th>Ename</th>
                    <th>Sal</th>
                </tr>";


        for($i=0; $i < sizeof($sal); $i++) {
            $empName = $emp[$i];
            echo "<tr>";

            # If this row is not printed then print.
            # and make the printed value to "yes", so that
            # next time it will not printed.
            if ($arr[$empName]['printed'] == 'no') {
                echo "<td rowspan='".$arr[$empName]['rowspan']."'>".$empName."</td>";
                $arr[$empName]['printed'] = 'yes';
            }
            echo "<td>".$sal[$i]."</td>";
            echo "</tr>";
        }
        echo "</table>";
        ?>
    </body>
</html>

结果:

这篇关于从数据库中获取记录时的动态行跨度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！