CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 Tip:Directory Builder, counting Categories and Subcategories

Print topic Send  topic

Author Message
lvalverdeb

Posts: 299
Posted: 03/27/2005, 7:41 AM

Hi,

This is a re-write of a function I posted a while ago to count the number of categories and subcategories in a Directory form generated by the CCS Directory Builder. This time the function is presented as a Class.

Luis

Example
--------------
Using the Directory example in the sample pack:
1) Place the class below in the Common.php or any other includable location.
2) In the BeforeShowCategory event, place the following code:
  
global $yp_categories,$DBInternetDB;  
$tmp = new DirectoryCount($DBInternetDB,"directory_categories","category_id_parent","category_id");  
$tmp->writeChildrenCountToLink($yp_categories->CategoryLink);  
unset($tmp);  
3) Likewise, in the BeforeShowSubCategory event, place the following code:
  
global $yp_categories,$DBInternetDB;  
	$tmp = new DirectoryCount($DBInternetDB,"directory_categories","category_id_parent","category_id");  
	$tmp->writeChildrenCountToLink($yp_categories->SubcategoryLink);  
Another way of instantiating the class is to modify its public properties:
  
$tmp = new DirectoryCount();  
$tmp->Connection = $DBInternetDB;  
$tmp->Table = "directory_categories";  
$tmp->ParentField = "category_id_parent";  
$tmp->Param = "category_id";  
$tmp->ValueType = ccsInteger;  
$tmp->writeChildrenCountToLink($yp_categories->CategoryLink);  
unset($tmp);  

Here's the code:
  
  
class DirectoryCount	{  
	//Public Vars  
	var $Connection; //Connection Object  
	var $Table;   
	var $ParentField; // Field Containing the "Parent Categories"  
	var $Param; //Parameter name from the link querystring  
	var $ValueType; // value type, class constructor defaults to ccsInteger  
  
	function DirectoryCount($Connection=null,$Table="",$ParentField="",$Param="",$ValueType=ccsInteger)	{  
		if ($Connection !== null)  
			$this->Connection = $Connection;  
		if (strlen($Table))  
			$this->Table = $Table;  
		if (strlen($ParentField))  
			$this->ParentField = $ParentField;  
		if (strlen($Param))  
			$this->Param = $Param;  
		$this->ValueType = $ValueType;  
  
	}  
  
	function writeChildrenCountToLink(&$Link) {  
		// e.g writeChildrenCountToLink($yourform->CategoryLink,new clsDB...(),"directory_table","parent_id","category_id");  
		// Save original link value  
		$CurText = $Link->GetValue();  
		$CurLinkParams = $Link->Parameters;  
		$myParamValue = $this->getParamValue($CurLinkParams,$this->Param);  
		if ($myParamValue != null) 	{  
			// if our parameter and its corresponding value were found then compute the count and modify the link text  
			$Result = $this->getChildrenCount($myParamValue);  
			if (intval($Result) > 0)  
				$Link->SetValue($CurText."(".$Result.")");  
		}  
		return $Link;  
	}  
  
	function getParamValue($QueryString,$Param)	{  
		// This function is used to retrieve parameter values from links built on the fly.  
		$paramArray = split("&",$QueryString);  
		$myParam = null;  
		$myParamValue = null;  
		//find our $Param in the QueryString  
		foreach ($paramArray as $item)	{  
			if (substr($item,0,strlen($Param)) == $Param)	{  
				$myParam = substr($item,0,strlen($item));  
				$myParamValue = substr($item,strlen($Param)+1,strlen($myParam));  
			}  
		}  
		return $myParamValue;  
	}  
  
	function getChildrenCount($myParamValue)	{  
		$Result =  CCDLookUp("count(*)",  
						 $this->Table,  
						 $this->ParentField."=".$this->Connection->ToSQL($myParamValue,$this->ValueType),  
						 $this->Connection  
				  );  
	return $Result;  
	}  
}  

_________________
lvalverdeb
CR, GMT-6
XAMPP/Ubuntu/CCS3.2/4
View profile  Send private message
datadoit.com
Posted: 03/27/2005, 2:08 PM

