tutorial "Displaying Nested Recordsets With Data Bridge"

This was a great tutorial.

I want to apply what I learned in this to a PowerStore store (4.53) I'm working on. I want to create a "Related items" or "If you like this you may like these" section on the product details page.

In the tutorial it only uses two tables: menu_category and menu_items

In PowerStore (4.5.3) would I need to use three tables?
- ps4_categories -- to match with the same category as the product detail page
- ps4_productcategories -- to match the productID with the categoryID
- ps4_products -- to display the data from the above matches

What would the recordset look like to accomplish this?

Tim

What you are trying to accomplish will take a fair bit of custom coding due to the many to many relationship that is used instead of the 1 to many from the tutorial.


the the first recordset would join the ps4_products and ps4_productcategories tables:
SELECT ps4_products.*,ps4_categories.CategoryName
FROM ps4_products
INNER JOIN ps4_productcategories ON ps4_products.ProductID = ps4_productcategories.ProductID


the second would join all 3 record sets together.
SELECT ps4_products.*,ps4_categories.CategoryName
FROM ps4_products
INNER JOIN ps4_productcategories ON ps4_products.ProductID = ps4_productcategories.ProductID
INNER JOIN ps4_categories ON ps4_categories.CategoryID = ps4_productcategories.CategoryID

So don't I need some kind of variable like below (but not this) in the PowerStore example:

$colname_MenuItems = "-1";
if (isset($_GET['ItemCategory'])) {
$colname_MenuItems = $_GET['ItemCategory'];
}

Also, what column names in the Powerstore example would replace ItemCategory and CategoryID from the tutorial?

Below is the record set I'm referring to in the Tutorial and what I'm attempting in the PowerStore example:

Tutorial

<?php
$colname_MenuItems = "-1";
if (isset($_GET['ItemCategory'])) {
$colname_MenuItems = $_GET['ItemCategory'];
}
mysql_select_db($database_nested, $nested);
$query_MenuItems = sprintf("SELECT * FROM menu_items WHERE ItemCategory = %s", GetSQLValueString($colname_MenuItems, "int"));
$MenuItems = mysql_query($query_MenuItems, $nested) or die(mysql_error());
$row_MenuItems = mysql_fetch_assoc($MenuItems);
$totalRows_MenuItems = mysql_num_rows($MenuItems);
?>


Powerstore Nested tables

<?php
mysql_select_db($database_powerstore, $powerstore);
$query_MenuItems = "SELECT ps4_products.*,ps4_categories.CategoryName FROM ps4_products INNER JOIN ps4_productcategories ON ps4_products.ProductID = ps4_productcategories.ProductID INNER JOIN ps4_categories ON ps4_categories.CategoryID = ps4_productcategories.CategoryID";
$MenuItems = mysql_query($query_MenuItems, $storecss_powerstore) or die(mysql_error());
$row_MenuItems = mysql_fetch_assoc($MenuItems);
$totalRows_MenuItems = mysql_num_rows($MenuItems);
?>

yes, you do need the dynamic variables, I was giving you the basic queries.

the category id is set by the ps4_categories.CategoryID column

the item category is set by the ps4_productcategories.CategoryID column

When I've made my adjustments I see the second recordset (MenuItems) in the code view but it is no longer showing up as a recordset in the server behavior panel or the bindings panel. That's not good. Is there a common mistake I'm making or would you need to look at the document?

Thanks for your help.

Tim

I would need to see the page, but the reality is that we do not offer support for making modifications to power store. There is simply to much that can go wrong.

if you would like help with setting up cross selling, i would suggest signing up for a premiere support appointment.

Here's the page. I'll do a premiere ticket if this is too far out of wack. Thanks for looking at it.
Tim

Did you edit the SQL Select code directly in the code or in the recordset window?

it looks like you did the edit directly in the code.

if you look at the code for the SQL it is spread over 4 lines, the SQL code should all be on the same line, having the SQL on multiple lines is causing the issue.

when you change the SQL code, make sure to do it in the recordset dialog box instead of changing it directly in the code.

Thanks. I put all the code in without breaks and I see the recordset in the panels now. However, when I hit OK in the Recordset box it gives me an error:

colname is an invalid variable name; it does not appear in the SQL

Tim

in the recordset window, what is the SQL

in the where section, you will need to define a variable.

make sure that in the parameters section, that same variables is defined.