从表中获取父类和子类

Question

I'm working myself into trouble on this one.

Currently working on a script to create slugs for products and categories in my opencart website. I've got a max 4 lvl deep structure before the product itself.

Each product stores an array with the categories. The client inserted the products in a way I'm not always having the same situation. Sometimes It's only the deepest (lvl 4) categories sometimes it's all the categories from the 4 lvl structure up to top. This works since I'm having opencart to show all products in parent categories.

So I'm working on a script that gathers all categories names upon one given category id. It has to look for possible parents and possible child.

This somehow is not leading me tot the satisfied result. But I see I'm almost at the desired point. Although my code is a mess like I've never seen before.

So.. The categories table holds the category id and his parent id. When no parent, parent id = 0 The categories names are stored in another table containing id and name.

Each product row holds a set of category id's of which I get one.

I'm sure I'm doing it all wrong since their must be a way to check for all child and parent categories and get their names via some nested loops that are a fraction of the code I have right here...

$products = $db->query("SELECT * FROM ".DB_PREFIX."product");
                          $products = $products->rows;
                          foreach ($products as $product) {
                                $url = $db->query("SELECT * FROM ".DB_PREFIX."url_alias WHERE query='product_id=".$product['product_id']."' LIMIT 1");
                                $url = $url->rows;
                                $url = $url[0];
                                $info = $db->query("SELECT * FROM ".DB_PREFIX."product_description WHERE product_id='".$product['product_id']."' LIMIT 1");
                                $info = $info->rows;
                                $info = $info[0];


                                $categories = $db->query("SELECT `bnc_category_description`.`name` , bnc_category_description.category_id FROM  `bnc_product_to_category` JOIN  `bnc_category_description` ON  `bnc_product_to_category`.`category_id` =  `bnc_category_description`.`category_id` WHERE  `bnc_product_to_category`.`product_id` ='".$product['product_id']."' LIMIT 1");
                                $categories = $categories->rows;
                                $categories = $categories[0];

                                //$i = 1;

                                $parent = $db->query("SELECT category_id, parent_id FROM bnc_category WHERE category_id ='" . $categories['category_id'] . "'");
                                $parent = $parent->rows;
                                $parent = $parent[0];


                                $link = $parent['category_id'] . "_";

                                $n = $parent['category_id'];

                                $pr = $parent['parent_id'];

                                $y = $db->query("SELECT category_id, parent_id FROM bnc_category WHERE parent_id ='" . $parent['category_id'] . "'");
                                $y = $y->rows;
                                $y = $y[0];

                                $x = $db->query("SELECT name FROM bnc_category_description WHERE category_id = '" . $y['category_id'] . "'");
                                $x = $x->rows;
                                $x = $x[0];

                                $final = $x['name'] . "_";

                                while($pr != 0){

                                    $s = $db->query("SELECT category_id, parent_id FROM bnc_category WHERE category_id ='" . $pr . "'");
                                    $s = $s->rows;
                                    $s = $s[0];
                                    $pr = $s['parent_id'];

                                    $n = $s['category_id'];

                                    $link .= $n . "_";
                                }

                                $name = $db->query("SELECT name FROM bnc_category_description WHERE category_id = '" . $test['category_id'] . "'");
                                $name = $name->rows;
                                $name = $name[0];

                                $test = $db->query("SELECT category_id FROM bnc_category WHERE parent_id='" . $n . "'");
                                $test = $test->rows;
                                $test = $test[0];

                               $final .= $categories['name'] . "_" . $name['name'];