MySQL query and php help needed, please

[dhimok]

Hello everyone.

 

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

 

table name: categories
Fields 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: products
Fields 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
------ etc
TV

 

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 = 20

 

What 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 MOBILES

 

So, anyone one knows a good function and sql query to make this work?

 

Thanks in advance. Any answer is appriciated

 

 

[hackerkts]

MySQL database

CREATE TABLE `products` (
  `product_id` tinyint(4) NOT NULL auto_increment,
  `category_id` tinyint(4) NOT NULL,
  `product_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`product_id`)
);

INSERT INTO `products` (`product_id`, `category_id`, `product_name`) VALUES 
(1, 3, 'N70'),
(2, 3, 'B80'),
(3, 3, 'etc'),
(4, 4, 'Model1'),
(5, 4, 'Model2'),
(6, 4, 'etc'),
(7, 5, 'Model1'),
(8, 5, 'Model2'),
(9, 5, 'etc');

 

Script

<?php
/*
Note: parent_id = 0 is the parent of the category
*/
@mysql_connect("localhost", "root", "");
@mysql_select_db("cat");

$catID = $_GET['catID'];

if (!isset($catID)) {
$result = mysql_query("SELECT * FROM categories WHERE parent_id = '0'") or die('MySQL Error: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    	echo '<a href="?catID='.$row['category_id'].'">' .$row['category_name'] . '</a><br>';
}
} else {
    $result = mysql_query("SELECT * FROM categories WHERE category_id='$catID'") or die('MySQL Error: ' . mysql_error());
    $row = mysql_fetch_assoc($result);
    if ($row['category_id'] == $catID && $row['parent_id'] == 0) {
    	echo '<b>' . $row['category_name'] . '</b><br>';
        $result = mysql_query("SELECT * FROM categories WHERE parent_id = ".$row['category_id']."") or die('MySQL Error: ' . mysql_error());
        while ($row = mysql_fetch_assoc($result)) {
        	echo '-<a href="?catID='.$row['category_id'].'"> ' . $row['category_name'] . '</a><br>';
        }
    }
    elseif ($row['category_id'] == $catID && $row['parent_id'] != 0) {
    	echo '<b>' . $row['category_name'] . '</b><br>';
        $result = mysql_query("SELECT * FROM products WHERE category_id = ".$row['category_id']."") or die('MySQL Error: ' . mysql_error());
        while ($row = mysql_fetch_assoc($result)) {
        	echo '-<a href="?productID='.$row['product_id'].'"> ' . $row['product_name'] . '</a><br>';
        }
    }
}


?>

 

The script is quite messy, but it function what you want.

Note: You'll need to make it more secure from mysql injection.

 

Regards,

SkY

[dhimok]

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 products

 

Lets say the menu has 3 top categories (Mobiles, Tvs, Cameras)

Each of these categories has sub categories

 

Mobiles --> Nokia, Ericsson, Motorola

Tvs --> Philips, Sony, Toshiba

Cameras --> Cannon, Olympus

 

Each 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 table

and so on

 

Hope I explained myself well, Thanks again

 

 

 

 

[jitesh]

At first you need to get selected categories and all subcategories throught out tree of the categories up to n level.

 

This is possible through recursion.

 

Then write query

 

select * from product where categories IN (1,2,3,4)

 

Where Suppose 1 is category id which you have selected and others are corresponding subcategories.

[hackerkts]

Ok try this,

MySQL database

CREATE TABLE `categories` (
  `category_id` tinyint(4) NOT NULL auto_increment,
  `category_name` varchar(32) NOT NULL,
  `parent_id` tinyint(4) NOT NULL,
  PRIMARY KEY  (`category_id`)
) ;

INSERT INTO `categories` (`category_id`, `category_name`, `parent_id`) VALUES 
(1, 'Mobiles', 0),
(2, 'Tvs', 0),
(3, 'Cameras', 0),
(4, 'Nokia', 1),
(5, 'Ericsson', 1),
(6, 'Motorola', 1),
(7, 'Philips', 2),
(8, 'Sony', 2),
(9, 'Toshiba', 2),
(10, 'Cannon', 3),
(11, 'Olympus', 3);

CREATE TABLE `products` (
  `product_id` tinyint(4) NOT NULL auto_increment,
  `category_id` tinyint(4) NOT NULL,
  `product_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`product_id`)
) ;

INSERT INTO `products` (`product_id`, `category_id`, `product_name`) VALUES 
(1, 4, 'N70'),
(2, 4, 'B80'),
(3, 4, 'etc'),
(4, 5, 'Model1'),
(5, 5, 'Model2'),
(6, 5, 'etc'),
(7, 6, 'Model1'),
(8, 6, 'Model2'),
(9, 6, 'etc');

 

Script

<?php
/*
Note: parent_id = 0 is the parent of the category
*/
@mysql_connect("localhost", "root", "");
@mysql_select_db("cat");

$catID = $_GET['catID'];

if (!isset($catID)) {
$result = mysql_query("SELECT * FROM categories WHERE parent_id = '0'") or die('MySQL Error: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
    	echo '<a href="?catID='.$row['category_id'].'">' .$row['category_name'] . '</a><br>';
}
} else {
    $result = mysql_query("SELECT * FROM categories WHERE category_id='$catID'") or die('MySQL Error: ' . mysql_error());
    $row = mysql_fetch_assoc($result);
    if ($row['category_id'] == $catID && $row['parent_id'] == 0) {
    	echo '<b><a href="?catID='.$row['category_id'].'">' . $row['category_name'] . '</a></b><br>';
        $result = mysql_query("SELECT * FROM categories WHERE parent_id = ".$row['category_id']."") or die('MySQL Error: ' . mysql_error());
        while ($row = mysql_fetch_assoc($result)) {
        	echo '-<a href="?catID='.$row['category_id'].'"> ' . $row['category_name'] . '</a><br>';
            $result2 = mysql_query("SELECT * FROM products WHERE category_id = ".$row['category_id']."") or die('MySQL Error: ' . mysql_error());
            while ($row = mysql_fetch_assoc($result2)) {
            	echo '--<a href="?productID='.$row['product_id'].'"> ' . $row['product_name'] . '</a><br>';
            }
        }
    }
    elseif ($row['category_id'] == $catID && $row['parent_id'] != 0) {
    	echo '<b><a href="?catID='.$row['category_id'].'">' . $row['category_name'] . '</a></b><br>';
        $result = mysql_query("SELECT * FROM products WHERE category_id = ".$row['category_id']."") or die('MySQL Error: ' . mysql_error());
        while ($row = mysql_fetch_assoc($result)) {
        	echo '-<a href="?productID='.$row['product_id'].'"> ' . $row['product_name'] . '</a><br>';
        }
    }
}


?>

[dhimok]

Hello again. I came up with this query and it works ok. The only problem is that it doesn't loop to the and of the tree. It just loops one level down and thats it. Any chance to make it better? Thanks again

 

<?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>";
	}

}


?>

[hackerkts]

Sorry I don't really know what you mean, can you explain it with examples or something?