Categories and sub categories

[jemq]

Sorry admins, I should have posted this in coding or code critique - my apologies, please move to where you think it is relevant.

After some ideas and coding assistance please.

I have 4 tables

Products- Basically products to view
REL_Products_Categories - One Product can be part of multiple categories. Contains the relationship between each product number and each category it is part of
Categories - All categories no matter what their position
REL_Categories_Categories - Basically If a perent is 0 it is top level and the child is the sub category of the parent. This allows for unlimited number of sub categories.

TABLE - Products
record_num INT Auto Increment
product_name VARCHAR

Sample Data
1 Item 1
2 Item 2
3 Item 3
4 Item 4

TABLE - REL_Product_Categories
record_num INT Auto Increment
product_rn INT
category_rn INT

Sample Data
1 1 14
1 1 3
2 2 14
2 2 3
3 3 14
3 3 3
4 4 10
4 4 1

TABLE - Category
cat_num INT Auto Increment
cat_name VARCHAR

Sample Data
1 Pendants
2 Earrings
3 Necklaces
4 Shrinkies
5 Plushies
6 Custom
7 Keyring
8 Categories
9 Themes
10 Animals
11 Food
12 Other
13 Rats
14 Tree Of Life

TABLE - REL_Category_Category
record_num INT Auto Increment
category_parent_rn INT
category_child_rn INT

Sample Data
1 0 8
2 0 9
3 9 10
4 9 11
5 9 5
6 9 13
7 9 14
8 8 6
9 8 2
10 8 7
11 8 3
12 8 1
13 8 5
14 8 4

So Basically this data represents.

Item 1 is a product (products Table). It belongs in categories of 1. Pendants and 14 Tree of life (REL_Products_Categories and Category table)
Categories and Themes are root level categories. Animals, Food, Plushies, Rats and Tree Of Life are sub categories of Themes and Pendants, Earrirngs, Necklaces, Custom, Plushies and Keyrings are sub categories of Themes.
NOTE Plushies is a sub category of BOTH Categories and Themes.

I have the code to produce the categories and view the proucts associated with each category as follows

<link href="/css/mm.css" rel="stylesheet" type="text/css" />
<!--[if IE]> <link href="/css/ie.css" rel="stylesheet" type="text/css"> <![endif]-->

<?php

include_once $_SERVER ['DOCUMENT_ROOT'] . "/store/config.php";

ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(0); // timeout in seconds, set to 0 for no timeout 

// Connect
	$connection = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
	if (!$connection){
		die ("Could not connect to the database: <br />". mysql_error( ));
		}
// Select the database
	$db_select=mysql_select_db(DB_DATABASE);
	if (!$db_select) 		{
		die ("Could not select the database: <br />". mysql_error( ));
		}
// Setup the query for category
$cat_rel_query = "SELECT * FROM REL_Category_Category WHERE category_parent_rn=0";

// perform the database query
$cat_rel_result = mysql_query($cat_rel_query) or die("display_db_query:" . mysql_error());

while($row_cat_rel = mysql_fetch_row($cat_rel_result)) {

$cat_query = "SELECT * FROM category WHERE cat_num = '{$row_cat_rel[2]}'";
$cat_result = mysql_query($cat_query) or die("display_db_query:" . mysql_error());
while ($row_cat_query = mysql_fetch_row($cat_result)){
	echo('<h2><strong>'.$row_cat_query[1].'</strong></h2>');
	// Setup the query for Sub_categories
	$sub_query = "SELECT * FROM REL_Category_Category WHERE category_parent_rn='{$row_cat_query[0]}' ";

	// perform the database query
	$sub_result = mysql_query($sub_query) or die("display_db_query:" . mysql_error());
	while($row_sub_result = mysql_fetch_row($sub_result)) {
		$cat_sub_query = "SELECT * FROM category WHERE cat_num = '{$row_sub_result[2]}'";
		$cat_sub_result = mysql_query($cat_sub_query) or die("display_db_query:" . mysql_error());	
		while ($row_cat_sub_result = mysql_fetch_row($cat_sub_result)){
			echo('	<a href="/store/templates/product_content.tpl.php?id='.$row_cat_sub_result[0].'">'.$row_cat_sub_result[1].'</a></br>');
		};
	};
};

}; 		
		echo('<p>&nbsp;</p>');



