0

I have this table: The data inside the table are results from mysql query. Each item has a total of 8 classes. The table will display each item and its classes that have a quantity only. If there is 0 quantity of item in a class, the class will not shown.(This explanation is just for your understanding.) What I have so far is something like this:

     |--------------|------------|----------|------------|
     |  Item Name   | Item Class | Quantity |    Total   |
     |--------------|------------|----------|------------|
     |              |     A      |    10    |
     |              |------------|----------|
     | Upper Bearing|     B      |     5    |
     |              |------------|----------|------------|
     |              |     C      |     4    |     19     |
     |--------------|------------|----------|------------|
     |              |     A      |     8    |            
     | Lower Bearing|------------|----------|------------|
     |              |     D      |     2    |     10     |
     |--------------|------------|----------|------------|
     |              |     B      |     2    |          
     |              |------------|----------|
     |     Vane     |     D      |    11    |
     |              |------------|----------|------------|
     |              |     E      |     2    |     15     |
     |--------------|------------|----------|------------|

The Total column is the sum of all quantities for each item. For example, Upper bearing, Total=Qty(A)+Qty(B)+Qty(C)= 10+5+4= 19. Take note that the quantity for each class is a result of some calculation, and not selected directly from the database.

What I want to do is to rowspan the <td> for Total so that it fill the whole row like Item Name did.

The expected output should look like this:

     |--------------|------------|----------|------------|
     |  Item Name   | Item Class | Quantity |    Total   |
     |--------------|------------|----------|------------|
     |              |     A      |    10    |            |
     |              |------------|----------|            |
     | Upper Bearing|     B      |     5    |     19     |
     |              |------------|----------|            |
     |              |     C      |     4    |            |
     |--------------|------------|----------|------------|
     |              |     A      |     8    |            |
     | Lower Bearing|------------|----------|     10     |
     |              |     D      |     2    |            |
     |--------------|------------|----------|------------|
     |              |     B      |     2    |            |
     |              |------------|----------|            |
     |     Vane     |     D      |    11    |     15     |
     |              |------------|----------|            |
     |              |     E      |     2    |            |
     |--------------|------------|----------|------------|

This is my code:

<table>
  <tr>
    <td>Item Name</td>
    <td>Item Class</td>
    <td>Item Quantity</td>
    <td>Total</td>
  </tr>

<?php
$result=mysql_query("SELECT * FROM tblitem INNER JOIN itemloc ON tblitem.itemId=itemloc.itemId INNER JOIN refitemclass ON tblitem.itemClassId=refitemclass.itemClassId");

$classqty=array();
while($row = mysql_fetch_array($result)){
    $item=$row['itemNm'];
    $class=$row['itemClassName'];

    if(!isset($classqty[$item][$class]))
    {
    $classqty[$item][$class] = 0;
    }
    if(is_null($row['itemLocCheckOut'])){
        $classqty[$item][$class] += $row['itemLocQty'];
    }
    else{
        $classqty[$item][$class] -= $row['itemLocQty'];
    }
}

$sum=array();
    foreach($classqty as $k1=>$v1){
        foreach($v1 as $k2=>$v2){
            if(!isset($sum[$k1])){
            $sum[$k1] = $v2;
            }
            else
            {
            $sum[$k1] += $v2;  /*calculation for total*/
            }
?>



<tr id="table2">

<td><?php echo $k1;?></td> /*output the item name*/
<td><?php echo $k2;?></td> /*output the item class*/
<td><?php echo $v2;?></td> /*output the quantity for item class*/
<?php
}
?>
<td><?php echo $sum[$k1];?></td> /*output the total*/
</tr>
<?php
}
?>

</table>

And here is the javascript to make rowspan for the item name.

<script>
    $(document).ready(function() {
       var span = 1;
       var prevTD = "";
       var prevTDVal = "";
       $("#table2 td:first-child").each(function() { //for each first td in every tr
          var $this = $(this);
          if ($this.text() == prevTDVal) { // check value of previous td text
             span++;
             if (prevTD != "") {
                prevTD.attr("rowspan", span); // add attribute to previous td
                $this.remove(); // remove current td
             }
          } else {
             prevTD     = $this; // store current td 
             prevTDVal  = $this.text();
             span       = 1;
          }
       });
    });
    </script>

I've tried things like <td rowspan=<?=count[$k2]?><?php echo $sum[$k1];?></td> and make the same javascript like for item name by just changing $("#table2 td:first-child") to $("#table2 td:last-child") but none of these works. The total is still in the same row with last row of item class.

