CODEWITHSUNDEEP
phpmysql
dee
dee

Reputation: 21

Dynamic rowspan while fetching records from database

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

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

How can i get following output:

Ename   Sal      
tom     100
        200
bill    100
        250
        450
        400

Upvotes: 2

Views: 21903

Answers (4)

Dinesh Patra
Dinesh Patra

Reputation: 1135

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 Work:

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;

Result:

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

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;

CODING:

  1. The whole task we can divide into 3 parts.
    1. Mysql Data fetch and storing in array.
    2. Calculating rowspan
    3. Printing

MySql Datafetching:

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

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

Code Optimization:

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>

Result:

a busy cat

Upvotes: 8

vozaldi
vozaldi

Reputation: 97

It should be like this

<?php 
    $sql = "SELECT * FROM emp ";
    $result= mysql_query($sql);
    while($row=mysql_fetch_array($result)):
      $ename = $row['ename'];

      // count the esal in each ename
      $sql2 = "SELECT * FROM emp WHERE ename=$ename";
      $result2 = mysql_query($sql2);
      $count_result2 = mysql_num_rows($result2);

    ?>
      <tr >
        <td rowspan="<?php echo $count_result2; ?>"><?php echo $row['ename']; ?></td>
        <?php
          // loop each esal
          while($row2 = mysql_fetch_array($result2)):
          ?>
              <td><?php echo $row['esal']; ?></td>
            </tr>
          <?php
          endwhile; // endwhile for each esal looping
    endwhile; // endwhile for the main looping
    ?>

Upvotes: 0

nithi
nithi

Reputation: 3877

You can check condition like this with the last row and the current row.

$sql = "select * from emp";
$result= mysql_query($sql);
echo "<table>";
while($row=mysql_fetch_array($result))
{ 
  $now=$row[0];
  if($last!=$now) {
  echo "<tr><td>$row['ename']</td><td>$row['esal']</td></tr>";
  }else{
  echo "<tr><td>&nbsp;</td><td>$row['esal']</td></tr>";
  }
  $last = $row[0];
 }
echo "</table>";

I hope this will help you.

Upvotes: 0

Otto
Otto

Reputation: 4190

Something like this?

While data
 echo
   <tr>
        <td rowspan="3"><p>numbers</p></td>
        <td><p>1</p></td>
        <td><p>2</p></td>
 </tr>

Please post something of your code

Upvotes: 0

Related Questions