Navigating through tables to item

I've managed to link 4 tables with the help of the forum (showthread.php?t=4658) which has achieved most of the functionality required but I'm struggling to navigate the way I need to. The four tables are Items (main table with foreign keys), Types (child 1), Makes (child 2) and Styles (child 3). Basically I want to start with a list of product types. That then links to a list of product makes of the chosen product type. That then links to a list of product styles of the chosen product make and finally link to the detail page of the actual item. I appreciate CSS Menu Writer goes someway to dynamically achieving this but need to do it going from results page (type) to results page (make) to results page (style) to get to the required detail page (item). Could I see what the dynamic link code should be stage by stage and the record sets, including sql code. I've tried following other examples but get lost at the second stage. Also, am I missing a trick using DateAssist. EG just run the Wizard 4 times and then hand code the sql?

In your last sentence you nailed it, DataAssist is the way to go. You can use DataAssist to create the results pages for each of the three tables, then craft the final details page for the items.

You could also make them your self pretty easily.

The idea is to do this one page at a time. On your first listing page you will have a recordset that selects all from the types table. After the recordset is on the page you will go to the bindings panel to create the listing on the page, you just drag and drop the values you would like to see on the page. Once they are on the page you just put a repeat region around them and specify all records.

You then make a dynamic link from one of the columns values on the page. This is a regular link with an id appended to it, the id value should come from your recordset.

This link will go to the next results page with the id for that type. On the next results page you will need to add a recordset to select all from the make table, but this time you will filter based on the id column being equal to your URL parameter. This is the id value in the link you created on the first page.

The process is exactly the same for the rest of the pages, create the design, add bindings, set the repeat region, then add in a link. Then on the next page start with your filtered recordset.

Should have tried it first, works a treat. Anyway, good for the Forum. Thanks Eric.

I thought I'd craked this but in certain situations I'm not getting the right list of items. If I'm going from TYPES to MAKES to STYLES, how do I make sure that I still have the ID for TYPES in the last list of STYLES. In some situations when I get to the Styles page I get a list that's forgotten that I only want a list of styles in the TYPE I first selected. IE what should the recordset and/or dynamic links to the STYLE and DETAIL pages look like.

I think there are a couple of ways that you could make the Type and other parameters persistent. I think the best approach would be to just add the previous id to the link on the details page.

So when you are on the types page and you click a type to go to the makes you will have a list of the makes for that type. On this page the various makes will link to the styles results page that will be filtered by the make. On this makes results page you will want to add in the type id to the link so when it goes to the style page it will be filtered by both the make and type rather than just the make.

How you do this on your makes page will depend on the name of the URL parameter that holds type id and how your current link to the styles table looks. You will need to append the GET variable to the link that goes to the style page. If you post back with the name of the variable that is present in the URL when you are on the makes listing as well as the link code you have that goes to the style page I can show you how to combine the two.

Also, on the styles page you will need to add in an additional filter on the recordset to filter it by the type as well as the make. Does the recordset on your styles page currently select the type column from the table? You may need to add in a join to this query to get the data to come from the types table, then filter on the type.

Please post back with the info about the URL parameter and link so we can get that going then also let me know how the query looks on your style page.

Hi Eric

This is what we've got:

1. On the products page you click on a Type which takes you to the Makes page, url looks like: Makes.php?ItemTypeID=1
On the Producutspage no variables or GET in the Recordset (sql)

2. Then on the Makes page you click on a Make to go to the Styles page, url looks like: Styles.php?ItemMakeID=1
On the Makes page the Recordset (sql) looks like:
SELECT Items.*, ItemMake.*, ItemStyle.*, ItemType.*
FROM Items INNER JOIN ItemMake ON Items.MakeID = ItemMake.ItemMakeID INNER JOIN ItemStyle ON Items.StyleID = ItemStyle.ItemStyleID INNER JOIN ItemType ON Items.TypeID = ItemType.ItemTypeID
WHERE ItemTypeID = colname
Variables, Name: colname
Type: integer
Default value: -1
Runtime value: $_GET['ItemTypeID']

