[SOLVED] Total In Joomla Component

[hellouthere]

I have this code...

 

<div id="con_types1">
	<div id="heading_Types">Categories</div>
	<div id="con_types2">
		<div class="con_types3">
			<?php
				$query = "SELECT *
						  FROM #__hp_cats
						  WHERE parent = 0";
				$database->setQuery( $query );
				$crows = $database->loadObjectList( );

				foreach( $crows as $crow ){
					$query = "SELECT COUNT(*)
							  FROM #__hp_properties
							  WHERE cat = ".$crow->id;
					$database->setQuery( $query );
					$cnt = $database->loadResult();

					?>
						<a href="index.php?option=com_hotproperty&task=viewcats&id=<?=$crow->id?>&Itemid=<?=$Itemid?>"><?=$crow->name?></a> (<?=$cnt?>)
					<?php
					$query = "SELECT *
							  FROM #__hp_cats
							  WHERE parent = ".$crow->id;
					$database->setQuery( $query );
					$scrows = $database->loadObjectList( );
					if( count( $scrows ) ){
						echo '<ul>';
						foreach( $scrows as $scrow ){
							$query = "SELECT COUNT(*)
  										  FROM #__hp_properties
									  WHERE cat = ".$scrow->id;
							$database->setQuery( $query );
							$cnt = $database->loadResult();
							?>
							<li><a href="index.php?option=com_hotproperty&task=viewcats&id=<?=$scrow->id?>&sub=1&Itemid=<?=$Itemid?>"><?=$scrow->name?></a> (<?=$cnt?>)</li>
							<?php
						}
						echo '</ul>';							
					}
					echo '<br />';
				}
			?>

		</div>
	</div>
</div>

 

At the moment it takes the total of each subcategory and display it next to the linked subcategory...

 

I would also like the total of all the subcategories displayed next to the category...

 

i.e.

 

San Antonio (6)
     North (2)
     East (1)
     South (0)
     West (3)

 

Any ideas or completed code would be much appreciated...

 

Thanks in advance

[fenway]

Well, using subqueries & joins, you could combine all of these into a single sql statement; as for the layout, well, that's up to you...

[hellouthere]

I tried this query but i dont think the syntax is correct...

 

$query = "SELECT COUNT(*)
FROM #__hp_properties
WHERE cat = ".$crow->id."
OR cat = (SELECT id
	FROM #__hp_cats
	WHERE parent =".$crow->id.")";

 

Thanks in advance

[fenway]

replace = ( SELECT... with IN ( SELECT...

[hellouthere]

Thanks for the help, i solved the problem by using  a much simpler query by not trying to over complicate things... seems i have a habit of it...