How to show data from database with dynamic rowspan

Question

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

This question does not show any research effort. It is important to do your homework. Tell us what you found and why it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. FAQ. — John Conde
– John Conde, Commented May 12, 2013 at 14:10
@JohnConde sorry , it's my mistake, I've added code that I use.. — TheRob
– TheRob, Commented May 12, 2013 at 14:50
Why are you using both mysql_* and PDO in the same file? You can completely switch to PDO. — hjpotter92
– hjpotter92, Commented May 12, 2013 at 15:06

Dinesh Patra · Accepted Answer · 2015-01-17 16:29:30Z

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>";

    }
?>

Wessel van der Linden · Accepted Answer · 2014-12-02 22:24:36Z

0

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.

answered Dec 2, 2014 at 22:24

Wessel van der Linden

2,6422 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

How to show data from database with dynamic rowspan

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related