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

Display "ItemCatName", not "ItemCatID"

Thread began 4/20/2010 2:45 am by jshafor | Last modified 4/26/2010 11:22 am by Jason Byrnes | 1026 views | 8 replies |

jshafor

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

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

jshafor

No results

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!

Sign in to reply to this post

Jason ByrnesWebAssist

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']); ?>

Sign in to reply to this post

jshafor

Not displaying anything

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!

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

jshafor

Still not there . . .

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?

Sign in to reply to this post

jshafor

Solved!!

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!

Sign in to reply to this post

Jason ByrnesWebAssist

great, glad to hear you have it working.

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...