Display "ItemCatName", not "ItemCatID"

Thanks in advance for your help.

1. I'm using the sample database provided from WebAssist
2. I've made "item" admin pages (Insert, Search, Results, Update, Delete) with DataAssist - everything works fine.
3. On my Results and Detail page, in the Item Category row, it get the actual ID for the category, not the category name:
itemcategory table is:
ItemCatID ItemCatName
1 CD
2 Gear
Based on the table above, for the category "CD", my Results and Detail page returns "1" instead of "CD"

My understanding was that I needed to use an inner join - I've searched for hours (from WebAssist forums to W3Cschool and everywhere in between) trying to find a combination that works - NO LUCK.

How do I get the "Results" and "Details" page to return the "Name" instead of the "ID"?

Use the following query to get the related category name:

SELECT products.*, productcategories.CategoryName
FROM products
INNER JOIN productcategories
ON products.ProductCategoryID = productcategories.CategoryID

On the detail page, do not replace the WHERE clause. only replace the query up to the WHERE clause.


in the display, make sure to change out the ProductCategoryID binding for the productcategories.CategoryName binding

Jason - thanks for the chunk of code. I'm no expert - in fact hardly a novice with PHP; however that chunk of code did nothing. I changed the code to reflect the proper tables (item / itemcategory) with zero results.

Also, there are not any lines on the pages that have the WHERE command. Here's what the Data Assist produced:

<?php do { ?>
<tr class="<?php echo $WARRT_AltClass1->getClass(true); ?>">
<td class="WADAResultsTableCell"><a href="item_Detail.php?ItemID=<?php echo(rawurlencode($row_WADAitem['ItemID'])); ?>" ><?php echo($row_WADAitem['ItemThumb']); ?></a></td>
<td class="WADAResultsTableCell"><?php echo($row_WADAitem['ItemName']); ?></td>
<td class="WADAResultsTableCell"><?php echo($row_WADAitem['ItemSKU']); ?></td>
<td class="WADAResultsTableCell"><?php echo($row_WADAitem['ItemCatID']); ?></td>
<td class="WADAResultsTableCell"><?php echo($row_WADAitem['ItemShortDesc']); ?></td>
<td class="WADAResultsTableCell"><?php echo($row_WADAitem['ItemPrice']); ?></td>
<td class="WADAResultsEditButtons" nowrap="nowrap"><table class="WADAEditButton_Table">


It displays the itemcategory.itemCatID in the browswer; not the itemcategory.itemCatName.

I could not get anything to display with the code you sent - I must be missing something in the code. I swapped the following code:

Took out:
<?php echo($row_WADAitem['ItemCatID']); ?> (from the code listed above)

Put in:
<?php SELECT item.*, itemcategory.ItemCatName
FROM item
INNER JOIN itemcategory
ON item.ItemCatID = itemcategory.ItemCatID; ?>

Am I missing something? Do I have to somehow echo the result?

IS IT POSSIBLE I've done something wrong in the Wizard when choosing "Menu" for this field? I've struggled for an extremely long time with this and can't find hardly anything regarding it in the forum; which makes me think NO ONE else has had this problem.

I have to imagine that pulling information from another table based on storing and "ID" is pretty standard with Data Assist as it's a result of database normalization, with that being the case I'm wondering if I'm messed up from the start. - just a thought.

Thanks Jason!

this is the code that out puts the values being returned from the recordset:
<?php echo($row_WADAitem['ItemCatID']); ?>

You need to edit the recordset select statement using the code I provided.


Go to Window -> Server Behaviors.

In the list of server behaviors, double click the WADAitem recordset to edit it.

Replace the SELECT* FROM item portion of the recordset with the SQL:

SELECT item.*, itemcategory.ItemCatName
FROM item
INNER JOIN itemcategory
ON item.ItemCatID = itemcategory.ItemCatID

then change:
<?php echo($row_WADAitem['ItemCatID']); ?>


to:
<?php echo($row_WADAitem['itemcategory.ItemCatName']); ?>

Jason - thanks for the detailed instructions. That was an extremely helpful "how to" that will now be in my "help pages".

I made the changes and it gave me an "Undefined Index . . ." I added a line to suppress error handling and that obviously got rid of the error. However, now I do not get any results at all in my "Category" column.

I hit the "test" button in the server behavior and it and both tables were indeed combined into one - all columns present.

It appears that the line <?php echo($row_WADAitem['itemcategory.ItemCatName']); ?> isn't pulling up the ItemCatName.

Any additional suggestion - (I know it's close because the inner join is working - it's producing the new table . . .)

Thanks man!

try using the bindings panel to get the correct code them. Open the bindings panel, and select the Category name binding and add it to your page.

At least now the actual category names are displaying (progress); however, when I try to do another search with the "Category" drop down menu (WADADynListitemcategory) , I get this this error:

Column 'ItemCatID' in where clause is ambiguous

I have two recordsets: 1) WADAitem 2) WADADynListitemcategory Both tables have a column labeled 'ItemCatID'.

Now what do you suggest?

I figured out what the problem was. I had two columns that were labeled identically in my items table and itemcategory table. I changed the itemCatID to itemCat in the item table, and left the itemCatID alone in the itemcategory table.

To recap (the final process that produced the needed result):

1. I changed the WADAitem recordset code (from Dreamweaver "Server Behaviors" panel) to:

SELECT item.*, itemcategory.ItemCatName
FROM item INNER JOIN itemcategory ON item.ItemCat = itemcategory.ItemCatID
(click on "test" (Dreamweaver) and you'll see the result of the inner join in the last column)

2. I inserted the ItemCatName binding (from the "Bindings" panel).

NOTE: The column ItemCatName in the Bindings panel is the result of the inner join listed above; it's NOT an actual column in the table. This principle will work for categories, states . . . whatever information that will be duplicated from record to record.

THANKS TO JASON for all your help!

great, glad to hear you have it working.