P/S: Please don't suggest me to make any changes in the calculation part as the calculation works fine and the results are all correct. My problem is just the output structure of total quantity.

7
  • Do you need to do this in javascript? Why not do your calculations in php in your foreach($classqty as $k1=>$v1) loop, and then build your table after. Commented Nov 23, 2015 at 5:10
  • @Sean There's nothing wrong with my calculation. The result is all correct. I just want to know how to do the rowspan for the total. I think you didn't understand the whole concept of my question but thanks for commenting btw. Yes, this needs Javascript. Commented Nov 23, 2015 at 5:49
  • No, I understand the whole concept of [your] question. I was just wondering if you are open to doing it a different way that involved less code, and no need for javascript. Commented Nov 23, 2015 at 5:51
  • @Sean I hope so but my system is a bit complicated. And there's no need to change anything in the calculation part. I believe this needs Javascript to do the rowspan. Commented Nov 23, 2015 at 6:01
  • Your calculation part looks just fine, I just see where you could use it to your benefit by using it also to find your rowspan. Using javascript would work, but will be a little more challenging, as you can't use your method that you did for the Item Name, as you may have times when sibling Item Names have the same Total so it would rowspan too many rows. My javascript coding is a little slower than my php coding, but I could give it a try. Commented Nov 23, 2015 at 6:08

1 Answer 1

1

Using your current javascript code, you just need to add the following 2 lines, right after setting the rowspan for the td:first-child-

prevTD.siblings().last().attr("rowspan", span).text($this.siblings().last().text());
$this.siblings().last().remove();

So your code now looks like -

<script>
    $(document).ready(function() {
       var span = 1;
       var prevTD = "";
       var prevTDVal = "";
       $("#table2 td:first-child").each(function() { //for each first td in every tr
          var $this = $(this);
          if ($this.text() == prevTDVal) { // check value of previous td text
             span++;
             if (prevTD != "") {
                prevTD.attr("rowspan", span); // add attribute to previous td
                prevTD.siblings().last().attr("rowspan", span).text($this.siblings().last().text()); // add rowspan attribute to previous td's last sibling, and set the text
                $this.siblings().last().remove(); // remove the current td's last sibling
                $this.remove(); // remove current td
             }
          } else {
             prevTD     = $this; // store current td 
             prevTDVal  = $this.text();
             span       = 1;
          }
       });
    });
</script>

in action in this jsFiddle example - http://jsfiddle.net/aoqq43yg/


Edit

Since only the last row of each group has a Total cell, you need to find those rows, move them to the top of the group, and increase the rowspan. Using a similar loop to your other javascript -

   var prevTR = 0;
   $("#table2 tr").each(function(index,value) { // each row
       if($(this).children("td:eq(3)").text() != ""){ // if 4th td is not empty
           $("#table2 tr:eq("+prevTR+")").append($(this).children("td:eq(3)").attr("rowspan", (index+1) - prevTR)); // remove td, and move to the top row, and set the rowspan
          prevTR = index+1; // reset to the next row
       }
       else {
       }
   });

make sure to run this first, before your other code. Your full code would look like -

<script>
$(document).ready(function() {

   var prevTR = 0;
   $("#table2 tr").each(function(index,value) {
       if($(this).children("td:eq(3)").text() != ""){
           $("#table2 tr:eq("+prevTR+")").append($(this).children("td:eq(3)").attr("rowspan", (index+1) - prevTR));
          prevTR = index+1;
       }
       else {
       }
   });

   var span = 1;
   var prevTD = "";
   var prevTDVal = "";

   $("#table2 td:first-child").each(function() { //for each first td in every tr
      var $this = $(this);
      if ($this.text() == prevTDVal) { // check value of previous td text
         span++;
         if (prevTD != "") {
            prevTD.attr("rowspan", span); // add attribute to previous td
            $this.remove(); // remove current td
         }
      } else {
         prevTD     = $this; // store current td 
         prevTDVal  = $this.text();
         span       = 1;
      }
   });
   var preTR = 0;
});
</script>

updated jsFiddle example - http://jsfiddle.net/aoqq43yg/1/

Sign up to request clarification or add additional context in comments.

3 Comments

still not working..instead it drag the last item name into item class, the last item class into item quantity and the last item quantity into total. I think maybe some part of the calculation needs to be in the Javascript as well.
Sorry, apparently I did not notice that you only have the total <td> on the last row of each group, not each row. Let me check again.
I hate to say this but nothing happen. :( thanks for your time and effort though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.