[Resolved] Theory

[asa_carter]

Hi,

Could somebaody help me with some theory?

I have just designed an auction site but it has limitations. I can only have a main category and sub category.

The database has the following fields:-

Table categories

Category, Category_ID, sub_category, sub_category_ID

Table Item

Item, Item_ID, category_ID, sub_category_ID

I would like to be able to extend this to at least two more layers or possibly infinite.

Does anyone know the best way of doing this?

Asa Carter

[Pig]

You create a single table for all of your categories, with the following columns: id, title, parent_id.

Root level categories have a parent_id of 0. Child categories of other categories use their parent's id as the parent_id.

This allows an infinite level of depth.

[asa_carter]

How then, do I access all items within a category?

E.g.

table category

id title parent_id
1 antiques 0
2 art 1

3 books 0
4 property 0
5 detached 1

detached would have the same parent_id as art as they are at the same tree level but under different categories.

[coreyp_1]

In this model (a good one, BTW!) parent_id is not telling you what tree level the category is at, but is a reference to it's parent category.

Let's look at your example:

table category

id title parent_id
1 antiques 0
2 art 1
3 books 0
4 property 0
5 detached 1

Your main categories are antiques, books, and property, since they have a parent_id of 0 (i.e., no parent).

The subcategories art and detached have a parent_id of 1, which references back to the category with the id of 1, antiques.

The layout is something like this:

antiques
    art
    detached
books
property

If you add some more rows to the table, say:

id title parent_id
6 old 2
7 older 2
8 hotels 3
9 houses 3
10 oldest 2

Then you wold have a structure like this:

antiques
    art
    detached
books
    old
    older
    oldest
property
    houses
    hotels

HTH

[asa_carter]

Ah, thanks. I get it now. So, to access all the items within a certain category, all I have to do is access it's parent_ID. Excellent. 😃

[asa_carter]

Hi,

Ok. I've got this working, but I was wondering how to display the number of items in all the sub-categories below a category?

www.cartermediadesign.co.uk/browse_selector.php?id=1

<? 
include ("common/connect.php");
include ("common/nav_small.php");
include ("common/banner.php");

// Display the current category
$category = mysql_fetch_row(mysql_query("SELECT id, title FROM category_new WHERE id='$id'", $mysql_access));
echo "$category[1]<BR><BR>";


// Display how many items are in all sub categories ****NOT FINNISHED (only dislays main sub cat)
$num_rows = mysql_num_rows(mysql_query("SELECT id FROM item_new WHERE item_id='$category[0]'", $mysql_access));
echo "$num_rows <BR><BR>";


// List all the sub categories in the current category
$result = mysql_query("SELECT id, title FROM category_new WHERE parent_id='$id'", $mysql_access);
if ($result)
{
  while($category = mysql_fetch_row($result))
  {
    // Calculate how many items are in each sub category and display them
    $num_rows = mysql_num_rows(mysql_query("SELECT id FROM item_new WHERE item_id='$category[0]'", $mysql_access));
	echo "$category[1] ($num_rows)<BR>";

// List all sub categories and display them also
$sub_result = mysql_query("SELECT id, title FROM category_new WHERE parent_id='$category[0]'", $mysql_access);
while($category_2 = mysql_fetch_row($sub_result))
{
  $num_rows = mysql_num_rows(mysql_query("SELECT id FROM item_new WHERE item_id='$category_2[0]'", $mysql_access));
  echo "<font color='red'>$category_2[1] ($num_rows)</font><BR>";
}
  }
}
?>

Thanks

Asa Carter

[coreyp_1]

You need to begin thinking recursively to handle this.

psuedocode would go something like this:

function get_number_in_categories ( $category_id_to_search)
{
$result = mysql_query ("select * from category_table
    where categoryid='$category_id_to_search'");
$total = mysql_num_rows ($result);
$result = mysql_query ("select * from category_table
    where parentid='$category_id_to_search'");
while ($row = mysql_fetch_array($result)
    $total += get_number_in_categories ($row['parentid']);
return $total;
}

Since this is a recursive function, it will handle an infinite number of category levels (up to the server's physical limits, that is).

Obviously, this is just a rough sketch of the code that can be used, but with a little imagination, you can use this to also print out the listing of each subcategory, too.

HTH

[asa_carter]

Hi,

I've been on holiday so I didn't get around to finnishing this.

I'm still confused as to how this works:-

<html>
<head>
</head>
<body>
<? 
include ("common/connect.php");

function get_number_in_categories ($category_id_to_search)
{
  $result = mysql_query("SELECT * FROM category_new WHERE id='$category_id_to_search'");
  $total = mysql_num_rows($result);
  $result = mysql_query("SELECT * FROM category_new WHERE parent_id='$category_id_to_search'");
  while ($row = mysql_fetch_array($result))
    $total += get_number_in_categories ($row['parent_id']);
  return $total;
}

echo get_number_in_categories(1);

?>
</body>
</html>

www.cartermediadesign.co.uk/infinite_cat.php

It just gets stuck in an infinite loop and doesn't display anything.

:😕

Also, is there any software available that lets you step through the code and add watches to variables?

Thanks,

Asa Carter

[coreyp_1]

Logic error. ;oP

It's stuck in a loop b/c we keep searching the same category! I made the mistake in my psuedocode.

The revised code below should:
1. Count the number of items matching the given category.
2. Search for sub-categories.
3. Count the number of items in that subcategory (using the recursive function).

<?  

include ("common/connect.php"); 

function get_number_in_categories ($category_id_to_search) 
{ 
  $result = mysql_query("SELECT * FROM item_new WHERE id='$category_id_to_search'"); 
  $total = mysql_num_rows($result); 
  $result = mysql_query("SELECT * FROM category_new WHERE parent_id='$category_id_to_search'"); 
  while ($row = mysql_fetch_array($result)) 
    $total += get_number_in_categories ($row[id']); 
  return $total; 
} 

echo get_number_in_categories(1); 

?> 

HTH

Corey

[HalfaBee]

Originally posted by asa_carter

Also, is there any software available that lets you step through the code and add watches to variables?

Thanks,

Asa Carter

It is called debugging.
Stick an 'echo' in where you think the problem is.

HalfaBee