3. Finally on the Style page you choose a Style to go to the Details page, url looks like: Detail.php?ItemStyleID=1
On the Style page the sql looks like:
SELECT Items.*, ItemMake.*, ItemStyle.*, ItemType.*
FROM Items
INNER JOIN ItemMake ON Items.MakeID = ItemMake.ItemMakeID
INNER JOIN ItemStyle ON Items.StyleID = ItemStyle.ItemStyleID
INNER JOIN ItemType ON Items.TypeID = ItemType.ItemTypeID
WHERE ItemStyleID = colname
Variable, Name: colname
Type: integer
Default value: -1
Runtime value: $_GET['ItemMakeID']

4. The Recordset on the final Details page looks like:
SELECT Items.*, ItemMake.*, ItemStyle.*, ItemType.*
FROM Items INNER JOIN ItemMake ON Items.MakeID = ItemMake.ItemMakeID
INNER JOIN ItemStyle ON Items.StyleID = ItemStyle.ItemStyleID
INNER JOIN ItemType ON Items.TypeID = ItemType.ItemTypeID
WHERE ItemStyleID = colname
Variables, Name: colname
Type: integer
Default value: -1
Runtime value: $_GET['ItemStyleID']

Hope that helps.

Ok, so with the links you posted you will want to modify the link that is going to the styles page to include the type id. So the link that looks like this:

Styles.php?ItemMakeID=1

Should look like this:

Styles.php?ItemMakeID=1&ItemTypeID=1

Then in the recordset on your styles page you will add in a filter for the type like this:

SELECT Items.*, ItemMake.*, ItemStyle.*, ItemType.*
FROM Items
INNER JOIN ItemMake ON Items.MakeID = ItemMake.ItemMakeID
INNER JOIN ItemStyle ON Items.StyleID = ItemStyle.ItemStyleID
INNER JOIN ItemType ON Items.TypeID = ItemType.ItemTypeID
WHERE ItemStyleID = colname AND ItemTypeID = colname2
Variable, Name: colname
Type: integer
Default value: -1
Runtime value: Variable, Name: colname
Type: integer
Default value: -1
Runtime value: $_GET['ItemMakeID']
Variable, Name: colname2
Type: integer
Default value: -1
Runtime value: $_GET['ItemTypeID']

I can see one issue with this recordset that you have though, it seems that you are filtering the ItemStyleID on the $_GET['ItemMakeID']. It seems that you should be filtering the ItemMakeID column rather than the style column on this value.

With the links and recordset updated like this you should Type to Make to Style keeping all the results filtered by the previous selection. Then when you are on the styles results page you will link to the details page for the individual item. This part should not change.

So just to confirm, the php for the link on the Make page would look like:

Styles.php?ItemMakeID=<?php echo(rawurlencode($row_rsMakes['ItemMakeID'])); ?>&ItemTypeID=<?php echo(rawurlencode($row_rsTypes['ItemTypeID'])); ?>

Also, I thinks there's a sql variable missing on the Style page recordset. What's the runtime on the first variable, is it $_GET['ItemStyleID']. Are there 3 sql variables?

I've tried the above and it's not picking up the Type ID on the Makes Page (EG Styles.php?ItemMakeID=4&ItemTypeID=) IE No TypeID

Do I need to change the record set on the makes page, and/or the details page? I've only changed the Styles page as described above.

Lastly, are you saying the WHERE should be: WHERE ItemMakeID = colname AND ItemTypeID = colname2

Thanks

It seems that your recordset on the makes page may not have the value for the type. You can use the get variable for the type like this:

Styles.php?ItemMakeID=<?php echo(rawurlencode($row_rsMakes['ItemMakeID'])); ?>&ItemTypeID=<?php echo(rawurlencode($_GET['ItemTypeID'])); ?>

As for the recordset on your styles page I think it is mostly correct but you should be comparing the makeID column to your colname variable rather than the style column. If you just update the part in the where clause that is looking for:

WHERE ItemStyleID = colname

To be like this:

WHERE ItemMakeID = colname

I'm not sure if this is correct for the recordset but you will need to match up the column you are filtering on with the variable that holds this value. You could do it the other way around like you mentioned and keep the column name in the where the same but use your other get query parameter.

There should only be the two variables on the style page recordset for filtering. One for the make, and another for the type. You are correct about the where clause in your last sentence.

Sorry. I've tried various combinations either exactly as described or not and it's just not picking up the Type ID (.../Styles.php?ItemMakeID=1&ItemTypeID=). I'm thinking maybe I've got a problem in the tables or I'm missing something obvious because I understand what you're saying and it makes sense. Just can't get it to work. I think I'm going to need more hands on I'm afraid.