...

View Full Version : Compound MySQL Satements



coffeedemon
04-12-2011, 06:07 PM
I'm trying to select products in a category and the products in subcategories of that category.

My category table looks something like below where each category has a parentid - which it is a subcategory of.

Data


Table: Categories
+-----+----------+--------+
| id | parentid | name |
+-----+----------+--------+
| 1 | 0 | first |
+-----+----------+--------+
| 2 | 1 | second |
+-----+----------+--------+
| 3 | 1 | third |
+-----+----------+--------+
| 4 | 2 | fourth |
+-----+----------+--------+
| 5 | 3 | fifth |
+-----+----------+--------+

The above data is displayed like such:

first
+ second
+ fourth
+ third
+ fifth



Table: Products
+--------------+-------------------+---------------+
| id | categoryid | name |
+--------------+-------------------+---------------+
| 1 | 4 | shoe |
+--------------+-------------------+---------------+


If I'm in the fourth category then I need a query that will select products with the categoryid of fourth,second and first.

If i'm in the third category then the query needs to select products with the categoryid of third and first.

Kind of hard to explain, I hope what I am trying to do is clear.

But some how I need the query to see if there are parent categories and select products with those categoryid's as well.

coffeedemon
04-12-2011, 07:25 PM
This is what I am doing it. I was hoping there is a way to do it in a single MYSQL query.


function compound_categories($categoryid, $iii=0) {
$iii++;
if($iii==10) { echo 'killed'; exit; }


$sql = "SELECT id,parent FROM categories WHERE parent='". $categoryid ."'";
$results = mysql_query($sql)or die(mysql_error());
$row = mysql_fetch_array($results);
$count = mysql_num_rows($results);
$array[] = $row['id'];

if($count != 0) {
$array[] = compound_categories($row['id'], $iii);

}
if($iii == 1) {
return $array;
} else {
return $row['id'];
}
}
if(isset($_GET['category'])) {
$categoryid_array = compound_categories($_GET['category']);
$query_where_clause = "WHERE categoryid='". $_GET['category'] ."'";
foreach($categoryid_array as $key => $value) {
$query_where_clause .= " or categoryid='". $value ."'";
}
echo $sql = "SELECT * FROM products AS t2 ". $query_where_clause ." ORDER BY date DESC";
}

oesxyl
04-12-2011, 07:57 PM
maybe this will help:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

best regards

coffeedemon
04-12-2011, 08:45 PM
Excellent thank you!

oesxyl
04-12-2011, 10:24 PM
Excellent thank you!
you are welcome, and thank you, :)
I search few years ago information about this subject, i was mainly interested in graphs then trees. Another related links:

http://evolt.org/node/4047/
http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html
http://blogs.sitepoint.com/hierarchical-data-database/

best regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum