DB display with rowspan

Hi all,

I have two tables in a database, ‘mp_fruit_cats’ & ‘mp_fruit_items’.

One table holds the categories and the other holds the items. They are related by a mp_fruit_cat_id in the items table which matches up with the id’s of the category.

I’m using the following code:


<table width="75%" border="1" cellspacing="0" cellpadding="1">
  <tr>
    <td><b>Category id</b></td>
    <td><b>Fruit category</b></td>
    <td><b>Fruit item</b></td>
	<td><b>Fruit item's Category id</b></td>
  </tr>

<?php
// pluck the data from DB
$sql = "SELECT * FROM `mp_fruit_cats`,`mp_fruit_items` WHERE mp_fruit_cats.mp_fruit_cat_id = mp_fruit_items.mp_fruit_item_cat_id";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);

	if($num_rows > 0){
	
		while($row = mysql_fetch_array($result)){
		
			// values from 'mp_fruit_cats' table
			$mp_fruit_cat_id = $row['mp_fruit_cat_id'];
			$mp_fruit_cat = $row['mp_fruit_cat'];
			$mp_fruit_cat_order = $row['mp_fruit_cat_order'];
			
			// values from 'mp_fruit_items' table
			$mp_fruit_id = $row['mp_fruit_id'];
			$mp_fruit_item_cat_id = $row['mp_fruit_item_cat_id'];
			$mp_fruit_item = $row['mp_fruit_item'];
			$mp_fruit_num = $row['mp_fruit_num'];
			
			// display the rows of the table
			echo "<tr>\
";
			echo "<td>$mp_fruit_cat_id</td>\
";
			echo "<td>$mp_fruit_cat</td>\
";
			echo "<td>$mp_fruit_item</td>\
";
			echo "<td>$mp_fruit_item_cat_id</td>\
";
			echo "</tr>\
";
		
		}
	
	}

?>
</table>

which produces the following output:


<TABLE cellSpacing=0 cellPadding=1 width="75%" border=1>
  <TBODY>
  <TR>
    <TD><B>Category id</B></TD>
    <TD><B>Fruit category</B></TD>
    <TD><B>Fruit item</B></TD>
    <TD><B>Fruit item's Category id</B></TD></TR>
  <TR>
    <TD>1</TD>
    <TD>Yellow Fruits</TD>
    <TD>Banana</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>1</TD>
    <TD>Yellow Fruits</TD>
    <TD>Grapefruit</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>1</TD>
    <TD>Yellow Fruits</TD>
    <TD>Lemon</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>2</TD>
    <TD>Green Fruits</TD>
    <TD>Apple</TD>
    <TD>2</TD></TR>
  <TR>
    <TD>2</TD>
    <TD>Green Fruits</TD>
    <TD>Pear</TD>
    <TD>2</TD></TR>
  <TR>
    <TD>3</TD>
    <TD>Red Fruits</TD>
    <TD>Strawberry</TD>
    <TD>3</TD>
</TR>
</TBODY>
</TABLE>


All well and good, but what I’d like to do, is add a rowspan attribute to the category column so that it would produce output such as:


<TABLE cellSpacing=0 cellPadding=1 width="75%" border=1>
  <TBODY>
    <TR>
      <TD><B>Category id</B></TD>
      <TD><B>Fruit category</B></TD>
      <TD><B>Fruit item</B></TD>
      <TD><B>Fruit item's Category id</B></TD>
    </TR>
    <TR>
      <TD rowspan="3">1</TD>
      <TD rowspan="3">Yellow Fruits</TD>
      <TD>Banana</TD>
      <TD>1</TD>
    </TR>
    <TR>
      <TD>Grapefruit</TD>
      <TD>1</TD>
    </TR>
    <TR>
      <TD>Lemon</TD>
      <TD>1</TD>
    </TR>
    <TR>
      <TD rowspan="2">2</TD>
      <TD rowspan="2">Green Fruits</TD>
      <TD>Apple</TD>
      <TD>2</TD>
    </TR>
    <TR>
      <TD>Pear</TD>
      <TD>2</TD>
    </TR>
    <TR>
      <TD>3</TD>
      <TD>Red Fruits</TD>
      <TD>Strawberry</TD>
      <TD>3</TD>
    </TR>
  </TBODY>
</TABLE>

I’m a bit confused as to how I would go about working out the required value for the rowspan attribute. :\

Would I need to perform an additional query somewhere, or will one query be enough?

Any help would be really really appreciated.

Cheers
Matt

as per me, you got 2 options.
FIRST you runa query which counts up number of items in a category and when it is executed then you loop throught the result array and then get item from the table where cat_id is what you got in the first one.

SECOND
you do as you are doing right now. generate an array like
$array[‘catid’] = aray(‘fruit’=>‘fruitname’,‘item’=>array(‘item1’,‘item2’));

then you go throught this array and count how many items are there like
$count = count($array[‘catid’][‘item’]);

now whatever the $count is that’s what your rowspan value will be.

AND I WOULD PREFER SECOND ONE.
hope i am being realistic.

Thanks for the feedback so far…

I opted to try the first suggestion and have come up with this so far:


<table width="75%" border="1" cellspacing="0" cellpadding="1">
  <tr>
    <td><b>Category id</b></td>
    <td><b>Fruit category</b></td>
    <td><b>Fruit item</b></td>
	<td><b>Fruit item's Category id</b></td>
  </tr>

<?php
// pluck the data from DB
$sql = "SELECT * FROM `mp_fruit_cats`,`mp_fruit_items` WHERE mp_fruit_cats.mp_fruit_cat_id = mp_fruit_items.mp_fruit_item_cat_id";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);

	if($num_rows > 0){
	
		while($row = mysql_fetch_array($result)){
		
			// values from 'mp_fruit_cats' table
			$mp_fruit_cat_id = $row['mp_fruit_cat_id'];
			$mp_fruit_cat = $row['mp_fruit_cat'];
			$mp_fruit_cat_order = $row['mp_fruit_cat_order'];
			
			// values from 'mp_fruit_items' table
			$mp_fruit_id = $row['mp_fruit_id'];
			$mp_fruit_item_cat_id = $row['mp_fruit_item_cat_id'];
			$mp_fruit_item = $row['mp_fruit_item'];
			$mp_fruit_num = $row['mp_fruit_num'];
			
			$sql2 = "SELECT * FROM mp_fruit_items WHERE mp_fruit_item_cat_id = '$mp_fruit_cat_id'";
			$result2 = mysql_query($sql2);
			$num_rows2 = mysql_num_rows($result2);
			
			
			// display the rows of the table
			echo "<tr>\
";
			echo "<td rowspan=\\"$num_rows2\\">$mp_fruit_cat_id</td>\
";
			echo "<td rowspan=\\"$num_rows2\\">$mp_fruit_cat ($num_rows2)</td>\
";
			echo "<td>$mp_fruit_item</td>\
";
			echo "<td>$mp_fruit_item_cat_id</td>\
";
			echo "</tr>\
";
			
			$sql3 = "SELECT * FROM mp_fruit_items WHERE mp_fruit_item_cat_id = '$mp_fruit_cat_id' LIMIT 1,$num_rows2";
			$result3 = mysql_query($sql3);
			$num_rows3 = mysql_num_rows($result3);
			
				if($num_rows3 > 0){
				
					while($row3 = mysql_fetch_array($result3)){

						$mp_fruit_item = $row3['mp_fruit_item'];
						$mp_fruit_item_cat_id = $row3['mp_fruit_item_cat_id'];
						
						echo "<tr>\
";
						echo "<td>$mp_fruit_item</td>\
";
						echo "<td>$mp_fruit_item_cat_id</td>\
";
						echo "</tr>\
";										
					
					}
				
				}
		
		}
	
	}

?>
</table>

which is producing the following:


<TABLE cellSpacing=0 cellPadding=1 width="75%" border=1>
  <TBODY>
  <TR>
    <TD><B>Category id</B></TD>
    <TD><B>Fruit category</B></TD>
    <TD><B>Fruit item</B></TD>
    <TD><B>Fruit item's Category id</B></TD></TR>
  <TR>
    <TD rowSpan=3>1</TD>
    <TD rowSpan=3>Yellow Fruits (3)</TD>
    <TD>Banana</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>Grapefruit</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>Lemon</TD>
    <TD>1</TD></TR>
  <TR>
    <TD rowSpan=3>1</TD>
    <TD rowSpan=3>Yellow Fruits (3)</TD>
    <TD>Grapefruit</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>Grapefruit</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>Lemon</TD>
    <TD>1</TD></TR>
  <TR>
    <TD rowSpan=3>1</TD>
    <TD rowSpan=3>Yellow Fruits (3)</TD>
    <TD>Lemon</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>Grapefruit</TD>
    <TD>1</TD></TR>
  <TR>
    <TD>Lemon</TD>
    <TD>1</TD></TR>
  <TR>
    <TD rowSpan=2>2</TD>
    <TD rowSpan=2>Green Fruits (2)</TD>
    <TD>Apple</TD>
    <TD>2</TD></TR>
  <TR>
    <TD>Pear</TD>
    <TD>2</TD></TR>
  <TR>
    <TD rowSpan=2>2</TD>
    <TD rowSpan=2>Green Fruits (2)</TD>
    <TD>Pear</TD>
    <TD>2</TD></TR>
  <TR>
    <TD>Pear</TD>
    <TD>2</TD></TR>
  <TR>
    <TD>3</TD>
    <TD>Red Fruits (1)</TD>
    <TD>Strawberry</TD>
    <TD>3</TD></TR>
</TBODY>
</TABLE>

Nearly there, apart from each category repeating as the same amount of related items, plus some strange results in the items column.

So near but so far… :\

Please help before I pull out every strand of hair
Matt

Yippee… sorted it out…

here’s what worked if anyone is interested



<table width="75%" border="1" cellspacing="0" cellpadding="1">
  <tr>
    <td><b>Category id</b></td>
    <td><b>Fruit category</b></td>
    <td><b>Fruit item</b></td>
	<td><b>Fruit item's Category id</b></td>
  </tr>
<?php
$sql1 = "SELECT * FROM mp_fruit_cats";
$result1 = mysql_query($sql1);
$num_rows1 = mysql_num_rows($result1);

	if($num_rows1 > 0){
	
		while($row1 = mysql_fetch_array($result1)){
		
			$mp_fruit_cat_id = $row1['mp_fruit_cat_id'];
			$mp_fruit_cat = $row1['mp_fruit_cat'];
			
			// count how many fruits relate to this category
			$count1_sql = "SELECT * FROM mp_fruit_items WHERE mp_fruit_item_cat_id = '$mp_fruit_cat_id'";
			$count1_result = mysql_query($count1_sql);
			$count1_num_rows = mysql_num_rows($count1_result);
			
			echo "<tr>";
			echo "<td rowspan=$count1_num_rows>$mp_fruit_cat_id</td>";
			echo "<td rowspan=$count1_num_rows>$mp_fruit_cat</td>";
			
			// loop through fruits in here
			$sql2 = "$count1_sql";
			$result2 = mysql_query($sql2);
			
				while($row2 = mysql_fetch_array($result2)){
				
					echo "<td>$row2[mp_fruit_item]</td>";
					echo "<td>&nbsp;</td>";
					echo "</tr>";			
				}
			// end loop of fruits here
			echo "</tr>";			
		
		} // end while($row1 = mysql_fetch_array($result1))
	
	} //  end if($num_rows > 0)
?>
</table>

… I was trying to loop through ther fruits in the wrong place