recordset self join

Hey there I wondered if you could give me some pointers on defining a mysqli self join recordset?

I believe this may help me solve my long issue on coding a website navigation.

I have 3 tables.

1. products (I may store a CategoryParentID here as well if it helps coding logic)
2. categories (This stores both parent categories AND child categories in the field CategoryParentID and CategoryID (auto increment)
3. catalog (this stores ProductID and CategoryID (and possibly CategoryParentID if needed)

NOTE: for the categories table I am also storing CategoryTopMenu (either 1 or NULL) this allows the category to be placed as a top level category. I didnt use CategoryParentID for this because it limits me in what I want to do) - I want the client to be able to create (show/hide) top level categories in the menu bar) Some of these top level categories will have child categories but others may just navigate to the category listing page.

If there is a parent category that includes child categories then I want the customer to click on the initial link that will display ALL products for the parent category, or the customer can hover over a child category.

I think I maybe close to a solution if I create a self join on the above tables in some way so that I can loop the parent records and then in the menu code, i can loop any child records.

here is the structure of the menu code in basic form:

....begin loop
<li><a href="../category.php?Department=&Category=">ParentCategory</a>

<ul class="dropdown">
...begin loop
<li <a href="../category.php?Department=&Category=">ChildCategory</a> </li>
...end loop
</ul>

</li>...end loop

So for the above menu structure (knowing that the one table contains parent and child records it would make sense to use a self join sql query right?

Chris

If you are only going one level deep then a join to an aliased self would work. If you want to go N levels deep then you would want to use a nested loop.

To self join it would look like:

SELECT * from yourTable LEFT OUTER JOIN yourTable AS selfJoinedChildren ON yourTable.ID = selfJoinedChildren.ParentID

Hi, I think I missed something with the logic as it breaks the whole website. Perhaps if I show the code in context (may need a premiere ticket which I am in urgent need asap). but can you at least look at the code below to see at least your solutions fits in what I want to do?

<?php

$rsParentCategories = new WA_MySQLi_RS("rsParentCategories",$ecartdb,0);

$rsParentCategories->setQuery("SELECT * from categories LEFT OUTER JOIN categories AS selfJoinedChildren ON categories.CategoryID = selfJoinedChildren.CategoryParentID");

$rsParentCategories->execute();

?>

then further down....

<?php

while(!$rsParentCategories->atEnd()) {

?>



<li><a href="../category.php?Department=<?php echo($rsParentCategories->getColumnVal("CategoryLink")); ?>"><?php echo($rsParentCategories->getColumnVal("CategoryName")); ?></a>



<ul class="dropdown">



<?php

while(!$rsParentCategories->atEnd()) {

?>



<li><a href="../category.php?Department=Accessories&Category=<?php echo($rsAccessories->getColumnVal("CategoryLink")); ?>"><?php echo($rsAccessories->getColumnVal("CategoryName")); ?></a> </li>



<?php

$rsParentCategories->moveNext();

}

$rsParentCategories->moveFirst(); //return RS to first record

?>



</ul>

</li>



<?php

$rsParentCategories->moveNext();

}

$rsParentCategories->moveFirst(); //return RS to first record

?>

NOTE: I need to adjust the code above (../category.php?Department=Accessories&Category=) and remove Accessories as this needs to be a dynamic binding.

But can you see what I am trying to do here?


Chris

You are going to have to alias in any columns from the second table as well, like:

SELECT categories.*, selfJoinedChildren.CategoryID AS ChildID, selfJoinedChildren.CategoryName AS ChildName from categories LEFT OUTER JOIN categories AS selfJoinedChildren ON categories.CategoryID = selfJoinedChildren.CategoryParentID

But really the more I think about it, you probably want to use a nested recordset instead so you can order them properly and get the nesting correct.

Hi Ray, ok nested recordsets, I think I had used that method in an old project, is that where you would place a recordset in the main body code inside a while do loop? or is this something different? With the support ticket I am hoping for the following:

(1) Get the navigation to work how I want it (so if a customer clicks on the parent link it will display all products that's associated with the parent category.
(2) If a parent category has child then on mouse hover the customer can navigate to the page showing all products associated with that particular sub-category (child).
(3) Help with the actual category listing page which displays results from linking table (from your advice a while back).
(4) In the admin I have separated parent categories from child categories so that the client can create a new top level category (obviously they are limited on the number of top level categories displaying at any one time due to the width of the website. But I will explain to the client the physical limitation of displaying too many top level categories at the same time :)

Notes:
(1) From your advice a while back I am now storing parent AND child categories within one table.
(2) From your advice I have created a new table (called catalog) which only stores productID and categoryID (I am storing a 3rd field for ParentCategoryID but this may not be needed).
(3) I am also storing the parentCategoryID in the products table (but I guess this isn't needed).

I also want to seek advice on a good approach in the admin to allow my client to associated products with categories (or even vice versa). I have set something up, but wondering if there is an easier approach (my client isnt the best at computers so want to keep the process as simple as possible. Maybe if I send you the link to the admin and if you see the process/logic on associating products to categories then maybe you can give me some advice on best practice.

Chris

OK we can try to get to all of that in our meeting.