<?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 = " "; // 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]»<a href="?id=[id]">[name]</a></td></tr>',
"BodySelected" => '<tr><td>[prefix]»<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
?>