This works great for getting the count of categories and subcategories.
Thanks for posting.

How can it be used or adjusted to get the count of items for a particular
category or subcategory?

lvalverdeb

Posts: 299
Posted: 03/28/2005, 5:54 AM

If I understood your question correctly:

1) hard coding the category:
  
$myCategory = 10;    
$tmp = new DirectoryCount($DBInternetDB,"directory_categories","category_id_parent","category_id");    
$ChildrenCount = $tmp->getChildrenCount($myCategory);    
unset($tmp);    

2) Getting the value from the link params:

  
$tmp = new DirectoryCount($DBInternetDB,"directory_categories","category_id_parent","category_id");    
$ChildrenCount = $tmp->getChildrenCount($tmp->getParamValue($yp_categories->CategoryLink->Parameters,"category_id"));  
unset($tmp);  



Luis
_________________
lvalverdeb
CR, GMT-6
XAMPP/Ubuntu/CCS3.2/4
View profile  Send private message
Mike@DDI
Posted: 03/28/2005, 6:38 PM

Here's what I tried in the BeforeShowCategory event:

$myCategory = $yp_categories->ds->f("cat_category_id");
$tmp = new DirectoryCount($DBInternetDB,"directory_categories","category_id_parent","category_id");
$ChildrenCount = $tmp->getChildrenCount($myCategory);
unset($tmp);

It returns nothing. So I'm wondering what the $ChildrenCount is. Is that a label control? I tried placing a label in the grid called $ChildrenCountLabel, then in the BeforeShow event for the label I placed the above code, followed by:

$yp_categories->ChildrenCountLabel->SetValue($ChildrenCount);

and it returns only zeroes (0) for the count.

I made sure the class functions work properly by using your code as provided. That works well.

What am I not understanding?

Thanks.

Mike
lvalverdeb

Posts: 299
Posted: 03/29/2005, 7:40 AM

Mike,
The $ChildrenCount is just a variable used in the example and is not internal to the class. Also, I am not sure why $myCategory = $yp_categories->ds->f("cat_category_id") would return a zero value but the solution below works (at least on my computer :*) ) Please note that I have replaced :

$myCategory = $yp_categories->ds->f("cat_category_id")

for

$myCategory = $tmp->getParamValue($yp_categories->CategoryLink->Parameters,"category_id");


Since you have added a label control ( I assume right next to the Category Link), you can change your BeforeShowCategory event code to:
  
$tmp = new DirectoryCount($DBInternetDB,"directory_categories","category_id_parent","category_id");   
$myCategory = $tmp->getParamValue($yp_categories->CategoryLink->Parameters,"category_id");    
$ChildrenCount = $tmp->getChildrenCount($myCategory);   
$yp_categories->ChildrenCountLabel->SetValue($ChildrenCount);  
unset($tmp);   

Luis

_________________
lvalverdeb
CR, GMT-6
XAMPP/Ubuntu/CCS3.2/4
View profile  Send private message
datadoit.com
Posted: 03/29/2005, 9:44 AM

It's returning the count of categories (that 'splains the zeroes). I'd like for it to return the count of category items for a given category. This value should come from the table "directory_items", not "directory_categories".

Ex:

Cars
- New (47)
- Used (53)

Trucks (38)

Vans
- Consumer (17)
- Commercial (23)

Thnx.
lvalverdeb

Posts: 299
Posted: 03/30/2005, 6:04 AM

Hi Mike,

You've probably figured this out already but try this in the BeforeShowSubCategory event:

  
	$tmp = new DirectoryCount($DBInternetDB,"directory_items","category_id","category_id");  
	$tmp->writeChildrenCountToLink($yp_categories->SubcategoryLink);  
	unset($tmp);  
  
I believe this is what you are looking for. Please note that we are now using the directory_items table and the ParentField is the same as the Parameter name.
_________________
lvalverdeb
CR, GMT-6
XAMPP/Ubuntu/CCS3.2/4
View profile  Send private message
datadoit.com
Posted: 03/30/2005, 7:55 AM

Yes! Thanks Luis.


Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.