Help with php mysql

[dhimo]

Hello everyone.I have a mysql table named categories which has these fields:

table name: categoriesFields in table categories: category_id, category_name, parent_id

The above tables builds a tree menu with sub categories, parent, child, like a recursive function, which will be placed as menu.Now I have another table named products which has these fields:

table name: productsFields in products: product_id, category_id, product_name

The category which holds sub categories cannot hold products and vice versa:Now lets say that tree looks like this

MOBILES - Nokia ------ N70 ------ B80 ------ etc - Motorola ------ Model1 ------ Model2 ------ etc - Samsung ------ Model1 ------ Model2 ------ etcTV

I use $_GET to run the sql query to list products under appropriate category. So when i click on Nokia and lets say ?catID=20 and I list all products that have category_id = 20What I am trying to accomplish is that when I click on MOBILES lets sat ?catID=5 then i list all pruducts under Nokia, Motorola, Samsung which are child categories of MOBILESSo, anyone one knows a good function and sql query to make this work?Thanks in advance. Any answer is appriciated

[justsomeguy]

You need to use a recursive function to build the menu. Unfortunately, for a big menu it's fairly database-heavy.

function show_menu($cat = 0, $indent = 0){  if ($cat != 0)  {	//show information for this category	$result = mysql_query("SELECT * FROM categories WHERE category_id=$cat");	if ($row = mysql_fetch_assoc($result))	{	  for ($i = 0; $i < $indent; $i++)		echo "--";	  echo $row['category_name'];		}  }    //get children  $result = db_query("SELECT category_id FROM categories WHERE parent_id=$cat");  while ($row = mysql_fetch_assoc($result))	show_menu($row['category_id'], $indent + 1);}

[dhimo]

Thanks for your answer. But maybe I wasnt clear enough in my question.I have 2 tables. 1) categories: which holds the recursive menu (category_id, category_name, parent_id)2) products: which holds the products and the category_id that shows to which category it belongs to (product_id, category_id, product_name)Now I have the menu (categories) and the productsLets say the menu has 3 top categories (Mobiles, Tvs, Cameras)Each of these categories has sub categoriesMobiles --> Nokia, Ericsson, MotorolaTvs --> Philips, Sony, ToshibaCameras --> Cannon, OlympusEach of sub categories contain their Top categories which contain subcategories cannot contain products, so Mobiles, Tvs, Cameras cannot have their id on products table.I use $_GET to retrieve the products.What I want now is:When I get querystring for Mobiles, I want to list all the products which reside in all its sub categories, so I want to list all products in Nokia, Ericsson, Motorola.When I get querystring for Nokia, I want to list all the products which reside only in Nokia tableand so onHope I explained myself well, Thanks again

[Mr_CHISOL]

Here's how you could do it (in theory, no code, sorry)1. Use a query to select the categories which have the id (from _GET) as a parent:

1.a If the query returns empty, then save the id (from _GET) in an array:

$cats= array( $_GET['cid'] );

1.b If the query returns other categories, store them in an array (same name as you would use on 1.a)

2. Loop thru the array and either create a SQL-query with the loop, or run one query for each post/loop to get the products that matches the categoriesThis will work fine in both cases (a "parent cat." or as "sub cat.")Hope that helped..Good Luck and Don't Panic!

[dhimo]

Hello again. I came up with this solution. But it doesnt loop to the end of the tree. It loops only one level down. Any idea how to make it better? thanks

<?php$catID = $_GET['catID'];	$result = mysql_query("SELECT * FROM categories WHERE parent_id = '$catID'") or die('MySQL Error: ' . mysql_error());	if(mysql_num_rows($result) > 0) {		while($row = mysql_fetch_assoc($result)) {		$cats[] = $row["category_id"];		}			foreach($cats as $key => $value) {			$result = mysql_query("SELECT * FROM products WHERE category_id = '$value'") or die('MySQL Error: ' . mysql_error());			while($row = mysql_fetch_assoc($result)) {			echo $row["product_name"] . "<br>";			}		}	} else {			$result = mysql_query("SELECT * FROM products WHERE category_id = '$catID'") or die('MySQL Error: ' . mysql_error());		while($row = mysql_fetch_assoc($result)) {		echo $row["product_name"] . "<br>";		}		}	?>

[justsomeguy]

Just modify what I did to look for the products.

function get_products($cat = 0){  if ($cat != 0)  {	//get category info if necessary	$result = mysql_query("SELECT * FROM categories WHERE category_id=$cat");	$row = mysql_fetch_assoc($result);	$cat_name = $row['category_name'];	//show products for this category	$result = mysql_query("SELECT * FROM products WHERE category_id=$cat");	while ($row = mysql_fetch_assoc($result))	{	  echo $cat_name . " - " . $row['product_name'] . "<br />";		}  }    //get children  $result = db_query("SELECT category_id FROM categories WHERE parent_id=$cat");  while ($row = mysql_fetch_assoc($result))	get_products($row['category_id']);}

You can just type get_products() to print all products, or if you want to print products for a certain category or subcategory tree you just pass the id, like get_products(10);

[dhimo]

Thank you, I think the above function does the trick. It was exactly what I was looking for. Thanks again. I have another question though. It is related to this topic.How can I create a navigation menu like the one in this forum:W3Schools Forum > Server Scripting > PHP > Help with php mysqlbut in my case are the categories, subs and productsMain category > subcategory1 > subcategory3 > My productI am still using same db tables and fieldsCan u give me a good function for this, I would much appreciated. Thanks again

[justsomeguy]

It's sort of the same thing. If you have the current product, then you would get the parent category of the product and keep checking for parents until there aren't any more. That can just be a loop though, you don't need recursion for that because it's not a tree, just a linear path.

function get_breadcrumb($id = 0){  $id = intval($id);  if ($id == 0)	return "No product found";  $retval = "";  //get product name and parent category  $result = mysql_query("SELECT product_name, category_id FROM products WHERE product_id=$id");  $row = mysql_fetch_assoc($result);  $retval .= $row['product_name'];  $cat_id = $row['category_id'];  //while there are parent categories  while ($cat_id != 0)  {	//get category info	$result = mysql_query("SELECT category_name, parent_id FROM categories WHERE category_id={$cat_id}");	if ($row = mysql_fetch_assoc($result))	{	  //add the category to the breadcrumb path	  $retval = $row['category_name'] . " -> " . $retval;  //add a link to the category name or whatever	  $cat_id = $row['parent_id'];	}	else	  $cat_id = 0;  }  return $retval;}$path = get_breadcrumb($product_id);

This function assumes that the parent_id will be 0 if the category does not have a parent, or if the product is not in a category. Ask if you have any questions about what it's doing.

[dhimo]

thanks man, I will test it and i am sure it will work fine hehe

[dhimo]

I tested and it works very well. I was wondering though how to make this with only categories and subcategories. Like we still have not clicked to get a product idWhen we are still on a subcategory we getParent > sub1 > sub2and when we click on a product we getParent > sub1 > sub2 > productand take away the "No products fund"If u know what i mean

[justsomeguy]

You'll probably want to just simplify it and have 2 functions, one for the product and one for the category. If the product ID is not empty, use the product one, or else use the category one. The main thing you need in that function to create the list for categories is the while loop, that builds the category list, the top part shows the product.

[dhimo]

Thanks a lot man. I think everything works fine now. Thanks again