doudi2431
2013-05-12 14:08 阅读 111
已采纳

如何使用动态rowspan显示数据库中的数据

Iam newbie in php programing, i have some problames with showing data from mysql database using php and html. this is my tables :

location :id_location
          location

component :id_comopnent
           id_location
           comonen

sub_component :id_sub_component
               id_comopnent
               sub_component

How can i get following output:

    location 
    |-----------|-------------|
    | component |sub component|
    |-----------|-------------|
    |           |    Data     |
    |   Data    |-------------|
    |           |    Data     |
    |-----------|-------------|
    |           |    Data     |
    |           |-------------|
    |   Data    |    Data     |
    |           |-------------|
    |           |    Data     |
    |-----------|-------------|

    location 

    |-----------|-------------|
    | component |sub component|
    |-----------|-------------|
    |           |    Data     |
    |   Data    |-------------|
    |           |    Data     |
    |-----------|-------------|
    |           |    Data     |
    |   Data    |-------------|
    |           |    Data     |
    |-----------|-------------|

    Location (according to the data)

this is my code

 <?php
   $dsn = "mysql:host=localhost;dbname=kampus_hijau";
   $dbc = new PDO($dsn, 'root', '');

    $sql1 = "SELECT * FROM Lokasi ORDER BY id_location";
    $stmt1 = $dbc->prepare($sql1);
    $stmt1->execute();

    while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
    $location++;
    echo "Location $location : ".$row1['location'];

  ?>
<table width="469" border="1">
  <tr bgcolor="#00FFFF">
    <th width="109" class="rounded" scope="col">Component</th>
    <th width="248" class="rounded" scope="col">Sub Component</th>
    <th>Nilai</th>

     </tr>
  <?php

    $query = "SELECT * FROM sub_component,component where sub_component.id_component=component.id_component and  component.id_location='$data[id_location]' order by component.id_location";
    $stmt = $dbc->prepare($query);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $result[$row['component']][] = $row['sub_component'];

    }

                foreach($result as $id => $invoices) {
                        echo '<td rowspan='. count($invoices) . '>' . $id . '</td>';
                        $count = 0;
                        foreach ($invoices as $invoice) {
                            if ($count != 0) {
                                echo '<tr>';
                            }
                            echo "<td>$invoice</td>";
                            echo "</tr>";
                            $count++;
                }
                    }
?>

</table>
<?php   

}
?>

and this the output that i get : enter image description hereenter image description hereenter image description here

with that code, the output form is appropriate, but the data does not appear appropriate, it's always show the data previous in the next table (red box is the data that is repeated ). how can i fix it ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    drtpbx3606 drtpbx3606 2015-01-17 16:29

    First of all sorry for my poor english.

    In your query, instead of doing order by id_location, do order by component name . This way you can add dynamic rowspan easily. I have not chnged your connection program, but I have changed your second part. Please check. I know there is 3 to 4 loops. But if any body found better algo please tell me.

        $sql1 = "SELECT * FROM Lokasi ORDER BY id_location";
        $stmt1 = $dbc->prepare($sql1);
        $stmt1->execute();
    
        while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
        $location++;
        echo "Location $location : ".$row1['location'];
    
    ?>
    
    
    
    <?php
        $query = "SELECT * 
                    FROM sub_component,
                         component 
                   WHERE sub_component.id_component=component.id_component 
                     AND component.id_location='$data[id_location]' 
                ORDER BY component.component_name";
        $stmt = $dbc->prepare($query);
        $stmt->execute();
    
        # Declare two emty array
        $component     = array(); # Will store the components
        $sub_component = array(); # Will store the sub components
    
        $loop = 0;
    
        # Now if any data is fetched from previsous query, then fill
        # the above declared arrays.
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    
            $component[$loop]     = $row['component'];
            $sub_component[$loop] = $row['sub_component'];
    
            $loop = $loop + 1;
        }
    
        # If no data fetched then I m telling 
        # No data fetched.
        if (!sizeof($component)) {
            echo 'Empty Data';
        } else {
    
            print "<table width='469px' border='1'>
                        <tr bgcolor='#00FFFF'>
                            <th width='109' class='rounded' scope='col'>Component</th>
                            <th width='109' class='rounded' scope='col'>Sub Component</th>
                        </tr>";
    
            # Now our main logic starts to print dynamic rowspan
    
            # Go for a loop.
            # Here the imporant is to use for loop
    
            $tmp_arr = array();
    
            $main_assoc_arr = array();
    
            for ($i = 0; $i < sizeof($sub_component); $i++) {
    
                array_push($tmp_arr, $sub_component[$i]);
    
                # If we have reached the last element
                # and in $main_assoc_arr the comonent is not exist
                # Then we will store them as following.
                if (   $i = (sizeof($sub_component)-1)
                    && !array_key_exists($component[$i], $main_assoc_arr)) {
    
                    $main_assoc_arr[ $component[$i] ] = array();
                    $main_assoc_arr[ $component[$i] ] = $tmp_arr;
    
                    # Restore the array.
                    $tmp_arr = array();
    
                    # Also get out of the loop
                    break;
                }
    
                # If the present component is not equal to the 
                # Next component then 
                if ($component[$i] != $component[$i+1]) {
    
                    $main_assoc_arr[ $component[$i] ] = array();
                    $main_assoc_arr[ $component[$i] ] = $tmp_arr;
    
                    # Restore the array.
                    $tmp_arr = array();
                }
            }
    
            # Now we are going to print the table with rowspan.
            foreach ($main_assoc_arr as $comp=>$sub_comp) {
    
                $printed = 0;
                $rowspan = sizeof($sub_comp);
    
                foreach ($sub_comp as $elm) {
    
                    print "<tr>";
    
                    # Manke sure that the column will not print
                    # in each loop as it conatins dynamic array.
                    if (!$printed) {
    
                        print "<td rowspan='$rowspan'>$comp</td>";
                    }
    
                    print "<td>$elm</td>"
                    print "</tr>";
                }
            }
    
            print "</table>";
    
        }
    ?>
    
    点赞 评论 复制链接分享
  • dongzizhi9903 dongzizhi9903 2014-12-02 22:24

    you're variable $result[][] doesn't get erased or cleaned by the end of your loop.

    In your first while loop (while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {) another loop executes to fill the $result array (the while ($row = $stmt->fetch(PDO::FETCH_ASSOC))). When the first while loop begins its second round, your second while loop will just stack the new rows on top of the old ones. So make sure the $result[][] array is empty at the end of the first loop.

    点赞 评论 复制链接分享

相关推荐