Categories, subcategories retrieving problem

Hi.

my db has

categories(CatID,ParentID,CatName)
items(ItemID,CatID,ItemName)

I need to retrieve particular items (Ex: ItemID=1,5,19,36). The problem
is I cannot make it that way that the item is under necessary
categories and subcategories.

Ex:
ParentID=0
ParentID=4
ParentID=10
...
ItemID=1

Thanx in advance

Re: Categories, subcategories retrieving problem

"TintedS" wrote in message
news:1137290126.401821.246300@z14g2000cwz.googlegroups.com.. .
> Hi.
>
> my db has
>
> categories(CatID,ParentID,CatName)
> items(ItemID,CatID,ItemName)
>
> I need to retrieve particular items (Ex: ItemID=1,5,19,36). The problem
> is I cannot make it that way that the item is under necessary

that sentence in english?

> categories and subcategories.
>
> Ex:
> ParentID=0
> ParentID=4
> ParentID=10
> ...
> ItemID=1
>
> Thanx in advance
>


perhaps you should try recursion.
a tree_roots table, a subcategories table, and a categories table to hold
strings is what I did.
if a root has no subcategories, nothing goes in the subcategories table.
this code is untested.


?> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">







include 'database_inc.php'; //sets $link
//-----------this code has not been debugged as of
1/30/2006-------------------

function traverse_tree($level=1,$cat_id) {
global $link;
$qr2=mysql_query("SELECT c.category,sc.subcat_id FROM categories AS
c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id ORDER BY
c.category", $link);
//make indenting for categories
$pad=""; for ($i=1; $i<=$level; $i++) {$pad .= " ";}
echo "$pad\[
\n"; //start of category set
while ($rowr2=mysql_fetch_array($qr2)) {
echo $pad . $rowr2['c.category'] . "
\n";
traverse_tree($level+1,$rowr2['sc.subcat_id']);
}
echo "$pad\]
\n"; //end of category set
}

function traverse_tree_select($level=1,$cat_id,$text) {
//just surround the call to this function with tags and you're
done.
global $link;
$qr2=mysql_query("SELECT c.cat_id,c.category,sc.subcat_id FROM categories
AS c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id ORDER
BY c.category", $link);
if ($text) {$text .= ":";} //category separator
while ($rowr2=mysql_fetch_array($qr2)) {
echo "$text$rowr2[c.category]\n";
traverse_tree($level+1,$rowr2['sc.subcat_id'], $text .
$rowr2['c.category']);
}
}

function display_categories() {
global $link;
$q=mysql_query("SELECT cr.cat_id,c.category FROM categoryroots AS
cr,categories AS c WHERE c.cat_id=cr.cat_id ORDER BY c.category", $link);
while ($row=mysql_fetch_array($q)) {
traverse_tree(1, $row['cr.cat_id']);
}
}

function display_categories_select($name,$id=0) {
global $link;
if (0==$id) {$id="";} else {$id="id=\"$id\"";}
echo "\n";
$q=mysql_query("SELECT cr.cat_id,c.category FROM categoryroots AS
cr,categories AS c WHERE c.cat_id=cr.cat_id ORDER BY c.category", $link);
while ($row=mysql_fetch_array($q)) {
traverse_tree_select(1, $row['cr.cat_id'], "");
}
echo "\n";
}


function delete_tree($cat_id) {
global $link;
$qr1=mysql_query("SELECT * FROM subcategories WHERE cat_id=$cat_id",
$link);
while ($rowr1=mysql_fetch_array($qr1)) {
if ($rowr1['subcat_id']) { //not 0?
delete_tree($rowr1['subcat_id']);
}
mysql_query("DELETE FROM categories WHERE cat_id=$rowr1[cat_id]", $link);
}
mysql_query("DELETE FROM subcategories WHERE cat_id=$cat_id", $link);
}

function zap_categories() {
global $link;
$q=mysql_query("SELECT cat_id FROM categoryroots", $link);
while ($row=mysql_fetch_array($q)) {
delete_tree(1, $row['cat_id']);
}
mysql_query("DELETE FROM categoryroots", $link);
}


?>

//note - this code is untested.
display_categories();
display_categories_select("category");
?>