close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

recordset self join

Thread began 5/22/2016 7:47 pm by Christopher West | Last modified 5/24/2016 12:43 pm by Ray Borduin | 169 views | 5 replies |

Christopher WestCommunity Expert

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

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post

Christopher WestCommunity Expert

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:
<?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:
<?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

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

Christopher WestCommunity Expert

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

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...