Location: PHPKode > scripts > Unlimited sub-categories > unlimited-sub-categories/categories.class.php
<?php
/***************************************************************
Categories Class
Author: Shadi Ali
hide@address.com: hide@address.com
---------------------------------

SQL TABLE:

CREATE TABLE `categories` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`position` VARCHAR( 255 ) NOT NULL ,
`c_name` VARCHAR( 255 ) NOT NULL ,
`c_desc` TINYTEXT NOT NULL ,
`c_icon` VARCHAR( 255 ) NOT NULL ,
`c_group` VARCHAR( 255 ) NOT NULL default '0' ,
PRIMARY KEY ( `id` )
);

class summary:
------------------

->add_new($parent , $name , $desc , $icon )  // add new category
->delete($id,$deleteItems) // delete existing category and all sub-categories And also possible to delete all items associated with it ($deleteItems=1)
->update($id , $parent , $name  , $desc  , $icon  ) // update existing category
->build_list($id=0,$collapsed="") // return array with the categories ordered by name , it could be collapsed by setting $collapsed="collapsed".
->browse_by_id($id) // return array with sub categories under a specific category only.
->fetch ($id) // return existing category info.
->count_categories($id) // get sub categories count below a TOP-LEVEL category $id.
->count_items($id) // get the count of items associated to a category and its sub-categories. [needs the 2 variables to be set...  $itemsTable,$CID_FieldName]
********************************************************************/