?>

Now the questions.

If I have extra sub categories down levels 3, 4 etc (eg category 15 has parent 3) how do I write the code for unknown levels of sub categories?
Is the the correct way to associate the records and cross link them to display correctly?

Thanks in advance

Quentin

[halojoy]

I think you have setup your table and fields in a good way.

This must have been done several 1000 times before.
To search a TREE.

There are 2 directions you need to handle in 2 diffeerent functions
1. Search down the tree, from a branch
2. Search/explore the tree, up, out into braches.

Same type of up or down search in tree, we can find
in search directories reqursively. To find files for example.
Parent-nodes and Child-nodes are terms often used.

Just because we have here a tree structure in database tables
makes the search not much different.
The effective way to do it will be the same.

And most probably easier.
There might be some built in things in SQL/MySQL to handle this very neatly!
🙂

[jemq]

Ok, So i got the menu to work correctly using the code below.

Now for step 2,

How do I display the menu as an unordered list?
How do I display only the last items as links to products?
How do I make the menu collapsible so only Root and 1st Child are visible until you click on the first child and then the sub children display?

Thanks

Q

<link href="/css/mm.css" rel="stylesheet" type="text/css" />
<!--[if IE]> <link href="/css/ie.css" rel="stylesheet" type="text/css"> <![endif]-->

<?php

include_once $_SERVER ['DOCUMENT_ROOT'] . "/store/config.php";

ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(0); // timeout in seconds, set to 0 for no timeout 


function sub_cat($cat_sub_id) {
	// Setup the query for Sub_categories
	$sub_query = "SELECT * FROM REL_Category_Category WHERE category_parent_rn='{$cat_sub_id}' ";

	// perform the database query
	$sub_result = mysql_query($sub_query) or die("display_db_query:" . mysql_error());
	while($row_sub_result = mysql_fetch_row($sub_result)) {
		$cat_sub_query = "SELECT * FROM category WHERE cat_num = '{$row_sub_result[2]}'";
		$cat_sub_result = mysql_query($cat_sub_query) or die("display_db_query:" . mysql_error());	
		while ($row_cat_sub_result = mysql_fetch_row($cat_sub_result)){
			echo('	<a href="/store/templates/product_content.tpl.php?id='.$row_cat_sub_result[0].'">'.$row_cat_sub_result[1].'</a></br>');
			sub_cat($row_sub_result[2]);
		};
	};


}


// Connect
	$connection = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
	if (!$connection){
		die ("Could not connect to the database: <br />". mysql_error( ));
		}
// Select the database
	$db_select=mysql_select_db(DB_DATABASE);
	if (!$db_select) 		{
		die ("Could not select the database: <br />". mysql_error( ));
		}

//Query to find ALL root parents. i.e category_parent_rn = 0
$root = "SELECT * FROM REL_Category_Category WHERE category_parent_rn=0";

// perform the database query
$root_result = mysql_query($root) or die("display_db_query:" . mysql_error());

// for ALL the root categories find ALL the sub categories
while($row_root_result = mysql_fetch_row($root_result)) {
	$cat_query = "SELECT * FROM category WHERE cat_num = '{$row_root_result[2]}'";
	$cat_result = mysql_query($cat_query) or die("display_db_query:" . mysql_error());
	$row_cat_query = mysql_fetch_row($cat_result);
'.$row_root_result[2].'</strong></h2>');
	echo('<h2><strong>'.$row_cat_query[1].'</strong></h2>');
	sub_cat($row_root_result[2]);
}; 		




?>