class categories
{


var $HtmlTree;

var $name_prefix  = "&nbsp;&nbsp;";	// this is the prefix which will be added to the category name depending on its position usually use space.
var $table_name   = "categories";
var $itemsTable   = "items";		// this is the name of the table which contain the items associated to the categories
var $CID_FieldName= "category_id";	 // this is the field name in the items table which refere to the ID of the item's category.

// use the following keys into the $HtmlTree varialbe.
var $fields = array(
// field		=> field name in database ( sql structure )
"id"  		=> "id",
"position" 	=> "position",
"name"		=> "c_name",
"desc"		=> "c_desc",
"icon"		=> "c_icon",
"group"		=> "c_group",
);
/**************************************************
	--- NO CHANGES TO BE DONE BELOW ---
**************************************************/

var $c_list  = array();  // DON'T CHANGE THIS
var $Group  = 0;		 // DON'T CHANGE THIS

function categories()
{

$this->HtmlTree = array(
"header" 		 => '<table width=300px border=0 cellpadding=2 cellspacing=2>',
"BodyUnselected" => '<tr><td>[prefix]&raquo;<a href="?id=[id]">[name]</a></td></tr>',
"BodySelected"	 => '<tr><td>[prefix]&raquo;<a href="?id=[id]"><strong>[name]</strong></a></td></tr>',
"footer"		 => '</table>',
);

}

// ********************************************************
//		Add New Category
// ********************************************************


function add_new($parent = 0 , $name , $desc , $icon )  // add new category
{
// lets get the position from the $parent value
$position  = $this->get_position($parent);

// lets insert add the new category into the database.
$sql = "INSERT into ".$this->table_name."(position,c_name,c_desc,c_icon,c_group)
		VALUES('','".$name."','".$desc."','".$icon."','".$this->Group."')";

mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

$position .= mysql_insert_id().">";

$sql = "UPDATE ".$this->table_name."
		SET position = '".$position."'
		WHERE id = '".mysql_insert_id()."'";

mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
}

// ********************************************************
//		Delete Category
// ********************************************************

function delete($id,$items=NULL) // delete this category and all categories under it [set $items=1 if you need to delete associated items too, needs the 2 variables $itemsTable,$CID_FieldName]
{
$position = $this->get_position($id);

if($items==1) // delete associated items
{
	if($this->itemsTable == "" OR $this->CID_FieldName==""){
	die("<br><storng><u>Class Error:</u></strong><br>Either items Table name Or CID field name is blank!<br><br>");
	}

$sql = "SELECT id
		FROM ".$this->table_name."
		WHERE position LIKE '".$position."%'";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

while($category = mysql_fetch_array($res)){

$sql2 = "Delete	FROM  ".$this->itemsTable."
		WHERE ".$this->CID_FieldName." = '".$category["id"]."'";
$res2 = mysql_query($sql2) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
}
}

$sql = "DELETE FROM ".$this->table_name."
		WHERE position
		LIKE '".$position."%'";
mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
}


// ********************************************************
//		Update Category
// ********************************************************

function update($id , $parent = 0 , $name = 0 , $desc = 0 , $icon = 0 ,$group = 0)
{
// lets see if there is a change on the group
if($group == 0){
$this_category = $this->fetch($id);
$group = $this_category['c_group'];
}

// lets get the current position
$position     = $this->get_position($id);
$new_position = $this->get_position($parent).$id.">";

if($position != $new_position){
// then we update all the sub_categories position to be still under the current category
$sql1 = "SELECT id,position
		FROM ".$this->table_name."
		WHERE position	LIKE  '".$position."%'";
$res = mysql_query($sql1) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql1."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

while($sub = mysql_fetch_array($res)){
$new_sub_position = str_replace($position,$new_position,$sub['position']);
$sql2 = "UPDATE ".$this->table_name."
		SET position = '".$new_sub_position."'
		WHERE id =  '".$sub['id']."'";
mysql_query($sql2) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql2."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
}
}
// finally update the category position.
$sql3 = "UPDATE ".$this->table_name."
		SET position = '".$new_position."'
		WHERE position	=  '".$position."'";
mysql_query($sql3) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql3."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

$sql = "UPDATE ".$this->table_name."
		SET ";

// lets see what changes should be done and add it to the sql query.
foreach($this->fields as $field => $field_name){
if ($field 	== 'id') continue;		// no change will be done on the id
if ($field 	== 'position' ) continue; // position change have been done in the section above
$sql .= "".$field_name." = '".$$field."',";
}

$sql = substr_replace($sql,"",-1);
$sql .= "WHERE id=".$id."";

mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
}

// ********************************************************
//		Build Categories Array
// ********************************************************

function build_list($id=0,$collapsed="") //return an array with the categories ordered by position
{
$RootPos = "";
$this->c_list = array();

if($id != 0){
$this_category  = $this->fetch($id);
$positions      = explode(">",$this_category['position']);
$RootPos        = $positions[0];
}

// lets fetch the root categories
$sql = "SELECT *
		FROM ".$this->table_name."
		WHERE position	RLIKE '^([0-9]+>){1,1}$' AND c_group	=      '".$this->Group."'
		ORDER BY c_name";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

while($root = mysql_fetch_array($res)){
$root["prefix"] = $this->get_prefix($root['position']);
$this->c_list[$root['id']] = $root;

	if($RootPos == $root['id'] AND $id != 0 AND $collapsed != ""){
	$this->list_by_id($id);
	continue;

}else{

// lets check if there is sub-categories
	if($collapsed == "" AND $id==0){
	$has_children = $this->has_children($root['position']);
	if($has_children == TRUE) $this->get_children($root['position'],0);
}}}
return $this->c_list;
}


// ********************************************************
//		Check if Category has childrens
// ********************************************************

function has_children($position) // return TRUE if that position has sub-categories otherwise returns FALSE
{
$check_sql = "SELECT id FROM ".$this->table_name." WHERE position RLIKE  '^".$position."[0-9]+>$'";
$check_res = mysql_query($check_sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$check_sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
$check = mysql_fetch_array($check_res);
if($check['id'] != "")return TRUE;
else return FALSE;
}

// ********************************************************
//		Get Childrens
// ********************************************************

function get_children($position , $id = 0){

$sql = "SELECT *
		FROM ".$this->table_name."
		WHERE position	RLIKE '^".$position."[0-9]+>$'
		ORDER BY c_name";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

while($child = mysql_fetch_array($res)){
$child["prefix"] = $this->get_prefix($child['position']);

if($id != 0)
{
$this->c_list_by_id[$child['id']] = $child;
$has_children = $this->has_children($child['position']);
if($has_children == TRUE){
$this->get_children($child['position']);
}
continue;

}else{

// lets check if there is sub-categories
$this->c_list[$child['id']] = $child;
$has_children = $this->has_children($child['position']);
if($has_children == TRUE)$this->get_children($child['position']);
}}
}


// ********************************************************
//		Get childs of Specific Category only.
// ********************************************************

function list_by_id($id) //return an array with the categories under the given ID and ordered by name
{
$this_category  = $this->fetch($id);

$positions = explode(">",$this_category['position']);
$pCount = count($positions);
$i = 0;

// lets fetch from top to center
while($i < $pCount){
$pos_id	   = $positions["$i"];
if($pos_id == ""){$i++; continue;}
$list = $this->browse_by_id($pos_id);

foreach($list as $key=>$value){
$this->c_list["$key"] = $value;
$ni = $i + 1;
$nxt_id = $positions[$ni];
if($key == $nxt_id ) break;
} $i++;
}

//center to end
$i = $pCount-1;

while($i >= 0){
$pos_id	 = $positions["$i"];
if($pos_id == ""){$i--; continue;}
$list = $this->browse_by_id($pos_id);

foreach($list as $key=>$value){
$ni = $i - 1;
if($ni < 0) $ni =0;
$nxt_id = $positions[$ni];
if($key == $nxt_id ) break;
$this->c_list["$key"] = $value;
} $i--;
}

}

/***************************************
    Get array of categories under specific category.
 ****************************************/

function browse_by_id($id) // return array of categories under specific category.
{
$children 		= array();
$this_category  = $this->fetch($id);
$position       = $this_category['position'];

$sql = "SELECT *
		FROM ".$this->table_name."
		WHERE position	RLIKE '^".$position."(([0-9])+\>){1}$'
		ORDER BY c_name";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

while($child = mysql_fetch_array($res)){
$child["prefix"] = $this->get_prefix($child['position']);
$children[$child['id']] = $child;
}
return $children;
}

// ********************************************************
//		Get Position
// ********************************************************

function get_position($id)
{
if($id == 0)return "";
$sql = "SELECT position
		FROM ".$this->table_name."
		WHERE id = '".$id."'";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
$record =  mysql_fetch_array($res);
return $record['position'];
}

// ********************************************************
//		Get Prefix
// ********************************************************

function get_prefix($position)
{
$prefix = "";
$position_slices = explode(">",$position);
$count = count($position_slices) - 1;
for($i=1 ; $i < $count ; $i++){
$prefix .= $this->name_prefix;
}
return $prefix;
}

// ********************************************************
//		Fetch Category Record
// ********************************************************

function fetch ($id)
{
$sql = "SELECT *
		FROM ".$this->table_name."
		WHERE id = '".$id."'";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

$record = mysql_fetch_array($res);
$record["prefix"] = $this->get_prefix($record['position']);
$position_slices  = explode(">",$record['position']);
$key              = count($position_slices)-3;
if($key < 0) $key = 0;
$record["parent"] = $position_slices["$key"];
return $record;
}

// ********************************************************
//		Build HTML output
// ********************************************************

function html_output($id=0)
{
	 $tree  = $this->build_list($id,"collapsed"); // we have selected to view category


$output = "";
$output .= $this->HtmlTree['header'];

			if(is_array($tree))
			{
				foreach($tree as $c)
				{

					if($c['id'] == $id) 	$body = $this->HtmlTree['BodySelected'];
					else   						  	 	$body = $this->HtmlTree['BodyUnselected'];

					foreach($this->fields as $name => $field_name)
					{
						$body = str_replace("[$name]" ,$c["$field_name"],$body);


					}
						$body = str_replace("[prefix]",$c['prefix'],$body);

				$output .= $body;
				}
			}

$output .= $this->HtmlTree['footer'];
return $output;
}

// ********************************************************
//                      get sub-categories count at TOP-Level Category.  ( needs top-level category ID as a param)
// ********************************************************

function count_categories($cat_id)
{
$thisPosition = $this->get_position($cat_id);
$sql = "SELECT *
		FROM ".$this->table_name."
		WHERE position LIKE '".$thisPosition."%'";
$res   = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
$count = mysql_num_rows($res);
$count-= 1; // remove the category itself from the count
return $count;
}

// ********************************************************
//                      get items count under TOP-Level Category/sub-categories.  ( needs top-level category ID as a param)
// ********************************************************

function count_items($cat_id)
{
if($this->itemsTable == "" OR $this->CID_FieldName=="") die("<br><storng><u>Class Error:</u></strong><br>Either items Table name Or CID field name is blank!<br><br>");

$count = 0;
$thisPosition = $this->get_position($cat_id);

$sql = "SELECT *
		FROM ".$this->table_name."
		WHERE position LIKE '".$thisPosition."%'";
$res = mysql_query($sql) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));

while($category = mysql_fetch_array($res)){

$sql2 = "SELECT *
		FROM ".$this->itemsTable."
		WHERE ".$this->CID_FieldName."	= '".$category["id"]."'";
$res2 = mysql_query($sql2) or die(trigger_error("<br><storng><u>MySQL Error:</u></strong><br>".mysql_error()."<br><br><storng><u>Query Used:</u></strong><br>".$sql."<br><br><storng><u>Info:</u></strong><br>",E_USER_ERROR));
$count+= mysql_num_rows($res2);
}

return $count;
}

} // Class END
?>
Return current item: Unlimited sub-categories